SQL Server professionals have always been locked in the dilemma of which SQL Server High Availability solution to recommend to clients who require a cheap, easy to install and reliable solution. In truth both SQL Server Mirroring and Log Shipping meet those requirements; well, almost. Both of them guarantee business continuity by providing protection at the user-database level, reduce downtime during planned maintenance activities, do not require special hardware and would take minimal time to configure. Both are built by restoring backups of production databases to standby(s). That is where the similarities, sort of, ends!
At the most basic level, SQL Server mirroring consists of a primary server and mirror server – witness servers are only needed for synchronous failover modes. It works by redoing all inserts, deletes and updates (active transactions) on the primary, as quickly as possible, to the standby databases on the mirror server. To speed up the process and reduce IO footprint, these active transactions are compressed. Log Shipping, on the other hand, works by copying and restoring transactions log backups from the primary server onto duplicate copies of the user databases on the secondary server(s).
So when is it best to use either? Well, it depends. It depends on the business requirements of the client. SQL Server Mirroring is more suitable for business requirements where: automatic failover in times of disaster is very important to the client; the standby is required to be as close to production as possible, only a single standby is needed, when automatic user reconnection after failover is a required option and for SQL Server shops running versions of SQL Server from 2005 till SQL Server 2012. Log Shipping on the other, is more suitable for protection against single site failures because its standbys can be stretched over long distances to different sites. It suits business requirements that need the standbys open for limited read-only reporting or delayed to protect against logical errors. It is also the preferred option if more than one standby is required.
In summary, SQL Server Mirroring is more suitable for High Availability while Log Shipping is more of a Disaster Recovery solution. Both solutions can be combined in one deployment to achieve High Availability and Disaster Recovery in one swoop thus availing the deployment of the benefits of both solutions while eliminating their limitations.
PS: Following the release of SQL Server 2012 and the introduction of SQL Server AlwaysOn in the market place, Microsoft informed clients of their plans to decommission SQL Server Mirroring in future releases and advised them to start using SQL Server AlwaysOn and Log Shipping (if they hit application compliance issues with AlwaysOn) in their future deployments. True to this advice, Microsoft deprecated SQL Server Mirroring in SQL Server 2014 releases. In my opinion, I think SQL Server Mirroring still has a few more years life span for two main reasons: 1) SQL Server Mirroring has strong adoption in quite a good number of SQL Server shops and Microsoft has obligations to support them for some time. 2) Adoption of AlwaysOn has not been as strong and fast as Microsoft had hoped because of compliance issues with a number of applications in the market.
