Replicating Jobs Between SQL Instances – Schedules
This is part 4 on the series of how to replicate SQL Jobs between instances.
Previously I’ve covered why replicate, setting 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:
- If a schedule is created on the target computer with the same id as the primary then it will be overwritten;
- 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.