Sync Logins Between Alwayson Replicas and DB Mirroing

Sync SQL Logins

Method 1

You can use a couple of stored procedures provided by Microsoft. It’s not automatic, but it’s something you could do yourself. When you create the SPs and execute sp help revlogin script, you’ll get the scripts you need to generate the logins on the partner. Because it was developed for a one-time transfer, it isn’t the prettiest way, but it works..

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

Method 2

SSIS can be used to synchronize SQL logins. There are a few issues with this strategy, but I’ll leave that to the reader to figure out. The good news is that it’s all done automatically.

https://docs.microsoft.com/en-us/sql/integration-services/control-flow/transfer-logins-task

Method 3

Databases that are contained. The complete user authentication method is kept within the database in a contained database, rather than depending on logins that are stored outside the database in Master. More information on enclosed databases can be found here..

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database

Method 4

This can be automated using an excellent script. This is the way that I like. It performs admirably and is simple to automate using SQL Agent job and can be used to schedule it.

Note : that this script uses Linked Servers and builds a stored procedure to manage the movement of Logins.

Please see the link below for the script, which I hope will be of great assistance to you.

https://sqlserveradmintutorial.blogspot.com/2018/01/sync-loginsusers-between-principal.html

DB Refresh Steps for Alwayson Configured Database

SQL Server AlwaysOn is a great feature developed by Microsoft that allows you to create a high-availability solution using an actual copy of the database in real-time synch mode. Now, as part of routine maintenance, we may need to perform a database refresh. I attempted to summarise all of the points and processes involved in carrying out such an activity. I hope the following information is useful:

Step 1: Make a backup or copy a backup from one location to another.
Step 2: Create a backup of the users in the pre-production database.
Step 3: In Pre-Prod, remove the database from the Availability Group.
Step 4: Using the copied backup file from the Source server, refresh the database in Pre-Prod.
Step 5: After the Pre-Prod DB has been restored, drop the users.
Step 6: Using the script from Step 2, create the users.
Step 7: Check if there are any Orphand Users and Sync the Users.
Step 8: Refresh\Update your Database statistics
Step 9: On pre-production, reconfigure the Alwayson AG setup.
Step 10: Check the AG status and make sure the database is synchronized once the configuration is complete.

I’ll be providing a script to Automate DB Refresh for Alwayson Configured Database activity for Alwayson Configured Database very soon, and the script can also be scheduled as a SQL Job.