3

I'm currently investigating the different options there are setting up a DR site for our main data centre. In an ideal world, we would have fewer, larger databases, however due to architectural constraints with the product, we have about 3000+ small (50MB to 300MB) databases.

I’ve had some experience with replication and I know that I can script setting up replication on each database, but my main concern is the performance overhead and the maintainability of this as a solution.

Before I go ahead and spend a lot of time testing this, has anyone else had any experience / advice on replication of multiple databases on this scale?


Thanks everyone for your comments and feedback. This is the first question I have asked on this forum so I’m having to edit my original question to respond to your comments and expand on the question…

In the main data centre we have a SQL Cluster with SQL2008R2 Standard connected to a SAN.

Regarding our RPO and RTO, the product is not a critical service, we would need to recover to a point in the last 24 hours and the recovery time needs to be within 24 hours of failure at the main data centre for all databases.

There are about 5 key databases which are used by all the other databases, however all other databases are independent of each other.

All databases have their recovery model is set to “Simple”, where full backups are made weekly and differential backups are made daily. Based off our RPO and RTO, log shipping does definitely sound like a viable solution.

  • What does your hardware platform look like? In particular do you use SANs or similar having features that might support replication at a file-system or block level? That could be a much easier way to go. – Colin 't Hart Jun 2 '14 at 11:04
  • 1
    What is your RTO and RPO ? That will determine your DR strategy. Also, IMO replication is not ideally suited as DR (though you might argue it), instead it is widely used for high availability of your data. Tell us your version and edition of SQL server and I will be happy to cater you in the right direction. – Kin Shah Jun 2 '14 at 11:20
  • Are all the databases independent of each another, or are there groups of databases that need to be recoverable/available together? Are there dependencies between any of the databases? – Jon Seigel Jun 2 '14 at 14:13
  • 1
    Do the 3,000 copies really need to be up to date and inter-dependent? Assuming that they don't you could consider log shipping (I mean, you're taking full and log backups of all databases already, right?). – Aaron Bertrand Jun 2 '14 at 14:15
  • 2
    I've heard of someone pulling off replication with ~400 subscribers, using a fan-out tree of distribution DBs. Batman personally supervised the project and Superman did the actual work. Never heard of 3k replicated DBs, with 3k reader agents etc. I would seriously consider log shipping first, specially considering that is for DR purposes. – Remus Rusanu Jun 3 '14 at 12:26
1

Yes, I've got clients with 1k-10k databases per server. (Fog Creek is actually one of them, and I can mention it here because I know Joel's talked about it on the podcast.) Usually you run into problems at this scale, and you start thinking about moving down to VMs instead - it's easier to manage more virtual SQL Servers with less databases per instance.

Log shipping technically works, but you have to roll your own solution rather than use the built-in tools. The problem is that these types of businesses usually have new databases coming online all day long, at any time of day, and they need these new databases to be automatically protected by the log shipping. This means as soon as a database is created, you need a full backup taken, plus get it added into the log shipping setup. You'll be rolling your own scripts, and you can't just have one job. One job won't be able to keep up with the sheer number of databases if it backs up one database at a time - you end up with multiple jobs, each of which handles a range of databases or does something like the modulus of the database id.

SAN replication is much easier because it automatically protects any new databases that get added in. For critical databases, I recommend using a combination of VSS-integrated SAN snapshots, dirty (non-VSS) SAN snapshots, and regular SQL Server transaction log backups, but you mentioned you've got SIMPLE recovery mode and a 24-hour RPO/RTO. You'll be able to stick with a much simpler solution: dirty SAN snapshots plus conventional full backups.

The specifics will depend on your SAN vendor's replication options, but typically they can take a dirty snapshot without quiescing SQL Server's writes, then replicate that snapshot over to your DR site. Done correctly, this happens without impacting SQL Server performance. The problem is that without quiescing SQL Server's writes, you're rolling the dice on whether or not each database will come online. Odds are, when you fail over, you're going to have a handful of databases that don't recover correctly - no problem, that's where your full backups come in.

Every night, you should be doing full database backups to another location (not the SAN). That other location should also be replicated to your other site.

This way, when you fail over, most of your databases will come online instantly via the SAN-replicated copy. Some of them will not (usually a fairly small percentage). For those few databases, you restore the most recent full backup. End result: pretty quick recovery time objective at a relatively low complexity cost.

I would NOT recommend this solution if you need every database to be at the exact same moment in time, or if you needed up-to-the-second recoverability. (Just making it clear because some reader is going to freak out at this design - it's certainly not for everybody, just for this one specific business case.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.