Agent Jobs

Find The Primary Server In AlwaysOn Configuration TSQL

Here’s a quick TSQL query to show you the primary server in an always on configuration. This is helpful in writing logic which first detects if the local server is indeed the Primary server or not.

if exists(select is_local, role_desc from sys.dm_hadr_availability_replica_states where role = 1 and role_desc = 'PRIMARY') begin
print 'This server [' + upper(@@servername) + '] is the primary.' end
else
print 'This server [' + upper(@@servername) + '] is NOT the primary.'
Advertisements

2 replies »

  1. Good one. what if you need to run the query on both the db mirroring and also always on servers? do you have any common query for both setups?

  2. Thanks Vicky… I don’t have a query that contains both the logic combined. AlwaysOn is pretty reliable, and you can setup the replica databases to be readonly. Not like the ‘read_only’ configuration in the traditional_sense. This is through the AlwaysOn setup process. I might put a post up here about that. You can hammer the database replicas on the secondary server all day with queries and should be fine. Can’t create or modify data or objects of course, but it’s a nice if you need it.

    If you’re looking to see which databases ( In SQL Database Mirroring ) that are the Primary and not the mirror; you can simply run this little script:

    select
    upper(sd.name)
    from
    sys.databases sd join sys.database_mirroring sdm on sd.database_id = sdm.database_id
    where
    name not in (‘tempdb’)
    and state_desc = ‘online’
    and sdm.mirroring_role_desc is null
    or sdm.mirroring_role_desc != ‘mirror’
    order by
    name asc

    This basically returns a list of databases that are ONLINE, and are NOT the Mirrored databases. In other words… It shows you a list of Databases that have been configured as the Primary databases in an SQL Mirrored Configuration. It’s a basic script. Hope it’s helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s