Replicating Jobs Between SQL Instances – Categories

February 28, 2016 By Greg No comments

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.

This is part 3 of how to Replicate Jobs Between SQL Instances. If you haven’t already, you might like to check out parts one and two before continuing.

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.

1. Setup

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.

2. Find

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.

3. Copy

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.

Replicating Jobs Between SQL Instances – Categories