Conceptually, replicating jobs between servers is straight forward. There are 2 things to do:
- If the job doesn’t exist, create it; and
- If the job exists, check that it’s the same, and if not, update it
We don’t have a requirement to delete extra jobs, but that would add a simple third step.
The problem with SQL Jobs is that there are so many parts to replicate. The jobs, job steps, schedules, job servers and the categories all need to be replicated.
Categories is the easiest of the parts to replicate, and will serve as the model for how to replicate the others. Categories is also unique in that there is no updates required. A category either doesn’t exists for a class or it does, so we either insert it or take no action.
There are 3 parts to all of the scripts that copy jobs from one server to the other.
In the setup phase, the details of the master database are set, and a check is run to make sure that the server running this is not the master database (remember, this job exists on all servers INCLUDING the master).
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 CREATE TABLE #JobCategories ( name sysname, Action varchar(6) ) --Find Categories to Copy --Copy END
Basically, call the function fCheckIfPrimary that was detailed in the setup blog of this series to see if this is the primary server. If it isn’t, then set the @PartnerServer and @Machine variables to point to the link server (again, detailed in the setup blog). A temp table is also created to store the categories that need to be inserted.
Except for the temp table, the setup stage will be the same in all copy steps of the job.
In this stage, the categories of the master server are queried, and compared with the categories on the local server. An action is then decided for each of the categories on the master. Because the master server is worked out dynamically, we need to use dynamic SQL to run this query. The code has two parts – build the query into a nvarchar variable, and then execute the SQL, saving the result into a temp table
IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName')) BEGIN --SETUP SET @SQL = CAST(' SELECT * FROM ( SELECT msdbPrimary.name ,CASE WHEN msdbLocal.category_id IS NULL THEN ''INSERT'' ELSE NULL END as Action FROM ' + QUOTENAME(@Machine) + '.msdb.dbo.syscategories as msdbPrimary LEFT JOIN msdb.dbo.syscategories msdbLocal ON msdbLocal.name = msdbPrimary.name AND msdbLocal.category_type = msdbPrimary.category_type AND msdbLocal.category_class = msdbPrimary.category_class ) InsertUpdate WHERE Action IS NOT NULL ' AS varchar(MAX)) INSERT INTO #JobCategories EXEC sp_ExecuteSQL @SQL --Copy END
The dynamic SQL queries the Primary MSDB syscategories table and does a left join to the local on the category name, class and type. We cannot use the ID’s for categories (or anything else) as they may be different between servers.
Finally, the copy part. As mentioned before, the copy for Categories is easy because all we ever need to do is insert new records, not change existing ones.
Originally, I inserted directly into the
msdb.dbo.syscategories table, but the problem with that is the SQL Agent process doesn’t get notified of the change until it reads the table again (either forced to because of another update, or because the process restarted). This is a bigger problem for schedules, as they do not update and enable in SQL Agent, but the same principle applies to categories.
To notify the SQL Agent process of the change, the stored proc
msdb.dbo.sp_add_category needs to be called. To call a Stored Procedure means we need a cursor.
Cursors are not evil in themselves, their bad name has come from poor use by developers and DBAs – the people who should know better. SQL works better with set based operations, but sometimes there is a need to run operations sequentially. Some DBAs and Developers are so scared of Cursors that they come up with a weird loop that essentially does the same thing, but uglier. I don’t think this is a better option, but that’s a discussion for another blog.
Back to the script:
IF 0 = (SELECT master.dbo.fCheckIfPrimary('PrimaryReplicaName')) BEGIN --Setup --Find Categories to Copy DECLARE @Name sysname, @Category_Class int, @Action varchar(6) DECLARE db_cursor CURSOR FOR SELECT name, Action FROM #JobCategories OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name, @Action WHILE @@FETCH_STATUS = 0 BEGIN if @Action = 'INSERT' BEGIN exec msdb.dbo.sp_add_category @name = @name END FETCH NEXT FROM db_cursor INTO @name, @Action END CLOSE db_cursor DEALLOCATE db_cursor drop table #JobCategories END
It’s a pretty simple script, the
if @Action = 'INSERT' is probably not required, as everything in there should say INSERT. I’ve elected to keep the check so that it looks like the other steps which might update or insert.
That’s about it for copying Job Categories. Running this step on the replicas will copy all of the categories from the primary.
Next up is Schedules.