After deciding to replicate SQL Agent Jobs between SQL Instances using a SQL Agent job, there were a few things to setup and test.
- 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;
- We needed a way to communicate with the primary server;
- 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.