SQL Server Data Recovery Showdown: Replication versus Log Shipping

October 14th, 2009 by Jason C.

Chances are the data stored in your database are of critical business importance. Employee data, customer data, history and trending, basically all of the information that composes your organization. Therefore, it’s critical to be able to recover that data in the case of device failure or other disaster scenario.

Because of their ability to be used in a geographic redundancy configuration, two contenders for the task come to mind: transactional/snapshot replication and log shipping. Let’s see if we can narrow that choice down with a little critical comparison.

Price

Both solutions will require an additional SQL Server instance to act as a publisher and another SQL Server instance to house the standby database. Replication requires that your servers running the publishing and distribution roles have to run SQL Server Standard Edition 2008 or better. The server holding the subscriber database can use any edition of SQL Server 2008 including the free Express edition. Log shipping can work with SQL Server Web Edition 2008 licensing on each end.

Winner: Log shipping, running Web Edition SQL Server.

Flexibility

Replication is more granular than log shipping, in that you can specify certain tables, columns, or filtered ranges to replicate. This amount of flexibility can help you minimize the overhead consumption of resources that replicating data can consume, if for example, the only data worth protecting are some records from certain tables.

Because of the granular nature of replication, adding a new table requires you to take a new snapshot and set up replication rules for a new table. This limitation doesn’t exist for log shipping, in which the new table SQL execution is logged and shipped automatically.

Winner: Replication, if you only need to protect a very small portion of your dataset.

Completeness

As discussed in flexibility, to add a new table to the mix with replication, a snapshot of the table must be taken and initially sent to the subscriber database as a bulk copy. This also adds a lot of overhead to the process, making it difficult to find an opportune time to spend the required system resources.

Log shipping ensures that all data from the database is recoverable, not just the portions you remembered to replicate. The log shipping process also requires lesser overhead and runs on a scheduled basis.

Winner: Log shipping, which requires you to capture the entire dataset.

Ease and Speed of Recovery

With replication, a subscriber database is fully accessible at all times but it is not recommended that you make changes at the subscriber unless you are using during a disaster recovery scenario as this could break your replication. In fact, in the case of any unresponsiveness of the primary database, you can configure failover to a subscriber instance for a nearly seamless switch. The standby server with log shipping must be brought out of its recovering state in order to regain usefulness. The speed of this process varies by size of your dataset, and the relative complexity of recovery compared to replication might be a turnoff for do-it-yourself.

Winner: Replication, which creates immediately useful database instances.

Overall

When the goal is data recovery, you probably don’t want to leave it to chance that you’ve remembered exactly which data might need to be recovered. When disaster strikes, there’s already enough chaos and panic, so why add to your worries that you might have left something out? Waiting for a standby server in a log shipping configuration to come out of its recovering state is a small price to pay, especially if you have experience with the process or outsource to a managed service provider. Because of those reasons, and the fact that it can potentially save you licensing fee costs, the winner for data/disaster recovery is: Log Shipping.