Skip to main content
Glad to be set straight, just not a fan of it being forever cached. Thanks for the better answer! I upvoted it as well!
Source Link
John Eisbrener
  • 9.5k
  • 6
  • 30
  • 63

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.

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.

I have created a stored procedure which will add a new first step to any existing agent job.

Source Link
Mitch Wheat
  • 1.4k
  • 9
  • 17

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...