Sync Logins Between Alwayson Replicas and DB Mirroing

Sync SQL Logins

Method 1

Microsoft provides a couple of stored procedures you can use.  It’s not automated, but you could automate it yourself.  Once you create the SPs and run sp_help_revlogin it gives you the scripts to run on the partner to create the logins.  It’s not the prettiest method since it was designed for a 1 time transfer, but it works.

Method 2

You can use SSIS to sync SQL logins.  This method has some gotchas, but I’ll leave that to the reader.  The good news is that it is automated.


Method 3

Contained databases.  A contained database keeps the entire user authentication mechanism within the database without relying on logins that sit outside the database in Master.  You can read more about contained databases here.

Method 4

There is an awesome script to automate this process.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent job on sql.

Note : This script creates a stored procedure to handle the move and also uses Linked Servers.  

For script please go through below link and hope this helps you a lot.

DB Refresh for Alwayson Configured Database

Alwayson Overview :

AlwaysOn Availability Groups is the big new feature in the High Availability stack of SQL Server 2012. Simply said it is the successor of Database Mirroring that was introduced with SQL Server 2005 SP1. Database Mirroring is a great technology for achieving HA (High Availability) and DR (Disaster Recovery), but has some weaknesses and limitations:

  • You can only mirror 1 database through a Database Mirroring session. When you have an application that consists of several database (just think of SharePoint Server), then you need to have several distinct Mirroring sessions.
  • The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
  • There is only 1 Mirroring partner possible. You can’t mirror to different partners. The Mirroring partner can be run with Synchronous Commit for achieving High Availability or with Asynchronous Commit for achieving Disaster Recovery. If you are using Synchronous Commit you can get an Automatic Failover if you deploy a Witness instance. This witness instance is used for acquiring a quorum and avoiding a so-called Split-Brain scenario.
  • The Mirroring database is always replaying Transaction Log records, which means that you are not able to access the database for read-only access (the undo part of Recovery has not yet run). The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis, but again – you have to do this at your own.

Beginning with SQL Server 2012 Microsoft provides us now AlwaysOn Availability Groups which are the successor of Database Mirroring.

Side Note: Database Mirroring is still available in its original feature set, but it is marked as a Deprecated Feature, which means, that it will be removed in a future release of SQL Server.

AlwaysOn Availability Groups offers you the following advantages over traditional Database Mirroring:

  • Multi-Database Failovers
  • Multiple Secondaries
  • Active Secondaries
  • Integrated HA Management

Please find the steps to perform DB Refresh Activity for Alwayson Configured Database from below link :

Very soon I will provide script to Automate DB RefresDB Refresh for Alwayson Configured Database activity for Alwayson Configured Database and the script can be scheduled as SQL Job.