Mirroring vs. Log Shipping in Sql Server 2005

Question

I'm interested in hearing people's thoughts about the pros and cons of database mirroring vs. log shipping in this scenario: we need to setup a database backup situation wherein there is exactly one secondary server that need not automatically pick up when the primary fails. Recovering and starting with the secondary should not have to take too long though.

Answer 1

Mirroring

• Database mirroring is limited to only two servers.
• Mirroring with a Witness Server allows for High Availability and automatic fail over.
• You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
• While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
• Mirroring with SQL Server 2005 standard edition is not good for load balancing (see sentence above)

Log Shipping

• You can log ship to multiple servers.
• Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
• You can leave the database in read only mode while it is being updated. Good for reporting servers.
• Good for disaster recovery

Answer 2

For backup purposes I would recommend Mirroring: it keeps an always up-to-date copy of your database with no hassle.. If you don't need automatic fail-over you need just two servers/instances. Note that High Performance mode is only available in the Enterprice (sp) edition!