Replicating Jobs Between SQL Instances – Schedules

By Greg No comments

This is part 4 on the series of how to replicate SQL Jobs between instances.

Schedule
Schedules

Previously I’ve covered why replicatesetting up and replicating categories. The next item to replicate is Schedules.

SQL Agent Schedules are used to tell SQL Agent Jobs when they should run. When you update a schedule, it’s not enough just to change the data in the msdb table. You need to notify SQL Agent that the data has changed. The better option is to always use the built in stored procs to add or update schedules. The built in stored procs will update the data and notify SQL Agent that a change has occurred.

The biggest problem when replicating schedules is working how to determine if the schedule already exists on the target server. The update stored procedure requires that you send through an ID or a name, but the name does not have to be unique. Why Microsoft designed it this way, I do not know, but it causes problems for the replication. You cannot update just on name alone because it’s not unique, but there is also no way to provide a unique ID, there is only the Identity Column that is automatically generated.

The approach we took was to create all schedules on the Primary server only, there are no schedules on the secondaries that are not created from the primary, and so we use schedule_id to compare schedules.

This has 2 risks:

  1. If a schedule is created on the target computer with the same id as the primary then it will be overwritten;
  2. If the schedule ID on the primary is less than the ID on the target then duplicate schedules will just continue to be created.

If you only ever create schedules on the primary server then neither of these will be a problem.

On to the code.

1. Setup

This is the same as the setup for categories. The script checks that it isn’t running on the primary server, creates a variable with the name of the primary server and creates a temp table.

IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName'))
BEGIN
  DECLARE	@PartnerServer sysname = 'PrimaryReplicaName'
  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


  --update schedules
  CREATE TABLE  #Schedules
  (
    Schedule_id int,
    ScheduleName sysname,
    Enabled tinyint,
    Freq_type int,
    Freq_interval int,
    freq_subday_type int,
    freq_subday_interval int,
    freq_relative_interval int,
    freq_recurrence_factor int,
    active_start_date int,
    active_end_date int,
    active_start_trim int,
    active_end_Time int,
    owner_login_name nvarchar(128),
    
    Action varchar(6)
  )

    --Find Schedules to Copy
 
    --Copy
END

 2. Find

Similar to the categories, just a bit more complex. Check if the schedule on the primary exists with the exact same setup on the replica.

IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName'))
BEGIN
    --SETUP
 
