How to keep SQL Jobs in sync in a AG

Chaitanya Kiran 776 Reputation points
2023-11-26T00:10:28.7966667+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,268 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 106K Reputation points MVP
    2023-11-26T09:43:47.9566667+00:00

    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.

    0 comments No comments

  2. Javier Villegas 900 Reputation points MVP
    2023-11-26T23:49:56.8433333+00:00

    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

    0 comments No comments