Replicating Jobs Between SQL Instances – Linking Jobs and Schedules

By Greg No comments

This is part 6 of the Replicating Jobs series. In the previous steps I’ve covered how to setup for replication, how to copy categories, schedules and jobs, and now finally we need to link Jobs to Schedules.

After the last step of copying jobs, the linking jobs to schedules is pretty simple.

1. Setup

By now, this code is going to be looking pretty familiar:

IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimayReplicaName'))
BEGIN
  DECLARE @PartnerServer sysname = 'PrimayReplicaName'
  DECLARE @Machine sysname
  DECLARE @SQL NVARCHAR(MAX)



  If CharIndex('', @PartnerServer) > 0
  Begin
    Set @Machine = LEFT(@PartnerServer, CharIndex('', @PartnerServer) - 1);
  End
  Else
  Begin
    Set @Machine = @PartnerServer;
  End

  CREATE TABLE  #JobSchedules
  (
    Action varchar(7),
    local_job_id UNIQUEIDENTIFIER null,
    current_schedule_id int null,
    job_name sysname null,
    primary_schedule_id int null
  )

END

 2. Find

Find all of the job schedules on the primary server and the secondary server. Do a full join so we can add new schedules and delete old schedules.

SET @SQL =	CAST('
      
          ;WITH LocalJobSchedules AS
          (
            select		name, msdbLocalJobs.job_id, schedule_id
            from		msdb.dbo.sysjobschedules msdbLocalSchedules
            INNER JOIN	msdb.dbo.sysjobs msdbLocalJobs
                ON	msdbLocalJobs.job_id = msdbLocalSchedules.job_id
          )
          ,PrimaryJobSchedules AS
          (
            select		name, msdbPrimaryJobs.job_id, schedule_id
            from		' + QUOTENAME(@Machine) + '.msdb.dbo.sysjobschedules msdbPrimarySchedules
            INNER JOIN	' + QUOTENAME(@Machine) + '.msdb.dbo.sysjobs msdbPrimaryJobs
                ON	msdbPrimaryJobs.job_id = msdbPrimarySchedules.job_id
          )

          SELECT		CASE WHEN LocalJobSchedules.job_id IS NULL THEN ''INSERT''
                   WHEN PrimaryJobSchedules.job_id IS NULL THEN ''DELETE''
                   ELSE ''NOTHING''
                END as Action
                ,LocalJobSchedules.Job_ID
                ,LocalJobSchedules.Schedule_ID as currentScheduleID
                ,PrimaryJobSchedules.Name
                ,PrimaryJobSchedules.Schedule_id as primaryScheduleID

          FROM		LocalJobSchedules
          FULL JOIN	PrimaryJobSchedules
              ON	LocalJobSchedules.name = PrimaryJobSchedules.name
              AND LocalJobSchedules.schedule_id = PrimaryJobSchedules.schedule_id


    ' AS varchar(MAX))




INSERT INTO #JobSchedules
EXEC sp_ExecuteSQL @SQL

3. Copy

This time instead of copying from one to the other we just need to attach or detach jobs from their schedules. The code is pretty simple:

DECLARE @Job_Name sysname, @local_job_id uniqueidentifier, @current_schedule_id int, @primary_schedule_id int, @Action varchar(6)

DECLARE db_cursor CURSOR FOR  
SELECT local_job_id, job_name, current_schedule_id, primary_schedule_id, Action
FROM #JobSchedules
WHERE Action <> 'NOTHING'


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @local_job_id, @Job_Name, @current_schedule_id, @primary_schedule_id, @action

WHILE @@FETCH_STATUS = 0   
BEGIN   
  if @Action = 'INSERT'
  BEGIN
    EXEC msdb.dbo.sp_attach_schedule @Job_Name = @Job_Name,@schedule_id=@primary_schedule_id
  END
  
  if @Action = 'DELETE'
  BEGIN
    EXEC msdb.dbo.sp_detach_schedule @job_id = @local_job_id, @schedule_id = @current_schedule_id
  END

  
  FETCH NEXT FROM db_cursor INTO @local_job_id, @Job_Name, @current_schedule_id, @primary_schedule_id, @action
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

That’s it. All jobs and schedules have now been copied from the primary to the secondary, and all jobs on the secondary have the same schedule as the primary.

One thing I’ve noticed when doing this step is that I’m joining on schedule id instead of on schedule name. This is going to be a potential problem and will probably need revisiting at some point. Similarly, the step that replicates schedules will need to look at names instead of ids.

This pretty much wraps up the copy job. There is one more post coming with an optional step to disable jobs that you don’t want running on the secondary.

Leave a Reply