Deciding on a replication strategy

Question

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?

Answer 1

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.

Answer 2

You're right about each of your options. Log Shipping is perhaps the easiest way to almost accomplish what you want, but there is the drawback that you won't be able to read from your secondaries while the logs are being restored. If your goal is simply to become more highly available in case something bad happens, I advise you to at least get your backup strategy up to speed ASAP. That means putting your databases in FULL recovery mode and performing transaction log backups at a high frequency, and putting those backups on a different disk than your database and transaction log.

One thing you didn't mention was creating a simple shared-disk cluster. Again, it depends on what type of redundancy you want. If you're just trying to ensure against a server hardware failure, this might be the way to go.

The technical solution to your problem that gives you the highest availability and flexibility is to upgrade to SQL Server 2014 Enterprise Edition, which has support for AlwaysOn Availability Groups. AlwaysOn is a technology almost identical to mirroring, but allows for a readable secondary (to be sure, there are other benefits).

That upgrade can be costly, however, since it requires Enterprise Edition. It's also pretty tricky to configure the first time, and you'll need to make sure you have a good relationship between your DBA-ish people and network administration team to pull it off. It may even be worth hiring a consultant for this setup alone if you really don't have the DBA resources. Just make sure the consultant has experience setting up this type of cluster. It IS something you can pull of yourself if you know enough about networking, security, clustering, and SQL Server, though. All the resources are out there, you'll just have to do some digging. Brent Ozar has some good resources you can check out, but you'll probably want to look into a book purchase.

Another nice thing about AlwaysOn AG's is that you can set up an Azure instance as your secondary without making another hardware purchase. This can be a really smart for DR scenarios where your main data center goes out -- you'll have Azure's completely separate infrastructure to fall back on.

Sorry, I think I didn't answer your question too well, I think I just gave you a lot more to think about. That's how this goes, I guess...