SQL Server Availability Groups – Syncing Logins and Jobs

availability-groupssql server

So I am configuring availability group which will sync the databases from primary replica to the secondary replicas.

How to sync the jobs and logins so that when there is failover to secondary then the logins and jobs can continue working on the secondary?

Best Answer

I have to credit David Browne for this one (I learned from one of his recent answers), but one thing you can do is setup a Linked Server between the your Primary and Secondary Replicas. Then you can create a SQL Agent Job on the Primary Replica that generates the scripts for the Logins and other Jobs and executes them on the Secondary via the Linked Server. You can even have the Job synchronize itself to the other servers as well. It of course should first check if the objects you're synchronizing already exist and take the appropriate action when they do.

You might find this article Synchronize logins between Availability replicas in SQL Server Always On Availability Groups helpful, especially the Use stored procedure sp_help_revlogin and sp_hexadecimal (which references this Microsoft BOL) section which discusses a Microsoft stored procedure you can utilize to transfer Logins between servers as well. You probably can simplify the job above by having it leverage this procedure.

As far as synchronizing the other Jobs, you might be able to do so with the meta-data stored in the the msdb Jobs tables, e.g. sysjobs, or this StackOverflow answer seems to have found a way via T-SQL. You might also find the Microsoft article Sync Logins and Jobs helpful as well.

Finally, depending on how many Secondary Replicas you are planning to setup, or if you have an affinity to SSIS, you might find it cleaner and more manageable to create an SSIS Package that does all of this (which is also discussed in the first article above) as opposed to using Linked Servers.

Either way, this is a moderate difficulty task to setup, but definitely accomplishable once you start diving into it.