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
- The
AG_Examplename is a placeholder for the availability group name. - The second version compares
@@SERVERNAMEwithreplica_server_name. The comparison works when the two values match, as they do for the instance the job runs on. - The first version raises an error with severity 16, which fails the job step on a secondary replica. The last version forces a divide-by-zero error on purpose to fail the step. Choose the form that matches how your alerts read failed steps.
- Background discussions are in the sources below.
References
- SQL Server Agent jobs and availability groups (dba.stackexchange)
- SQL Server availability groups: add check if primary step to existing agent jobs (Mitch Wheat)