SSTS Blog

Some news and tidbits that we share

Function to determine primary in an Availability Group

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Jul 26 in Blog 0 Comments

If you are using Availability Groups in SQL Server 2012 there are times that you may want to hinge some login on the current primary of the Availability Group. For example, you may want to run jobs against the primary of the Availability Group. This primary can vary over time so it would be to your advantage to be able to determine this in a dynamic way. Because of this I created a function to assist.

create function [AG].[is_ag_primary]() returns smallint

as

begin

declare @l_is_primary smallint

 

 

select @l_is_primary = max(primary_status) from (

select  case when replica_server_name=@@servername and role_desc='PRIMARY' then 1 else 0 end primary_status

from  sys.availability_replicas r

left join sys.dm_hadr_availability_replica_states rs on rs.replica_id = r.replica_id

and rs.role_desc ='PRIMARY'

) t

 

return @l_is_primary

end

GO

 

Tags: Untagged
Hits: 2646

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments