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
- The query joins
sys.availability_group_listeners, so it needs an availability group listener. Without one, no row is returned,@chkis NULL and the batch takes the error branch. - The error branch is intentional: it makes the calling step fail on a secondary replica.