possibility of auto sync of SQL logins and SQL jobs from primary to secondary in always on

Question

auto sync of SQL logins and SQL jobs from primary to secondary in always on

Answer 1

If you are on SQL 2022, you can create a Contained Availability Group to relieve yourself from this problem.

Answer 2

Hi @Rajesh Kumar,

You might find this article Synchronize logins between Availability replicas in SQL Server Always On Availability Groups helpful.

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.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Also can you suggest whether we could move the jobs created in primary can be moved to secondary?

You can script them in SSMS and then copy to the other server.

You will need to add something to prevent the job from running on the secondary. A common way is to have a first job step which attempts to access the database. Then you mark the step as "quit the job and report success" in case of an error. The second step of the job has the real action.