By: Alejandro Cobar | Updated: 2019-04-19 | Comments | Related: 1 | 2 | 3 | 4 | 5 | > Database Mirroring
Problem
In Part 3 of this series I presented a script to identify mismatches and inconsistencies for logins between the Principal and Mirror instances for database mirroring. But what about SQL Server Agent Jobs (which are also independent from the mirroring synchronization process)?
The PowerShell script presented in this tip will not only tell you which jobs are missing at the Mirror instance, but it will create a T-SQL script so that you can decide if you want to create the missing jobs. Keep in mind that it is ideal that both setups (Principal & Mirror) are as identical as possible.
Solution
PowerShell Script
Here's the complete code, along with some considerations, usage and outputs.
- The script receives 1 input as a parameter, which is the SQL Server instance you want to check.
- Since this is targeted for being used in an environment with database mirroring in place, its logic is built around that premise.
- You can enter either the Principal or the DR instance to obtain the information
for your setup.
- The script will determine which one is actually the true Primary instance and depart from there.
- This is done assuming that all the databases have the same role within the same instance (no mix-ups).
- You might need to modify the Invoke-Sqlcmd commands to enter a specific set of credentials to establish the connection.
if ($args[0].Length -gt 0){
$server = $args[0]
#Attempt to connect to the SQL Server instance and exit the whole thing if it is not successful
try{$test = Invoke-Sqlcmd -Query "SELECT 1" -ServerInstance $server -EA SilentlyContinue}
catch{
Write-Host "/*"$args[0]"is not a valid instance, please check and try again... */"
break
}
#If the connection to the instance is successful, then attempt to retrieve the jobs information
$hasMirroring = Invoke-Sqlcmd -Query "SELECT COUNT(*) AS 'value' FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL" -ServerInstance $server
if ($hasMirroring.value -gt 0){
#Missing Jobs Section#
################################################################################################
Write-Host "##################################################################"
Write-Host "##################### MISSING JOBS SECTION #######################"
Write-Host "##################################################################"
Write-Host ""
$roleDeterminationQuery = "
SELECT DISTINCT mirroring_role
FROM sys.database_mirroring
WHERE mirroring_role IS NOT NULL
"
$jobsQuery = "
SELECT s.name,l.name AS 'owner',s.enabled
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l on s.owner_sid = l.sid
"
$partnerQuery = "
SELECT DISTINCT mirroring_partner_instance
FROM sys.database_mirroring
WHERE mirroring_partner_instance IS NOT NULL
"
$serverRole = Invoke-Sqlcmd -Query $roleDeterminationQuery -ServerInstance $server
$partner = Invoke-Sqlcmd -Query $partnerQuery -ServerInstance $server
if($serverRole.mirroring_role -ne 1){
$temp = $server
$server = $partner['mirroring_partner_instance']
$partner['mirroring_partner_instance'] = $temp
}
Write-Host "PRINCIPAL INSTANCE:"$server
Write-Host "MIRROR INSTANCE:"$partner['mirroring_partner_instance']
Write-Host ""
$instanceJobs = @(Invoke-Sqlcmd -Query $jobsQuery -ServerInstance $server)
$partnerInstanceJobs = @(Invoke-Sqlcmd -Query $jobsQuery -ServerInstance $partner['mirroring_partner_instance'])
$resultMissing = @(Compare-Object -ReferenceObject $instanceJobs.name -DifferenceObject $partnerInstanceJobs.name)
$resultEqual = @(Compare-Object -ReferenceObject $instanceJobs.name -IncludeEqual $partnerInstanceJobs.name)
$missingJobsAtPrincipal = @()
$missingJobsAtMirror = @()
$matchingJobs = @()
foreach ($job in $resultMissing){
if ($job.SideIndicator -eq "=>"){ $missingJobsAtPrincipal += $job.InputObject }
if ($job.SideIndicator -eq "<="){ $missingJobsAtMirror += $job.InputObject }
}
foreach ($job in $resultEqual){
if ($job.SideIndicator -eq "=="){ $matchingJobs += $job.InputObject }
}
if($missingJobsAtPrincipal.Length -gt 0){
$header = "Missing Jobs at "+$server
$line = ""
Write-Host $header
for($i = 0; $i -lt $header.Length; $i++){ $line += "-" }
Write-Host $line
$missingJobsAtPrincipal
Write-Host ""
}
if($missingJobsAtMirror.Length -gt 0){
$header = "Missing Jobs at "+$partner['mirroring_partner_instance']
$line = ""
Write-Host $header
for($i = 0; $i -lt $header.Length; $i++){ $line += "-" }
Write-Host $line
$missingJobsAtMirror
}
if(($missingJobsAtPrincipal.Length -eq 0) -and ($missingJobsAtMirror.Length -eq 0)){
Write-Host "Nothing to show here..."
}
Write-Host ""
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server
#Script out each SQL Server Agent Job for the server
Write-Host "Generating script for missing jobs at"$partner['mirroring_partner_instance']
Write-Host ""
$srv.JobServer.Jobs | Where-Object {$_.Name -in $missingJobsAtMirror} | foreach {$_.Script() + "GO`r`n"} | out-file ".\MissingJobs.sql"
}
else{
Write-Host "/* Database mirroring is not configured in this SQL Server instance */"
}
}
else{
Write-Host "/* Please enter an instance as a parameter to proceed */"
}
Write-Host "Done!"
Output to Expect
To demonstrate the output that the script will display, I have configured database mirroring within 2 test instances (without a witness to keep things simple) and have created some test jobs.
- Principal Instance: MC0Z5A9C\TEST2
- DR Instance: MC0Z5A9C\TEST1
Missing Jobs Section
In the Principal instance, I have created 3 test jobs (test_job_1, test_job_2 and test_job_3) that are not present in the DR instance. Therefore, when you execute the script you will see the following:
Notice that the output tells you which is the current Principal/DR instance, and it lists the jobs that are present in the Principal instance but are missing at the DR instance. If we stop right here, then you would have at least a rough idea of how many jobs are missing (or not) between both instances; but probably (and it is a good idea) you want to have them synced to have both instances as identical as possible.
This script takes a step further and generates a T-SQL script that contains all the jobs reported so that they can be created at the DR instance. The execution of this script, against the DR instance, is manual at the moment due to a few considerations:
- You might not want/need a particular job to exist at the DR instance (for whatever reason).
- You might want to make sure that all the generated jobs are put in a disabled state so that they can be disabled right from the start when they arrive at the DR instance.
- You want to modify any hardcoded path/setting within a particular job.
Sample T-SQL Script Generated by the PowerShell Script
Here's the code of the T-SQL script generated by the PowerShell:
- The PowerShell script will generate a file called "MissingJobs.sql"
- For now, the path where this TSQL script is generated is exactly the same
where the PowerShell is being executed from.
- Feel free to tweak the code if you would like to change it (just make sure you have enough privileges to create the file in the new location).
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test_job_1',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test_job_2',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test_job_3',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Now, if we run this T-SQL script on the DR instance and then re-execute the PowerShell script then the output will look as follows:
The script now tells us that there are no inconsistencies between the Principal and DR instances, in regard to SQL Server Agent Jobs. One important thing to keep in mind is that if you have 2 jobs that do exactly the same thing in each respective instance, but are named differently, then the script will still report the inconsistency, but it will be up to you to discard any presented output you consider unnecessary.
Next Steps
- With Parts 3 and 4 (this tip), I have covered a couple of object categories that are often overlooked when it comes to working with database mirroring.
- In a future version of this same script I will include an option to automatically synchronize the jobs to the DR instance (triggering such option with a parameter).
- In Part 5 (and final) I will aim to present a PowerShell script that shows differences in basic key configurations for each of the instances involved, so stay tuned! This will allow the end user to see how similar are both setups in terms of CPU, RAM, disk, data files directories, etc.
- You can find a lot of articles about SQL Server Agent in case there is something very specific that you are looking for.
About the author
Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-04-19
