mirroring vs. log-shipping

[DrSql]

Please share your thoughts about why you would choose mirroring over log-shipping in SQL Server 2008.
Quick responses are greatly apppreciated.
Thanks

Dr.Sql
Good Luck.

 

[Jamfool]

ok, quick stab

Mirroring is basically log shipping as close to real time as they can get.

Advantages:

1. You have a standby that can be used for immediate transparent fail over. (you would need to redirect clients to new server with log shipping or rename box) automatic fail over.

2. You can create a sql readonly sql snapshot against the passive node and query it.

Disadvantages:

1. overhead to mirror, versus transaction logs.
2. mirroring, mirrors bad data. e.g. user breaks something..this is replicated over
3.only committed transactions replicated in mirroring

 

[mrdenny]

Your disadvantages 2 and 3 aren't valid.

With log shipping bad data is transferred over as well. It just takes a few extra minutes to get there.

With log shipping only committed transactions are shipped as uncommitted transactions in the log backups would be rolled back as they would be included in the next log backup when the commit happens.

In either case you'll get the database in a consistent state. With mirroring it'll probably be more up to date than with log shipping.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Jamfool]

2. to clarify log shipping, can be implemented with a time delay gap or not applied at all. therefore it gives a useful window that you do not get with mirroring.

3. not a disadvantage true, should have been a difference.