-> I had a requirement to execute jobs only in primary replica.
-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.
-> 1st step of these jobs should execute below TSQL Query,
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 = '<AGNAME>' and ars.is_local = 1) = 'PRIMARY' begin Print 'This is Primary Replica' end else begin RAISERROR('This job should not run in Secondary',16,1) end
-> For 1st step,
On success action : Go to the next step
On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)
-> Last step of the job should execute the below TSQL,print 'Job_Completion'
-> For Last step,
On success action : Quit the job reporting Success
On failure action: Quit the job reporting failure
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.