On the plane home from PAS Summit I was sat next to someone who had also attended and when he saw on my laptop that I was part of the SQL Community we struck up a conversation. He asked me how he could compare SQL Agent Jobs across availability group replicas to ensure that they were the same.
He already knew that he could use Copy-DbaAgentJob from dbatools to copy the jobs between replicas and we discussed how to set up an Agent job to accomplish this. The best way to run an Agent Job with a PowerShell script is described here
Compare-Object
I told him about Compare-Object a function available in PowerShell for precisely this task. Take these two SQL instances and their respective Agent Jobs
So we can see that some jobs are the same and some are different. How can we quickly and easily spot the differences?
$Default = Get-DbaAgentJob -SqlInstance rob-xps $bolton = Get-DbaAgentJob -SqlInstance rob-xps\bolton Compare-Object $Default $bolton
What If ?
Copy-DbaAgentJob -Source rob-xps -Destination rob-xps\bolton -WhatIf
and get the following result
which shows us that there are two jobs on Rob-XPS which would be created on the Bolton instance
And if they have been modified?
$Default = Get-DbaAgentJob -SqlInstance rob-xps $Dave = Get-DbaAgentJob -SqlInstance rob-xps\dave $Difference = Compare-Object $Default $dave -Property DateLastModified -PassThru $Difference | Sort-Object Name | Select-Object OriginatingServer,Name,DateLastModified
Just the Date please
$Dave = Get-DbaAgentJob -SqlInstance rob-xps\dave
## Create a custom object array with the date instead of the datetime
$DaveJobs = @()
$Dave.ForEach{
$DaveJobs += [pscustomobject]@{
Server = $_.OriginatingServer
Name = $_.Name
Date = $_.DateLastModified.Date
}
}## Get the Agent Jobs
$Default = Get-DbaAgentJob -SqlInstance rob-xps
$Dave = Get-DbaAgentJob -SqlInstance rob-xps\dave
## Create a custom object array with the date instead of the datetime
$DaveJobs = @()
$Dave.ForEach{
$DaveJobs += [pscustomobject]@{
Server = $_.OriginatingServer
Name = $_.Name
Date = $_.DateLastModified.Date
}
}
## Create a custom object array with the date instead of the datetime
$DefaultJobs = @()
$Default.ForEach{
$DefaultJobs += [pscustomobject]@{
Server = $_.OriginatingServer
Name = $_.Name
Date = $_.DateLastModified.Date
}
}
## Perform a comparison
$Difference = Compare-Object $DefaultJobs $DaveJobs -Property date -PassThru
## Sort by name and display
$Difference | Sort-Object Name | Select-Object Server, Name, DateThis will look like this
I have more than 2 instances
So if we have more than 2 instances it gets a little more complicated as Compare-Object only supports two arrays. I threw together a quick function to compare each instance with the main instance. This is very rough and will work for now but I have also created a feature request issue on the dbatools repository so someone (maybe you ?? ) could go and help create those commands
FunctionCompare-AgentJobs {
Param(
$SQLInstances
)
## remove jobs* variables from process
Get-Variable jobs*|Remove-Variable
## Get the number of instances
$count = $SQLInstances.Count
## Loop through instances
$SQLInstances.ForEach{
# Get the jobs and assign to a new dynamic variable
$Number = [array]::IndexOf($SQLInstances, $_)
$Job = Get-DbaAgentJob-SqlInstance $_
New-Variable-Name "Jobs$Number"-Value $Job
}
$i = $count - 1
$Primary = $SQLInstances[0]
While ($i -gt 0) {
## Compare the jobs with Primary
$Compare = $SQLInstances[$i]
Write-Output"Comparing $Primary with $Compare "
Compare-Object(Get-Variable Jobs0).Value (Get-Variable"Jobs$i").Value
$i --
}
}
