Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Synchronize SQL Server Agent Jobs for Database Mirroring


By:   |   Last Updated: 2019-04-19   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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:

Missing SQL Server Agent Jobs

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:

Generating script for missing SQL Server Agent Jobs

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.


Last Updated: 2019-04-19


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools