SQL Stuff

Check For AlwaysOn Primary Status

This SQL logic will check to see if the instance you are running it on is the Primary in an AlwaysOn configuration.

declare @server_name varchar(255)
declare @availability_group_name varchar(255)
declare @check_if_primary varchar(255)
set @server_name = ( select cast(serverproperty('servername') as varchar(200)) )
set @availability_group_name = ( select name from sys.availability_groups )
set @check_if_primary = ( select sdhars.role_desc from sys.dm_hadr_availability_replica_states sdhars inner join sys.availability_groups sag on sdhars.group_id = sag.group_id where sag.name = @availability_group_name and sdhars.is_local = 1 )

if @check_if_primary = 'PRIMARY'
 begin
 print 'This Server: ' + @server_name + ' (IS) the PRIMARY'
 end
 else
 print 'This Server: ' + @server_name + ' (IS NOT) the PRIMARY'
Advertisements

Categories: SQL Stuff

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