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.

1. Database mirroring can you leave your source database unusable

I blogged about this issue back in 2010: Top 5 Myths of Database Mirroring. When you mirror a database to a higher level version, at the point of failover, the mirroring session attempts to upgrade the source database. In some cases, it may successfully upgrade it to a certain point before the upgrade fails and the mirroring session is suspended.

If for some reason after failover, you determine that the new database isn’t usable (for example, if the clients determine they cannot connect to the new server), then you may need to revert back to the original server. If you drop the mirroring session and try to bring the original database back online, you might then get error message 943:

At this point, your only option is to restore the database from backup or upgrade the instance.

2. You can log ship to multiple secondary instance

Let’s say you have a really large database than you need to migrate to SQL Server 2016 and add it to an Availability Group (AG). One example that was used recently in a conversation was a 2 TB database so let’s assume it’s a 2 TB database.

If you use database mirroring as your migration tool, then you will only have a single copy of the database on the new servers. So after failing over the mirrored database and bringing the new copy online, you still have do the full backup and restore process to initialize the secondary to add it to the AG. The process to do this migration and add to the AG goes something like this:

1. Mirror the database from the original instance to the new primary instance. (backup, restore, etc) – several hours
2. Fail over the database to the new primary instance. – a few seconds
3. Drop mirroring. – a few seconds
4. Back up the 2 TB database. – a few hours
5. Restore the backup of the 2 TB database. – a few hours
6. Back up the log of the 2 TB database. – several minutes
7. Restore the log of the 2 TB database. – a couple minutes
8. Add to the AG. – a few minutes

If I was doing this migration, I would use log shipping. The beauty of using log shipping is that I can log ship the database to multiple destination databases. I would log ship the database to both the primary and secondary instances for the new AG. This process would look like this:

1. Log ship the database from the original instance to both the new primary and secondary instances. (backup, restore, etc) – several hours
2. Fail over the database to the new instances. – several seconds
3. Bring the new primary online. – a few seconds
4. Join the database to the AG. – a few seconds

Using log shipping, I can go straight from failover to having the database in an AG in a matter of a few seconds, not hours. The set up time will be a little more work because I’m setting up 2 secondaries (2 sets of restores) during set up time, but those can run at the same time so the difference in set up time is negligible. The big difference is I don’t have to go through the restore process for the secondary database because the databases came from the same source database, their log chains are intact, and their logs are in sync already.

The failover process for this log shipping scenario does have to be performed in a very specific manner. I wrote on this process previously in my article for SQL Server Pro magazine called 3 Log Shipping Techniques back in 2011. It is actually a very simple process once you understand it. For the scenario above, the failover process would look like this:

1. Disable the log shipping jobs to ensure that they do not run any more.
2. Run each of the log shipping jobs, one at a time, in order to make sure they did not leave any uncompleted work.
3. Drop log shipping leaving both secondaries in a restoring mode.
4. Back up the log of the original database using the NORECOVERY option. This will create a new log backup and put the original database in a restoring mode essentially taking it offline. This gives you 100% assurance that there are no transactions occurring that are not already backed up.
5. Restore the final log backup using the NORECOVERY option on both the new primary and new secondary instances of the database.
6. Recover only the primary instance of the database using:

7. You’re now ready to join the database to the AG using the “JOIN ONLY” option.

Closing Arguments

The two reasons provided above are huge, in my opinion. I’ve literally migrated thousands of databases over my years, and the difference is even more obvious when you are doing the above for a couple dozen databases at the same time. Deprecation of database mirroring shouldn’t be ignored as a reason, but it is really minor compared to the two reasons I explained.

^*Reposted with permission from SQLSoldier.com.