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


One response to “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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: