Sql-server – SQL mirroring or log-shipping – which has the least impact on a replicated database

mirroringreplicationsql server

We're planning a server which is a clustered SQL2005 Enterprise 64bit with a database of which a subset of tables are replicated one way via transactional replication to single subscriber located at different data center for reporting purposes. The distributor is co-located with the publisher on the cluster. A SAN is used for storage.

As well as clustering, the client want resilience for the publisher database in the event of a SAN failure. They have a second lesser SAN (from a performance perspective) available in (unfortunately for a DR perspective) the same data center. This will have a single 32 bit server with SQL2005 32bit Enterprise attached. The client are aware that in the event of a SAN incident they'll no longer have clustering or replication and a lesser level of performance.

I'm debating whether to use log-shipping or database mirroring to provide the database DR. We're using Quest LiteSpeed for backups and could use it for shipping compressed transaction log backups.

Do either of the two technologies (mirroring or log-shipping) have a lesser impact on the publisher database from performance and replication latency perspectives?

Best Answer

It depends. ha ha.

You also need to think about what data-loss requirements the customer wants for the publisher, and whether you're already taking log backups (I'm guessing you are).

Database mirroring can be set to have zero data-loss (as long as the mirror stays synchronized), but depending on the transaction log generation rate and the network bandwidth available, waiting for the log records to be hardened on the mirror before the transactions can commit on the principal may slow the workload down. Depends on what kind of transactions you're doing (long or short) whether this will have a noticeable effect on the overall response time.

With log shipping, it's just backup-copy-restore, repeat. So if you're already taking log backups, you're not going to be impacting performance at all. If you're not used to taking log backups, you may run into issues with transaction log size management.

Be aware that mirroring requires the FULL recovery model, so it could impact your database maintenance, especially if you're used to using the BULK_LOGGED recovery model. Depending on the network bandwidth available, this could also lead to log size management issues.

Both require network bandwidth, but in different ways. Log shipping is a burst every time a log backup is copied, database mirroring is more sustained, obviously depending on the log generation rate again. I'd need ot know a lot more to be able to tell whether the amount of extra bandwidth required for either would impact the movement of data in the replication stream and so affect latency there.

With log shipping, you'd have to manually failover to the log shipping secondary in the event of a failure, and there's the potential for data loss (all data since last log backup that was copied from the primary). And then you'd need to kick-start repl again.

With database mirroring, you can set it to failover automatically and you can specifically set the failover partner in the repl agent jobs to startup automatically on the new principal (which is also the new publisher). The trickiness is making sure that the datbase mirroring failover doesn't occur before the local cluster failover gets a chance to happen. You can do this by changing the mirroring partner timeout value. I blogged about this at http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-3-Database-mirroring-failover-types-and-partner-timeouts.aspx.

I wrote a whitepaper for Microsoft that describes how to use mirroring and tranasactional replication together: see http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server2008-New-whitepaper-on-combining-transactional-replication-and-database-mirroring.aspx.

All other things being equal, I'd recommend database mirroring because of the ease of management with potential for less dataloss. You may have some other requirements I'm not aware of that would prevent that though.

Hope this helps.