Sql-server – Deciding on a replication strategy

log-shippingmirroringreplicationsql serversql-server-2008

We have several dozen small-medium (<1Gb) databases on our SQL Server 2008R2 (Standard Edition) server, with one or two more being created every month. The server itself is a fairly well spec'd box for the task; plenty of processor, memory, and disk space and throughput. Unfortunately, we don't have a similarly spec'd DBA to consult in-house 🙁

All the databases are currently in SIMPLE recovery mode, partly for convenience, mostly due to ignorance of how FULL mode works. The databases are being backed up every night (using BackupExec).

We're now looking at the best way to introduce some kind of simple redundancy in our SQL infrastructure; we have a companion server ready of similar spec to the primary server that can be quickly put to use if the primary server fails. Our requirements are fairly basic: we just need some mechanism that will take a copy of the database to another server in a regular basis. We don't need automatic failover, and we don't necessarily need the data to be replicated in real-time: once every 5-15 minutes would be fine, as long as it doesn't impact the performance of the primary server too heavily.

Ideally, the replicated database would be available for read-only queries for reporting et al.
I've looked and experimented with the following options:

  • Replication. I've had a horrible time with this: we've had one database replicated between servers for over a year, and although it works well most of the time, for no apparently reason the replication breaks every couple of months. I've spent hours (days?) trying to find the cause for this, but the logging is indecipherable and all advice seems to point to ultimate what I've had to do each time – blow away the replication and/or reinitialise. This is no fun on one database; having to do this semi-regularly for potentially dozens of databases is out of the question.

  • Mirroring. I've played around with this, and it seems to work well, but the mirrored copy database remains in recovery mode, and therefore isn't available for queries.

  • Log file shipping. I've not looked into this too deeply yet, but the principals seem easy enough. It seems if I set the restore operation to "Standby" I can have the secondary database available for read-only queries. However, I've seen notes that suggest that, in this mode, restores can be time-consuming, and connections may need to be closed during the restore, which may affect us if we start to rely on the read-only copy being available.

Not having much experience of any of the above – we've only really used Replication before, and as mentioned our experience hasn't been good – I'm hoping that someone can advise me on the best strategy to employ. Are there any other methods I should be considering, or any third-party software that might help? Am I being naive, and it's simply not possible to satisfy all our (hopefully modest) requirements?

Best Answer

We don't need automatic failover, and we don't necessarily need the data to be replicated in real-time: once every 5-15 minutes would be fine.

First thing you need to do is to change your database recovery model to FULL as logshipping is not supported in simple mode.

In your scenario, logshipping would be best choice IMHO without the need to upgrade to enterprise edition (which depends on your budget and other requirements).

Logshipping offers you flexibility of restoring log backups to minimum of 1 min which suits your requirement.

Just make sure that no one takes adhoc log backup as it will break the log chain leading to logshipping failure.

Alternatively, you can use your secondary server for reporting purpose of you set the option for restoring log backups as standby mode.

Read up on logshipping on books online.