Introduction
When you configure your SQL servers for high availability you want the failover to be as seamless as possible. If you use availability groups, mirroring or log shipping as part of your high availability strategy you will need to take care of anything that lives outside the context of your databases. That might include logins, SQL Server agent jobs and ensuring consistent server level configuration across your availability nodes. This article will discuss how to handle SQL server agent jobs.
Not so seamless failover of agent jobs
If you have any SQL agent jobs that run in the context of your user databases these will need special handling to ensure a seamless failover. Ideally Microsoft would have added some improvements to the SQL agent to help with this. Maybe given us agent jobs that can live within the context of a user database instead of msdb?
As it stands you will need to keep your agent jobs in sync between the primary and secondaries and figure out a mechanism to activate the jobs on the new primary following failover and disable them on the old primary.
A Solution
There are a few ways to handle this, but my approach is to run all the jobs in the context of the master DB. I then check if the user DB is accessible and execute the command with a 3 part name. See the script below for an example. This will work for availability groups, mirroring and log shipping.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @DB SYSNAME = 'MyDB' IF DATABASEPROPERTYEX(@DB, 'Updateability') = 'READ_WRITE' AND EXISTS(SELECT 1 FROM sys.databases WHERE name=@DB and state = 0) BEGIN PRINT 'Run command' --EXEC MyDB.dbo.MyProc END ELSE BEGIN PRINT 'Not run: Database is not writeable' END |
With this approach you can create your jobs and have them active on your primary and secondaries. You don’t need to worry about enabling/disabling jobs on failover – the jobs are already running but they only do any work if the database is primary. This makes the failover totally seamless. The downside to this approach is that the jobs will report that they ran successfully even if they didn’t do any work which could cause confusion.
The next problem is keeping the jobs in sync between your servers. You can script out the jobs using SSMS easy enough but this could get tedious if you have a larger number of jobs. I’ve created a powershell script below to help with this which you can use below. You can use this to report if your jobs are out of sync and also for performing a synchronization. See the comments at the top of the script for some usage examples.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
Param( [Parameter(mandatory=$true)] [string]$ServerA, [Parameter(mandatory=$true)] [string]$ServerB, [Parameter(mandatory=$false)] [System.Array]$ExcludedCategories=@(), #Job categories to exclude from the synchronization process. [Parameter(mandatory=$false)] [System.Array]$IncludedCategories=@(), #Job categories to include in the synchronization process. [Parameter(mandatory=$false)] [switch]$DoSync #perform a two way synchronization if specified, otherwise run in reporting mode. ) <# Created: 2017-12-06 Author: David Wiseman Description: Sync agent jobs between two SQL Server instances or check synchronization status. This is useful if you are using a high availability technology like availability groups, log shipping or database mirroring that does not include SQL Agent jobs in the failover. The script can be used to validate that the jobs are in sync or it can synchronize the jobs. Requires SqlServer powershell module: Install-Module -Name SqlServer -AllowClobber Example1: ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" Agent jobs will be checked and differences will be reported. Example2: ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync Any jobs that don't exist will be copied from SERVER1 to SERVER2 and from SERVER2 to SERVER1. Any jobs that are different will be copied from the server with the newer version of the job (Based on DateLastModified). Example3: ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync ExcludedCategories "ADHOC" Same as example 2 but jobs in the category "ADHOC" are excluded from synchronization. Note: SQL Agent loads SQLPS which isn't compatible with SqlServer module. A workaround is to run the following powershell in the SQL Agent job. $Error.Clear() $out = powershell -NoProfile -Command "C:\Scripts\SyncSQLAgentJobs.ps1" -ServerA "SERVER1" -ServerB "SERVER2" if ($Error.Count -gt 0){ throw $out } else{ $out } This will ensure the job fails if anything is out of sync. The SQL Server agent account will need appropriate access to msdb database on both instances. #> $ErrorActionPreference="stop" Import-Module SqlServer -DisableNameChecking $IncludedCategories $jobs = Get-SqlAgentJob -ServerInstance $ServerA $ValidationStatus=$true $errorMsg="" $jobs | ForEach-Object{ $jobA = $_ $jobB = $null $scriptA = "" $jobA.Script() | ForEach-Object {$scriptA += $_} $jobB = Get-SqlAgentJob -ServerInstance $ServerB -Name $jobA.Name -ErrorAction Ignore if ($ExcludedCategories.Contains($jobA.Category)){ "Skipping " + $ServerA + ": " + $jobA.Name + " : Excluded category: " + $jobA.Category } elseif($jobB -ne $null -and $ExcludedCategories.Contains($jobB.Category)){ "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category } elseif($jobA -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobA.Category))){ "Skipping " + $ServerA + ": " + $jobA.Name + " : Not included category: " + $jobA.Category } elseif($jobB -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobB.Category))){ "Skipping " + $ServerB + ": " + $jobB.Name + " : Not included category: " + $jobB.Category } elseif ($jobB -eq $null){ $ErrorMsg += "Missing on " + $ServerB + ":" + $jobA.Name + "`n" "Missing on " + $ServerB + ":" + $jobA.Name $ValidationStatus=$false if ($DoSync){ "Creating job " + $jobA.Name + " on " + $ServerB Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands } } else { $scriptB = "" $jobB.Script() | ForEach-Object {$scriptB += $_} if ($scriptA -eq $scriptB){ "OK: " + $_.Name } else { if ($jobA.DateLastModified -lt $jobB.DateLastModified){ $ErrorMsg += "Different (Newer on $ServerB): " + $_.Name + "`n" "Different (Newer on $ServerB): " + $_.Name $ValidationStatus=$false if ($DoSync){ "Dropping job " + $jobA.Name + " on " + $ServerA $JobA.Drop() "Creating job " + $jobA.Name + " on " + $ServerA Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands } } else{ $ErrorMsg += "Different (Newer on $ServerA): " + $_.Name + "`n" "Different (Newer on $ServerA): " + $_.Name $ValidationStatus=$false if ($DoSync){ "Dropping job " + $jobB.Name + " on " + $ServerB $JobB.Drop() "Creating job " + $jobB.Name + " on " + $ServerB Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands } } } } } $missingA = Get-SqlAgentJob -ServerInstance $ServerB | Where-Object {@(Get-SqlAgentJob -ServerInstance $ServerA -Name $_.Name -ErrorAction Ignore).Count -eq 0} $missingA | ForEach-Object{ $jobB = $_ if ($ExcludedCategories.Contains($jobB.Category)){ "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category } else{ $ErrorMsg += "Missing on " + $ServerA + ":" + $jobB.Name + "`n" "Missing on " + $ServerA + ":" + $jobB.Name $ValidationStatus=$false if ($DoSync){ $scriptB = "" $jobB.Script() | ForEach-Object {$scriptB += $_} "Creating job " + $jobB.Name + " on " + $ServerA Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands } } } if ($ValidationStatus -eq $false -and $DoSync -eq $false){ $errorMsg = "SQL Agent jobs require synchronization`n" + $errorMsg throw $errorMsg } |