Replicating 3000+ SQL Server databases 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.
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.)