Replicating Jobs Between SQL Instances – The Setup

Replicating Jobs Between SQL Instances – The Setup

After deciding to replicate SQL Agent Jobs between SQL Instances using a SQL Agent job, there were a few things to setup and test.

1. We need a way to check if the server is the primary in the AlwaysOn Availability Group, so that jobs can decide if they need to run;
2. We needed a way to communicate with the primary server;
3. We needed to confirm that a SQL job could drop and recreate itself

1. Check if this server is the Primary

Because we are using AlwaysOn Availability Groups, this is actually quite easy for us to achieve. We created a function in the master database on every server called fCheckIfPrimary:

CREATE FUNCTION [dbo].[fCheckIfPrimary]
(
    @ReplicaName VARCHAR(500) = 'PrimaryReplicaName' 
) 
RETURNS BIT AS 
BEGIN 
    if (
        select      ars.role_desc 
        from        sys.dm_hadr_availability_replica_states ars 
        inner join  sys.availability_groups ag 
                on  ars.group_id = ag.group_id 
        where       ag.name = @ReplicaName 
                and ars.is_local = 1 
    ) = 'PRIMARY' 
    begin -- this server is the primary replica
        return 1 
    end 
    else 
    begin -- this server is not the primary replica
        return 0 
    end 
 
    return 0 
END

Basically, just check if this is the primary replica. If it is then we don’t need to replicate jobs.

2. Setup a linked server

Again, AlwaysOn Availability Groups make our life easier here. Because an AAG has a listener, we can create a linked server from every member of the AAG to the listener.

Linked Servers have potential risks that should be assessed, such as security problems if you hard code the SA username and password into them, and there are possible performance problems. We set our up using the security context “Be made using the login’s current security context.” This limits a user to only be able to do tasks that they could do on the linked server anyway.

3. Test that a Job can Recreate itself

The ideal for us would be that the job that is replicating other jobs can also replicate itself (after the first run). This means we can change the replication job once on the primary and it would be recreated on all of the replicas. To test this out, we created a simple test job and scripted out the drop and create command from SSMS. We put that command back into the test job so that it would drop and create itself every time it ran, and we ran it. It turns out SQL Agent can delete the job that is currently running and create a new one with the same name. Not sure if that’s intentional or not, but it’s awesome for what we’re trying to achieve.

That is the setup basically done. In the next posts I’ll take you through the steps of the job that need to run.