As per my comment to @John Eisbrener's (incorrect) assertion "If you wanted to add the check as the new step 1 of each job, you would have to delete every step from the job", I have created a stored procedure which will add a new first step to any existing agent job.
It checks if running on the AG primary replica and quits with success otherwise:
use master
go
-- Adds a first step to specified job, which checks whether running on Primary replica
create procedure AddAGPrimaryCheckStepToAgentJob
@jobname nvarchar(128)
as
set nocount on;
-- Do nothing if No AG groups defined
IF SERVERPROPERTY ('IsHadrEnabled') = 1
begin
declare @jobid uniqueidentifier = (select sj.job_id from msdb.dbo.sysjobs sj where sj.name = @jobname)
if not exists(select * from msdb.dbo.sysjobsteps where job_id = @jobid and step_name = 'Check If AG Primary' )
begin
-- Add new first step: on success go to next step, on failure quit reporting success
exec msdb.dbo.sp_add_jobstep
@job_id = @jobid
, @step_id = 1
, @cmdexec_success_code = 0
, @step_name = 'Check If AG Primary'
, @on_success_action = 3 -- On success, go to Next Step
, @on_success_step_id = 2
, @on_fail_action = 1 -- On failure, Quit with Success
, @on_fail_step_id = 0
, @retry_attempts = 0
, @retry_interval = 0
, @os_run_priority = 0
, @subsystem = N'TSQL'
, @command=N'IF (SELECT ars.role_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1) <> ''Primary''
BEGIN
-- Secondary node, throw an error
raiserror (''Not the AG primary'', 2, 1)
END'
, @database_name=N'master'
, @flags=0
end
end
GO
Blog post is here.
Note: For SQL Server 2014 onwards you can use the builtin function sys.fn_hadr_is_primary_replica('dbname')
:
If sys.fn_hadr_is_primary_replica (@dbname) <> 1
BEGIN
-- This is not the primary replica, exit without error.
END
-- This is the primary replica, continue to run the job...