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.