Synchronizing Logins And Jobs

Ryan Adams enumerates several methods for synchronizing logins and SQL Agent jobs across mirrored instances or Availabilty Group replicas:

There is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent or you can just use Scheduled Tasks in the OS.  The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server.  Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.

Chrissy wrote this with the intent of making migrations easier, and she succeeded.  In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier.  The advantage here is that you can automate a lot more than than just logins.  In fact you can migrate and automate pretty much anything at the server level.  Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.

DBATools would be my preference in this situation as well, but click through to see four other methods, as well as code.

The Value Of Log Shipping

Robert Davis explains that log shipping can be better than mirroring for database migrations:

This topic has come up several times recently, so I feel the need to blog on it. As the person who wrote the book on Database Mirroring, it will probably come as a surprise to many of you that I believe that log shipping is a much better tool for database migrations than database mirroring.

I’m not just talking about the fact that database mirroring is deprecated (since SQL Server 2012) and log shipping is not. Both are still in SQL Server to this day. Because database mirroring is deprecated, it is no longer receiving bug fixes (except maybe critical security bugs) and no work is being done to make sure that it works with new features in current and future versions. Log shipping is still receiving both of these things. I will lay out the real reasons below.

Robert makes two compelling arguments in favor of log shipping.

TDE With Database Mirroring

I have a post on setting up database mirroring when the underlying database uses Transparent Data Encryption:

 Now it’s time to take some backups. First, let’s back up the various keys and certificates:

USE [master]
--Back up the service master key
--Note that the password here is the FILE password and not the KEY password!
BACKUP SERVICE MASTER KEY TO FILE = 'C:\Temp\ServiceMasterKey.key' ENCRYPTION BY PASSWORD = 'Service Master Key Password';
--Back up the database master key
--Again, the password here is the FILE password and not the KEY password.
BACKUP MASTER KEY TO FILE = 'C:\Temp\DatabaseMasterKey.key' ENCRYPTION BY PASSWORD = 'Database Master Key Password';
--Back up the TDE certificate we created.
--We could create a private key with password here as well.
BACKUP CERTIFICATE [TDECertificate] TO FILE = 'C:\Temp\TDECertificate.cert'
    WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificatePrivateKey.key', ENCRYPTION BY PASSWORD = 'Some Private Key Password');

Click through for the details.

Asynchronous Mirroring And Asynchronous AGs

Tara Kizer contrasts asynchronous database mirroring with asynchronous Availability Groups:


For asynchronous Database Mirroring, all we need is two servers: the principal at the primary site and the secondary at the DR site. Setup async mirroring between the two, and you’re done. If the secondary server goes down, production still continues. The transaction log isn’t clearing when the log backups occur because the principal still needs to send those log records to the secondary. As long as you have enough disk space where the transaction log resides to support this until the secondary server comes back online, production still continues. Of course if you run out of disk space, users will start receiving errors. But that can take some time and often times is enough time to get the secondary back online.

Availability Groups are more difficult to implement than database mirroring, but the flipside is that they’re much more powerful.

Database Mirroring

Derik Hammer has chosen Database Mirroring as his favorite feature:

With the end of SQL Server 2005, we also will soon see the end of database mirroring. There is a new feature releasing with SQL Server 2016 called Basic Availability Groups. This is the replacement for database mirroring. The use cases and limitations will appear very similar to database mirroring but it will use the Availability Group technology. In theory this will be like a stim-pack for the database mirroring feature while leaving it available in Standard Edition. Let’s cross our fingers that the Windows Failover Cluster components don’t slow down the failovers like it did with AGs.

A bold choice, but that “available in standard edition” thing is huge for smaller organizations which can’t afford Enterprise (especially with The Licensing Changes of 2012).

Granting Permissions In AGs Or On Mirrors

Matan Yungman discusses how to grant permissions only to the replica database in a mirroring or Availability Groups scenario:

You work with Database Mirroring or AlwaysOn AG, and you want to make sure your end users work only on the secondary server. How should you do that?

This solution feels a little hacky to me.  There’s enough value in it that I could see companies doing this, but it’d be nice if there were an easier way.

Check Endpoint Security

Erik Darling ran into an issue with endpoint security while setting up mirroring:

This is the error text:

The ALTER DATABASE command could not be sent to the remote server instance ‘TCP://’. The database mirroring configuration was not changed. Verify that the server is connected, and try again.


Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.

I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?

These things always happen right before bed, right before the big meeting, right before lunch.  They never happen on a slow Tuesday afternoon, it seems…


July 2017
« Jun