Standby Servers: Log-Shipping vs Transactional Replication vs Database Mirroring

Article by Paul Ibison

Introduction

Back in July 2004 I wrote a simple article comparing log-shipping and transactional replication. In many ways these 2 technologies are similar as they both distribute data from publisher to subscriber and are talked about as solutions to both disaster recovery and the need to offload reporting requirements. With the advent of SQL Server 2005 there was a new technology which exists in a similar domain: "database mirroring". SQL Server 2005 also made significant changes to the way transactional replication may be configured with a view to simplifying the high-availability setups. So overall the choice is slightly more complicated and this article tries to simply outline the most important points. Each technology has slightly different naming conventions so I'll standardise on the terms "Publisher" and "Subscriber" to describe the 2 computers involved.

Key Differences

What is the latency?

Log-shipping can backup as frequently as once every minute and the copy and load frequency can also be every minute. If you use transactional replication the latency can be as low as a few seconds, assuming the relevant -POLLINGINTERVAL agent parameters are minimized. Database mirroring can have latencies lower than a minute. All this obviously depends on throughput and bandwidth constraints so it is only a rough guideline.

Is the schema altered at the publisher?  

Log-shipping does not alter the publisher's schema. Transactional Updating subscribers will add a GUID column. This may cause some queries on the publisher to fail, e.g. if you had (very poor :)) TSQL inside a stored procedure which did the following:

INSERT INTO ExistingTable
SELECT * FROM ReplicatedTable

Quite what effect this schema change will have on your applications only you will be able to determine....

Is the schema altered at the subscriber?  

Log-shipping makes no alterations to the schema. Does transactional replication alter the subscriber's schema? This depends on how it is set up! For SQL Server 2000 we had to jump through all sorts of manual hoops to get the schemas identical. For SQL Server 2005 it is now possible to replicate the PKs as PKs and take both the the identity properties and timestamp columns without modifications. So, provided the article properties are chosen well, we can have identical schemas. See this article for more details. There is one caveat here that the data itself in Transactional replication won't be identical when comparing the publisher and subscriber in all cases. This might sound bizarre but the differences concern timestamp columns. You can't explicitly enter a value in a timestamp column, so the subscriber will be using its own values rather than the publisher's values. This isn't really an issue for optimistic locking scenarios, but it can be an issue when you are using data comparison tools, although the modern suite of tools seem to allow for selective omitting of columns.

Are there schema requirements?

There are no requirements for log-shipping or database mirroring whereas all forms of transactional replication require primary keys on table articles.

Can we select individual articles?  

Log-shipping and database mirroring take the whole set of tables, stored procedures, triggers etc existing in the published database, while replication has the granularity to select articles individually. I've never come across an application which shares a database with other apps and which would have separate DR requirements, so I personally doubt that this is useful in terms of failover servers.

Is the Standby database 'protected'?  

Log-shipping restores the logs to the standby server with NoRecovery or with Standby - both options disallow edits to be made to the data. Replication doesn't enforce such a restriction so explicit permissions would be required to prevent changes to subscriber data. In database mirroring the database is inaccessible and therefore completely "safe".

Is system data transferred?  

A front-end application usually requires the presence of some system data. What I am referring to here is data in the system catalog or the database catalog: logins, messages, users, permissions etc. In the case of log-shipping and database mirroring, the database catalog is transferred but not the system catalog, while replication transfers neither.

Is the subscriber/standby server useable as reporting server?

Log-shipping can't usually allow the standby server to be used for reporting an exclusive lock is required on the database while the log is being restored so prior to the restore the users are forcibly removed.

Replication can allow reporting queries to be run. There might be concurrency issues as with any multi-user system and these can be offset by using dirty reads or by using the READ_COMMITTED_SNAPSHOT isolation level.

Database mirroring databases are not available for queries, but there is the possibility of creating a database snapshot on the subscriber and allowing users to query it. Database snapshots are a point-in-time view of the database and don't change their logical content after creation - a query will return the same values each time it is run. So, there is a caveat here. The database mirroring latency might be really low - less than a minute even - but the real reporting latency to the users of the subscriber relates to the time difference from the creation of the database snapshot to the current time. If snapshots are created once a day, the latency for reporting is therefore up to 24 hours.

Is automatic failover supported?

This only applies in the case of database mirroring. Neither replication nor log shipping offer this capability. In the case of database mirroring, the witness server provides quorum capabilities for the mirroring partnership and in the event that a principal server is lost, the witness brings the mirror server online.

Ease of implementation

Personally I find that log shipping is pretty easy to set up and administer. Replication and database mirroring are more complex and require a bit more preparation but neither of them I would class as particularly difficult.

Conclusions

Hopefully this article provides enough info to make a more informed choice.