SET @SQL =	CAST('
        SELECT		*
        FROM
        (
          SELECT		msdbPrimary.schedule_id,
                msdbPrimary.name,
                msdbPrimary.Enabled,
                msdbPrimary.Freq_type,
                msdbPrimary.Freq_interval,
                msdbPrimary.freq_subday_type,
                msdbPrimary.freq_subday_interval ,
                msdbPrimary.freq_relative_interval ,
                msdbPrimary.freq_recurrence_factor ,
                msdbPrimary.active_start_date ,
                msdbPrimary.active_end_date ,
                msdbPrimary.active_start_time ,
                msdbPrimary.active_end_Time ,
                SUSER_NAME(msdbPrimary.owner_sid) as ownerloginname

                ,CASE	WHEN msdbLocal.schedule_id IS NULL THEN ''INSERT''
                    WHEN	msdbLocal.schedule_id IS NOT NULL
                      AND (
                            msdbLocal.name <> msdbPrimary.name
                          OR	msdbLocal.Enabled <> msdbPrimary.Enabled
                          OR	msdbLocal.Freq_type <> msdbPrimary.Freq_type
                          OR	msdbLocal.Freq_interval <> msdbPrimary.Freq_interval
                          OR	msdbLocal.freq_subday_type <> msdbPrimary.freq_subday_type
                          OR	msdbLocal.freq_subday_interval <> msdbPrimary.freq_subday_interval
                          OR	msdbLocal.freq_relative_interval <> msdbPrimary.freq_relative_interval
                          OR	msdbLocal.freq_recurrence_factor <> msdbPrimary.freq_recurrence_factor
                          OR	msdbLocal.active_start_date <> msdbPrimary.active_start_date
                          OR	msdbLocal.active_end_date <> msdbPrimary.active_end_date
                          OR	msdbLocal.active_start_time <> msdbPrimary.active_start_time
                          OR	msdbLocal.active_end_Time <> msdbPrimary.active_end_Time
                          OR	SUSER_NAME(msdbPrimary.owner_sid) <> SUSER_NAME(msdbPrimary.owner_sid)
                        ) THEN ''UPDATE''
                    ELSE NULL
                END as Action
          FROM		' + QUOTENAME(@Machine) + '.msdb.dbo.sysSchedules as msdbPrimary
          LEFT JOIN	msdb.dbo.sysSchedules msdbLocal
              ON	msdbPrimary.schedule_id = msdbLocal.schedule_id
        ) InsertUpdate
        WHERE	Action IS NOT NULL
      ' AS varchar(MAX))

  INSERT INTO #Schedules
  EXEC sp_ExecuteSQL @SQL

 
    --Copy
END

 3. Copy

As discussed in the categories post, the easiest way to copy these is with a cursor so you can call the insert or update stored proc on every record that needs inserting or updating.

IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName'))
BEGIN
  --Setup
  --Find Categories to Copy
  
 DECLARE @schedule_id INT,
 @schedule_name SYSNAME,
 @enabled TINYINT = 1, -- Name does not have to be unique
 @freq_type INT = 0,
 @freq_interval INT = 0,
 @freq_subday_type INT = 0,
 @freq_subday_interval INT = 0,
 @freq_relative_interval INT = 0,
 @freq_recurrence_factor INT = 0,
 @active_start_date INT = NULL, -- sp_verify_schedule assigns a default
 @active_end_date INT = 99991231, -- December 31st 9999
 @active_start_time INT = 000000, -- 12:00:00 am
 @active_end_time INT = 235959, -- 11:59:59 pm
 @owner_login_name sysname = NULL,
 @Action VARCHAR(6)


 DECLARE db_cursor CURSOR FOR 
 SELECT Schedule_ID,
 ScheduleName,
 Enabled,
 Freq_type,
 Freq_interval,
 freq_subday_type,
 freq_subday_interval,
 freq_relative_interval,
 freq_recurrence_factor,
 active_start_date,
 active_end_date,
 active_start_trim,
 active_end_Time,
 owner_login_name,
 Action
 FROM #Schedules


 OPEN db_cursor 
 FETCH NEXT FROM db_cursor INTO @Schedule_id
 ,@schedule_name 
 ,@enabled 
 ,@freq_type 
 ,@freq_interval 
 ,@freq_subday_type 
 ,@freq_subday_interval 
 ,@freq_relative_interval 
 ,@freq_recurrence_factor 
 ,@active_start_date 
 ,@active_end_date 
 ,@active_start_time 
 ,@active_end_time 
 ,@owner_login_name 
 ,@Action 

 WHILE @@FETCH_STATUS = 0 
 BEGIN 
 if @Action = 'INSERT'
 BEGIN
 exec msdb.dbo.sp_add_schedule @schedule_name 
 ,@enabled 
 ,@freq_type 
 ,@freq_interval 
 ,@freq_subday_type 
 ,@freq_subday_interval 
 ,@freq_relative_interval 
 ,@freq_recurrence_factor 
 ,@active_start_date 
 ,@active_end_date 
 ,@active_start_time 
 ,@active_end_time 
 ,@owner_login_name
 END
 
 IF @Action = 'UPDATE'
 BEGIN
 exec msdb.dbo.sp_update_schedule @schedule_id = @schedule_id
 ,@new_name = @schedule_name
 ,@enabled = @enabled 
 ,@freq_type = @freq_type 
 ,@freq_interval = @freq_interval 
 ,@freq_subday_type = @freq_subday_type 
 ,@freq_subday_interval = @freq_subday_interval 
 ,@freq_relative_interval = @freq_relative_interval 
 ,@freq_recurrence_factor = @freq_recurrence_factor 
 ,@active_start_date = @active_start_date 
 ,@active_end_date = @active_end_date 
 ,@active_start_time = @active_start_time 
 ,@active_end_time = @active_end_time 
 ,@owner_login_name = @owner_login_name
 END
 

 FETCH NEXT FROM db_cursor INTO @Schedule_id
 ,@schedule_name 
 ,@enabled 
 ,@freq_type 
 ,@freq_interval 
 ,@freq_subday_type 
 ,@freq_subday_interval 
 ,@freq_relative_interval 
 ,@freq_recurrence_factor 
 ,@active_start_date 
 ,@active_end_date 
 ,@active_start_time 
 ,@active_end_time 
 ,@owner_login_name 
 ,@Action 
 END 

 CLOSE db_cursor 
 DEALLOCATE db_cursor


 drop table #Schedules
END

The code is a bit longer than categories, but that is just because there are more fields to copy. It is just as simple as last time. Open a cursor, select all of the data that needs to be inserted or updated into fields, decide if the action is INSERT or UPDATE and then call the stored proc to insert or update the data. Finally, get the next record from the cursor.

 

That’s it! Schedules are now being replicated between SQL instances in the AAG. Next up is how to replicate the actual Job.

Leave a Reply