Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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 moderately difficulty task to setup, but definitely accomplishable once you start diving into it.
As you commented in the previous answer dbatools is an excellent tool for sync.
Copy-DbaAgentJob and Copy-DbaLogin or Sync-DbaAvailabilityGroup what you need, you can schedule your PowerShell script with a Windows scheduler or with SQL Server and of course, there are many 3rd party tools for it.
At the end of this article you can find a very good example code.
In my own opinion if you are running on a windows server environment this is the best choice.
Use -Force optional parameter if you want to overwrite the existing jobs and logins. Be careful when you overwrite logins! - "If this switch is enabled, the Login(s) will be dropped and recreated on Destination. Logins that own Agent jobs cannot be dropped at this time."
Examples:
Copy-DbaAgentJob -Source "SqlServerA" -Destination "SqlCluster" -Force
Copy-DbaLogin -Source "SqlServerA" -Destination "SqlCluster" -ExcludeSystemLogins -Force
