LLI Wiki — My Digital Knowledge Base

Run an Agent job step only on the primary replica

Several T-SQL variants so that a SQL Server Agent job step runs only on the primary replica of an availability group.

On an availability group, a SQL Server Agent job runs on every replica that holds the job. The snippets below go at the start of a job step: they stop the work when the local replica is not the primary. The approaches are alternatives; choose one.

Check the role of the local replica for a named group

This version reads the role of a named availability group on the local replica. It is the recommended form in the source.

 IF SERVERPROPERTY ('IsHadrEnabled') = 1
 Begin 
	if (select
			ars.role_desc
		from sys.dm_hadr_availability_replica_states ars
		inner join sys.availability_groups ag
		on ars.group_id = ag.group_id
		where ag.name = 'AG_Example'
		and ars.is_local = 1) = 'PRIMARY'
	begin
		-- this server is the primary replica, do something here
		print 'PRIMARY NODE'
	end
	else
	begin
		-- this server is not the primary replica, (optional) do something here
		RAISERROR (N'It isn''t primary group !!! ', -- Message id.  
           16, -- Severity,  
           1 -- State,  
           ); -- First argument supplies the string.  
	end
end 

Compare the server name with the replica name

DECLARE @ServerName NVARCHAR(256)  = @@SERVERNAME 
DECLARE @RoleDesc NVARCHAR(60)

SELECT @RoleDesc = a.role_desc
    FROM sys.dm_hadr_availability_replica_states AS a
    JOIN sys.availability_replicas AS b
        ON b.replica_id = a.replica_id
WHERE b.replica_server_name = @ServerName

IF @RoleDesc = 'PRIMARY'
BEGIN
    --Logic for the job goes here
	print 'exec'
END

Use the numeric role of the local replica and start a maintenance plan

In sys.dm_hadr_availability_replica_states, the role value is 1 for a primary replica and 2 for a secondary replica. This version starts a maintenance plan only on the primary.

DECLARE @localReplicaRole int

select @localReplicaRole = Role from [master].sys.dm_hadr_availability_replica_states where is_local = 1;
-- Role = 1 => primary
-- Role = 2 => secondary

IF (@localReplicaRole = 1)
BEGIN
	declare @plan_id uniqueidentifier
	SELECT @plan_id = id FROM msdb.dbo.sysmaintplan_plans AS s WHERE s.name=N'<YOUR JOBNAME GOES IN HERE>'
	exec msdb.dbo.sp_maintplan_start @plan_id
END

Fail the step on a secondary replica

This version makes the step fail on a secondary replica, so the failure is visible in the job history.

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

References

Mastodon LLIMastodon SocialFosstodon