LLI Wiki — My Digital Knowledge Base

AG: check that the instance is the primary replica

T-SQL block that prints PRIMARY NODE on the primary replica of an availability group and raises an error elsewhere, usable as a guard.

This T-SQL block checks whether the instance hosts the primary replica of an availability group, for example as a guard at the start of a job step. On the primary it prints PRIMARY NODE; on any other instance it raises an error on purpose (1/0).

IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
DECLARE @chk varchar(200) = (SELECT ARS.role_desc FROM sys.availability_groups_cluster AS AGC
  INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS RCS ON RCS.group_id = AGC.group_id 
  INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = RCS.replica_id
  INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id WHERE ARS.role_desc = 'PRIMARY')

IF @chk = 'PRIMARY'
BEGIN
print 'PRIMARY NODE'
END
ELSE
BEGIN
select 1/0
END
END

Notes

Mastodon LLIMastodon SocialFosstodon