SQL SERVER: Adds a step to all agent jobs to check if the job is running on the AG Primary and bails if not the primary. Ignores stand alone servers.

AddCheckAGJobStepToJobs.sql

DECLARE @job_name sysname,
		@job_id UNIQUEIDENTIFIER;					

DECLARE inject_jobs_cursor CURSOR FAST_FORWARD
FOR (
	SELECT [j].[name], [j].[job_id] 
	FROM msdb.dbo.[sysjobs] AS [j]
	WHERE [j].[enabled] = 1
		AND NOT EXISTS (
			SELECT * FROM [msdb].[dbo].[sysjobsteps] AS [s2] WHERE [s2].[job_id] = [j].[job_id] AND [s2].[step_name] = 'CHECK AG'
		)
);

OPEN inject_jobs_cursor;												
FETCH NEXT FROM inject_jobs_cursor INTO @job_name, @job_id;		

WHILE @@FETCH_STATUS = 0
BEGIN
	RAISERROR('INJECTING CHECK AG STEP INTO JOB: %s', 0, 1, @job_name) WITH NOWAIT;
	
	EXEC msdb.dbo.sp_add_jobstep @job_id=@job_Id, @step_name=N'CHECK AG', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=3, 
			@on_success_step_id=0, 
			@on_fail_action=1, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, @subsystem=N'TSQL', 
			@command=N'
	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 IS NOT NULL AND @RoleDesc <> ''PRIMARY''
	BEGIN
		RAISERROR(''SERVER %s is not the primary for this AG, exiting job'', 16, 1, @ServerName);
	END'

	FETCH NEXT FROM inject_jobs_cursor INTO @job_name, @job_id;

END;

CLOSE inject_jobs_cursor;												
DEALLOCATE inject_jobs_cursor;