Why I prefer Log shipping over Mirroring

• I have more than one standby server (a local standby server within the same data centre and one in another country). Mirroring only allows for one standby server.
• I can re-use the standby server for reporting by setting the databases to restore with STANDBY. (You have to delay restoring the logs while the standby is accessed for reports otherwise the restore will fail). One way to do this is to allow reports to run during working hours say 09:00 – 17:00 and schedule the restore jobs to run after out of hours (17:00 – 09:00).
• In my case (15-30 minutes) downtime is acceptable failover time. If you are looking for less downtime, then you need to look at a combination Mirroring, Clustering and Log shipping.
• I don’t have reliable bandwidth to our DR site. This means when the link is down, the log files in the principle server will grow until the link is restored which is an issue as my databases generate huge transaction logs. In my case, all my transaction log backups are compressed as I use a 3rd party tool. I can even move them to another server if they get too many until the link to the DR site is restored.
• I have around 30 databases. If you have more than 10 databases, you should consider what impact Mirroring will have on performance as each database requires two threads. This is not the case with log shipping.
• I can decide when to apply the logs onto the standby server for instance I apply the logs after 20 minutes to catch any accidental/ malicious change to propagate to the standby). In the mirroring scenario, the change is instantly reflected in the mirrored database.
• I like keeping my databases in Bulk-Recovery mode to reduce the amount of data that goes into the transaction log for the minimally-logged operations. If you are looking at mirroring, you databases must be in Full recovery mode.
• I am limited to Synchronous mirroring as I don’t have the Enterprise Edition of Sql server. This is an issue for me as performance matters in my case (mainly due to the latency between the two sites).
• Also, to populate my data warehouse I extract from the standby server (so my ETL has virtually no impact on my production databases!). During the ETL process I don’t restore logs for 1 hour.
• Our application is legacy asp code that cannot automatically re-connect to the mirror in the case of failure (.net code can automatically re-connect in the case of mirroring failover). We still have to do manual changes for the failover to happen.