Alwayson Logins Sync SQL Agent Job

There are lot of SQL Server Instances configured with Alwayson Availability Groups HADR in our environment.
When we create a login on Primary Replica of an Availability Group it will not be automatically moved or get sync with all secondary replicas
This will cause an issue(Failed login error messages) when a Failover occurs as in current Primary replica the login does not exist .

To avoid such issues I have googled for script and found one PowerShell script which suits ours requirement ( I have automated this long back ago , didn’t remember the site or link to provide here – thanks to the original Author who created this script ) we automated by creating a copy logins job on all replicas, here I am using a PowerShell script to perform our task
Creating an SQL Agent job which executes PowerShell script on all replicas and this job is smart enough to identify on which node it should execute.
On Primary replica When a new login creation or password update or changing\providing permissions occurs, as per the schedule when this job triggered the script will replicate all the changes or modifications done on Primary replica to all the secondary replicas.

Pre-requisites:

Need to have stored procedure ‘Sp_hexadecimal’ Script on all replicas.
This script will not do anything until there are any modifications done on Primary replica.
This script does not copy existing logins until you make any changes to them e.g. password change, grant/revoke privileges etc.

Schedule this job on all replica based on your environment and need.

Before implementing in Production environment please do testing

Created new  SQL Agent job Copy\Sync-Logins and added a Job Step Sync as shown below :

In Job Step given Step Name as ‘ Sync Logins’ and Type as ‘ Operating System (CmdExec) ‘ and command as ‘powershell -file “C:\temp\Sync-Logins.ps1”

click OK–>Schedule job–>OK–>OK, then the job gets created.

After creating the Job on Primary replica Script out and execute it on all secondary replicas.

Whenever there is a modification done on Primary replicas those will get replicated to all secondary when the scheduled job runs.

Please find the Full Script in below link:

https://drive.google.com/file/d/1BhbtBrqBwk_D1jdhHAw9A_idTMA83PFt/view?usp=sharing

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

One thought on “Alwayson Logins Sync SQL Agent Job”

  1. Thanks for your efforts…but simply we can achieve by creating the same login on secondary server with SID Details which creating…it will help in making the both server logins in Sync

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.