SQL Jobs on a Mirrored Environment

One of the little challenges a DBA has to deal with when setting up SQL Server mirroring is handling of SQL Server jobs. SQL Server mirroring works at the database level so SQL Server instance level objects are not mirrored. To achieve a transparent failover some of those instance level objects, like certain SQL Jobs related to your mirrored databases, need to be somehow "mirrored" as well. The objective is simple: any relevant jobs that were running on the principal, on failover should start running on the mirror. So we have to make sure the jobs are kept in sync and are activated at the right moment (you don't want those jobs to be running on the mirror until the failover happens).

One approach would be to have scheduled jobs that periodically syncs the SQL Jobs on the principal with those on the mirror and periodically checks the mirroring status to determine if the jobs on the mirror need to be activated.

Another simpler approach would be to use multi-server jobs. You can use a third server, like the witness for example, as the job "master" and do the following:

1. All the jobs that must be "mirrored" should be created on the job "master" server as multi-server jobs. 
2. The target for those jobs is set to be the principal 
3. Create a job on the job master server that checks the mirror status, let's say every minute
4. If a failover has happened and the mirror has become the principal then it changes the target server for those jobs.

Here is a simple stored procedure that checks the mirroring status and switches the target server for those jobs.

CREATE PROCEDURE [dbo].[usp_ChangeJobTarget]
  @DBName varchar(256) = 'MyMirroredDB',
  @Principal varchar(256) = 'MyPrincipal',
  @Mirror varchar(256) = 'MyMirror'
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @current_principal varchar(256)
  DECLARE @principalID int
  DECLARE @mirrorID int
  DECLARE @jobname  varchar(256)

   SELECT @current_principal = principal_server_name FROM sys.database_mirroring_witnesses WHERE database_name = @DBName
   SELECT @principalID = server_id from msdb.dbo.systargetservers WHERE server_name = @Principal
   SELECT @mirrorID = server_id from msdb.dbo.systargetservers WHERE server_name = @Mirror

 IF @current_principal like @Mirror
   BEGIN
    -- principal has changed so we need to change the target for all jobs
    -- first let's get the complete list of jobs that target the principal
   DECLARE @JobList TABLE
    (
      [JobName] [varchar](256) NOT NULL,
      [Changed] bit NOT NULL
    )
    INSERT INTO @JobList
      SELECT [name], 0
         FROM msdb.dbo.sysjobs as a INNER JOIN msdb.dbo.sysjobservers as b on a.job_id = b.job_id
         WHERE b.server_id = @principalID
    -- now for each job on the @JobList we need to change the target from principal to mirror
      SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
         WHILE (@jobname IS NOT NULL)
           BEGIN
              exec msdb.dbo.sp_delete_jobserver @job_name = @jobname, @server_name = @Principal
              exec msdb.dbo.sp_add_jobserver @job_name = @jobname, @server_name = @Mirror
              UPDATE @JobList SET [Changed] = 1 WHERE [JobName] = @jobname
              SET @jobname = null
              SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
           END
     END
  END
GO

A couple of notes to keep in mind:

1. There is a small delay on replicating a multi-server job. In other words, if you change a job or add a new job on the job master server it will take up to a minute for those changes to be reflected on the target server(s). 
2. The above stored procedure only goes in one direction – changes the target from principal to mirror in case of failover. You will need to adjust it to make it work both ways – one simple way to accomplish this is to simply compare the current principal with the target for those jobs and if they don’t match then change the target.
3. The above script is provided as is - use at your own risk.

Last but not least: check out our SQL Server comparison and synchronization tools, xSQL Object and xSQL Data Compare they will make your database deployment a breeze. Also, check out our Script Executor - no other tool in the market today comes close to it.