When Database Mirroring was introduced in SQL Server 2005, it seemed reasonable to assume that log shipping would gradually go out of fashion. Mirroring is a way of introducing high-availability to SQL Server by allowing the secondary server to become the main server instantly, in a way that is invisible to the applications using the database. This, in theory, is the dream solution for the .NET application developer: the application needs to do no work at all to take advantage of this resilience against hardware failure.

Log Shipping has been around for a lot longer, first as a sort of “home-brew” solution that the intrepid DBA could make work with version 6.5 and 7.0, before an official version was built into the enterprise edition of SQL Server 2000. It is a method, based on SQL Server Agent jobs, by which the transaction log backups from a primary server are applied to secondary servers. In this way, one can keep one or more spare ‘warm standby’ servers in a state of readiness to take over in the event of failure of the primary. It works well, and requires just a few mouse-clicks to set up, but it requires some nimble footwork from the DBA to switch the standby into becoming the primary server, and generally entails 15-30 minutes of downtime. In addition, application client redirection requires some manual changes.

Curiously, despite its many stated advantages and heavy support from Microsoft, Mirroring hasn’t replaced Log shipping, as predicted. Aside from the obvious fact that many shops still have SQL 2000 machines and so have no choice but to use log shipping, there are a number of other subtle and not-so-subtle reasons for its continued prevalence.

Many HA setups require more than one standby server, which mirroring cannot provide. Other DBAs require that delay time that is intrinsic in Log Shipping to protect against logical errors that, with mirroring, would have instantly been carried across to the standby. In a sense, Log Shipping also provides “free standbys”, since it makes use of backups would need to be made anyway and, to the delight of many DBAs, checks the integrity of the backup for free.

This final point hints at perhaps the biggest reason why log shipping still prevails. Many DBAs regard the High Availability aspect of log shipping as a “happy by-product” of their need to offload reporting functions to a secondary server. In a mirrored setup, the target database is never online, or accessible to the end user, until failure occurs on the source. The only way to get around this is to set up database snapshots on the target, which of course incurs the expense of Enterprise Edition. With log shipping, all you need is Backups, and a few cheap standby servers running SQL Server Express, and you have an effective reporting solution, with “poor man’s High Availability” thrown in!

However, although rumours of log shipping’s demise have been exaggerated, it may still be only a stay of execution. Microsoft may finally relent on the Enterprise requirement for snapshots. With the advent of server virtualization technologies, it will surely become easier to maintain ‘standby’ virtual servers which can be deployed even more quickly in the event of either physical or logical failure.

DBAs have stuck with log shipping for good reasons that were un-guessed at by the pundits, but does it have a long-term future as a high-availability strategy?

As always, we’d love to hear your opinion. Add your comments below (you’ll need to be signed in), and the best contribution will receive a $50 Amazon voucher.

Cheers,

Tony.