SSTS Blog
Some news and tidbits that we share
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