Agent Jobs

Query The Primary Server In AlwaysOn

Here’s another quick way you can query the primary server. I’ve posted about this before sure; but that was using a precise result. If you wanted to see all servers involved you can use this little number. It works by simply looking at the [ROLE_DESC] value from sys.dm_hadr_availability_replica_states. Simply put… If the PRIMARY is listed, then you are on the Primary server because the Primary will show the servers involved in the AlwaysOn configuration.

If this is run from the Secondary server then the PRIMARY value from the [ROLE_DESC] will not be listed. It’s a simple method to determine which server is which. I use this with certain automation logic as a quick check before any processes are carried out. This particular example is of an old Sharepoint 2013 environment which is using an OS of Server 2012 environment with SQL Server 2014. Ancient at this point but doesn’t hurt to mention it.

 use master;
set nocount on
set ansi_warnings off

select * from sys.dm_hadr_availability_replica_states;

if exists(select role_desc from sys.dm_hadr_availability_replica_states where role_desc in ('primary'))
    begin
        print 'The Primary role was found.'
    end
    else
        print 'The Primary role was NOT found.'

Advertisements

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