0

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?

2 Answers 2

2

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.

1
  • 1
    How about dbatools via powershell?
    – variable
    Jan 5 at 13:32
0

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
4
  • Problem is that if changes are made on primary then how to sync them to secondary. As this works only like a copy. Not sync.
    – variable
    Feb 2 at 18:04
  • You are right, you can't sync your changes with copy. I edited the answer and I added Sync-DbaAvailabilityGroup as possible command also from dbatools. Is it what you need?
    – kesadae11
    Feb 2 at 21:29
  • That sync command too behaves like a copy
    – variable
    Feb 3 at 3:32
  • Sorry I was a bit confused. There is an optional parameter for all these 3 commands named -Force, which overwrites the objects on the destination if these already exist. It sounds like a bit overkill because it overwrites everything, not just the changes, but if your primary is the "only source of truth" it couldn't be a problem.
    – kesadae11
    Feb 3 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.