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.

Othaothe

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

Pearce

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!

Saidee

Answer 3

Switching to the secondary database does take longer with log shipping, but it's not too bad. You'll have to manually copy any uncopied backup files, apply the transaction log backups to the secondary database, recover the secondary database, and change its role to primary. If the old primary databases accessible, you should back up its transaction log before beginning. Failing over with mirroring is somewhat simpler, and can be done automatically if you are using High Availability mode. Even when using High Performance mode, it's still a one statement operation.

Dillydally