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