-
Notifications
You must be signed in to change notification settings - Fork 1
/
SyncAgentJobs.ps1
125 lines (90 loc) · 6.83 KB
/
SyncAgentJobs.ps1
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
function Sync-AgentJobs {
<#
.SYNOPSIS
Syncs SQL Server Agent Jobs among replicas in the Availability Groups Cluster
.DESCRIPTION
It validates SQL Server Agent Jobs on each replica of the Availability Groups. It compares to a text level to see if something in the job like schedule, steps changed. SSRS and SSIS Jobs are not part of this script
.PARAMETER SQLInstance
Listener Name representing the SQL Server to connect to. This can be a collection of listeners, usually pointing to different environments
.PARAMETER ExcludeJob
The job(s) to exclude - this list is auto-populated from the server.
.PARAMETER TempFolder
Temporary Folder for placing the SQL Agent Job Script for text comparison
.NOTES
Tags: Jobs, Agent
Author: Marcos Freccia
Website: http://marcosfreccia.wordpress.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
.EXAMPLE
Sync-AgentJobs -SQLInstance MyListener001 -TempFolder 'D:\MSSQL\Automation\JobSync'
It compares and syncs all SQL Agent Jobs for the primary and secondary replicas of MyListener001. It makes textual comparisson to make sure all details such as: steps, schedules and so on are included.
.EXAMPLE
Sync-AgentJobs -SQLInstance MyListener001, MyListener002 -TempFolder 'D:\MSSQL\Automation\JobSync'
It compares and syncs all SQL Agent Jobs for the primary and secondary replicas of MyListener001 and MyListener002. It makes textual comparisson to make sure all details such as: steps, schedules and so on are included.
.EXAMPLE
Sync-AgentJobs -SQLInstance MyListener001 -ExcludeJob BackupDiff -TempFolder 'D:\MSSQL\Automation\JobSync'
It compares and syncs all SQL Agent Jobs for the primary and secondary replicas of MyListener001 and MyListener002, excluding the BackupDiff Job. It makes textual comparisson to make sure all details such as: steps, schedules and so on are included.
#>
[cmdletbinding(DefaultParametersetName = 'None')]
param
(
[Parameter(Mandatory = $true, ValueFromPipeline = $true)][object[]]$SQLInstance,
[Parameter(Mandatory = $false)][object[]]$ExcludeJob,
[Parameter(Mandatory = $true)][string]$TempFolder
)
try {
foreach ($SQL in $SQLInstance) {
$PrimaryJobs = @()
$SecondaryJobs = @()
$Results = @()
if ($ExcludeJob) {
$ExcludeJobs = $ExcludeJob
}
$ExcludeJobs += (Invoke-Sqlcmd2 -ServerInstance $SQL -Database msdb -Query "SELECT job.name FROM dbo.sysjobs AS job
JOIN dbo.syscategories AS cat ON cat.category_id = job.category_id WHERE cat.name IN ('Report Server')
UNION
SELECT job.name FROM dbo.sysjobs AS job JOIN dbo.sysjobsteps AS jobs ON jobs.job_id = job.job_id
WHERE jobs.subsystem IN ('SSIS')")
# The Failover always happens from the secondary initiating. So this command returns the secondary server at the moment.
$PrimaryReplica = Get-DbaAgReplica -SqlInstance $SQL | Where-Object {$_.Role -eq "Primary" } | Select-Object -Expand Name
# The Failover always happens from the secondary initiating. So this command returns the secondary server at the moment.
$SecondaryReplica = Get-DbaAgReplica -SqlInstance $SQL | Where-Object {$_.Role -eq "Secondary" } | Select-Object -Expand Name
$PrimaryJobs = Get-DbaAgentJob -SqlInstance $PrimaryReplica -ExcludeJob $ExcludeJobs.name | Select-Object -ExpandProperty name
$SecondaryJobs = Get-DbaAgentJob -SqlInstance $SecondaryReplica -ExcludeJob $ExcludeJobs.name | Select-Object -ExpandProperty name
# Symbol == means that it exists in both sides
# Symbol <= means that it exists in the PrimaryNode but not in the SecondaryNode
# Symbol => means that it exists in the SecondaryNode but not in the PrimaryNode
$Results = Compare-Object -ReferenceObject $PrimaryJobs -DifferenceObject $SecondaryJobs -IncludeEqual | Select-Object InputObject, SideIndicator
foreach ($job in $Results) {
$JobName = $job.InputObject
if ($job.SideIndicator -eq "<=") {
Write-Host "Copying Job [$JobName] from [$PrimaryReplica] to [$SecondaryReplica]" -ForegroundColor Yellow
Copy-DbaAgentJob -Source $PrimaryReplica -Destination $SecondaryReplica -Job $JobName -Force
}
elseif ($job.SideIndicator -eq "==") {
Write-Host "Job [$JobName] exists in all nodes. Looking for differences." -ForegroundColor Yellow
Get-DbaAgentJob -SqlInstance $PrimaryReplica -Job $JobName | Export-DbaScript -Path ("$TempFolder\$PrimaryReplica-$JobName.txt").Replace("/", "_")
Get-DbaAgentJob -SqlInstance $SecondaryReplica -Job $JobName | Export-DbaScript -Path ("$TempFolder\$SecondaryReplica-$JobName.txt").Replace("/", "_")
$JobFromPrimary = Get-Content -Path "$TempFolder\$PrimaryReplica-$JobName.txt" | Select-Object -Skip 4
$JobFromSecondary = Get-Content -Path "$TempFolder\$SecondaryReplica-$JobName.txt" | Select-Object -Skip 4
$JobTextComparison = Compare-Object -ReferenceObject $JobFromPrimary -DifferenceObject $JobFromSecondary -IncludeEqual | Select-Object InputObject, SideIndicator
if ($JobTextComparison.SideIndicator -eq "<=") {
Write-Host "Job [$JobName] in [$PrimaryReplica] is different of [$JobName] from [$SecondaryReplica]. Recreating Job on $SecondaryReplica" -ForegroundColor Yellow
Copy-DbaAgentJob -Source $PrimaryReplica -Destination $SecondaryReplica -Job $JobName -Force
}
elseif ($JobTextComparison.SideIndicator -eq "==") {
Write-Host "Job [$JobName] is synchronized in all replicas" -ForegroundColor Green
}
}
else {
Write-Host "Job [$JobName] found in [$SecondaryReplica] and not found in [$PrimaryReplica]. Removing it from [$SecondaryReplica]" -ForegroundColor Yellow
Remove-DbaAgentJob -SqlInstance $SecondaryReplica -Job $JobName -KeepHistory -Confirm:$false
}
}
}
}
catch {
Write-Host -ForegroundColor Red $Error[0].Exception
}
}
#Sync-AgentJobs -SQLInstance contoso-listener -TempFolder F:\Scripts\Automation\JobSync