Rather than doing this on a per job basis (checking every job for the state of the server before deciding to continue), I've created a job running on both servers to check to see what state the server is in.
- If its primary, then enable any job that has a step targeting a database in the AG.
- If the server is secondary, disable any job targeting a database in the AG.
This approach provides a number of things
- it works on servers where there are no databases in AG (or a mix of Db's in/out of AGs)
- anyone can create a new job and not have to worry about whether the db is in an AG (although they do have to remember to add the job to the other server)
- Allows each job to have a failure email that remains useful (all your jobs have failure emails right?)
- When viewing the history of a job, you actually get to see whether the job actually ran and did something (this being the primary), rather than seeing a long list of success that actually didn't run anything (on the secondary)
the script checks the database in the field below
This proc is executed every 15 mins on each server. (has the added bonus of appending a comment to inform people why the job was disabled)
/*
This proc goes through all SQL Server agent jobs and finds any that refer to a database taking part in the availability Group
It will then enable/disable the job dependant on whether the server is the primary replica or not
Primary Replica = enable job
It will also add a comment to the job indicating the job was updated by this proc
*/
CREATE PROCEDURE dbo.sp_HADRAgentJobFailover (@AGname varchar(200) = 'AG01' )
AS
DECLARE @SQL NVARCHAR(MAX)
;WITH DBinAG AS ( -- This finds all databases in the AG and determines whether Jobs targeting these DB's should be turned on (which is the same for all db's in the AG)
SELECT distinct
runJobs = CASE WHEN role_desc = 'Primary' THEN 1 ELSE 0 END --If this is the primary, then yes we want to run the jobs
,dbname = db.name
,JobDescription = CASE WHEN hars.role_desc = 'Primary' -- Add the reason for the changing the state to the Jobs description
THEN '~~~ [Enabled] using automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) looking for jobs running against Primary Replica AG ~~~ '
ELSE '~~~ [Diabled] using Automated process (DBA_tools.dbo.sp_HADRAgentJobFailover) because the job cant run on READ-ONLY Replica AG~~~ ' END
FROM sys.dm_hadr_availability_replica_states hars
INNER JOIN sys.availability_groups ag ON ag.group_id = hars.group_id
INNER JOIN sys.Databases db ON db.replica_id = hars.replica_id
WHERE is_local = 1
AND ag.Name = @AGname
)
SELECT @SQL = (
SELECT DISTINCT N'exec msdb..sp_update_job @job_name = ''' + j.name + ''', @enabled = ' + CAST(d.runJobs AS VARCHAR)
+ ',@description = '''
+ CASE WHEN j.description = 'No description available.' THEN JobDescription -- if there is no description just add our JobDescription
WHEN PATINDEX('%~~~%~~~',j.description) = 0 THEN j.description + ' ' + JobDescription -- If our JobDescription is NOT there, add it
WHEN PATINDEX('%~~~%~~~',j.description) > 0 THEN SUBSTRING(j.description,1,CHARINDEX('~~~',j.description)-1) + d.JobDescription --Replace our part of the job description with what we are doing.
ELSE d.JobDescription -- Should never reach here...
END
+ ''';'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps s
INNER JOIN DBinAG d ON d.DbName =s.database_name
ON j.job_id = s.job_id
WHERE j.enabled != d.runJobs -- Ensure we only actually update the job, if it needs to change
FOR XML PATH ('')
)
PRINT REPLACE(@SQL,';',CHAR(10))
EXEC sys.sp_executesql @SQL
Its not fool proof, but for overnight loads and hourly jobs it gets the job done.
Even better than having this procedure run on a schedule, instead run it in response to Alert 1480 (AG role change alert).
