How to keep SQL Jobs in sync in a AG

Question

I have an AG. The SQL Agent jobs on primary gets modified frequently. How to keep AG jobs in sync between primary and sesondary replica

Answer 1

The first post I found when googling the question was https://dba.stackexchange.com/questions/292085/how-to-sync-logins-and-jobs-when-using-an-availability-group. The answer provides a couple of ideas.

To this I like to add is that in SQL 2022, there is a new feature Contained Availability Groups. In a contained AG, the AG has its own master and msdb which are part of the AG itself, which evades the need to sync jobs.

Yet an option is to define the jobs in such a way that it does not matter which node is the primary. That is, rather than making the job T-SQL job steps, you make them CmdExec job steps than runs the job through SQLCMD connecting to the listener. In this case, the jobs would only be one node. Which obviously to some extent forfeits the idea with having an AG, because if that node is down, the jobs will not run.

Answer 2

Hi @Chaitanya Kiran

In addition to the answer above, I'll suggest you to try DBA Tools Sync-DbaAvailabilityGroup. This is the one I use

https://docs.dbatools.io/Sync-DbaAvailabilityGroup.html

Regards

Javier