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?