Best way to replicate / mirror 100s of databases in SQL 2005

Question

I currently host around 400-500 SQL 2005 databases of varying sizes (1-10 gig) each. I am aware of most of the different methods available and the general pros/cons of mirroring, log shipping, replication and clustering, but i am not aware of how well they tend to perform when its employed at the size i have specified (400-500 unique databases).

Does anyone have any good advice on what is likely the best method for having the ability to fail over to another server with this sort of setup?

Fail over does not need to be immediate, i'm just looking for something better than taking backups every day and moving them to storage. I'm preferably looking for something that would also makes it easy to manage the databases in bulk (as opposed to one at a time).

Thanks for your input!

Answer 1

You should talk with your storage vendor and see if they provide hardware based replication. It will be far faster than any other solution that you might come up with.

I am running 3 fairly big databases and replicate them over SAN both for backup (backup is done on the replica), dbcc checks and hot standby purposes. It was a nightmare to set up at the beginning but once set up works remarkably well.

I have 3 8-12 TB databases each - hosted on EMC SAN each running on a different server. We first replicate it over the same SAN, mount on a different host connected to SAN and use the replicated copy to do offline backups and DBCC checks, and batch reporting. We use replication manager for this.

We also do SAN to SAN copy every 3 days for a second line backup.

It was a nightmare to set up - took us a fair amount of time. We were bounced from EMC to Dell and back. After yelling at them, they finally sent someone who actually knew what he was doing. I wanted to go with Netapp as I have a fair bit of experience with it, but was overruled by bosses. Apparently Dell offered them a better leasing deal.

To be fair it has served us remarkably well in last 2 years.

HTH

Answer 2

Guess your target solution will revolve around the question of what is it that you are really after?

If you are after HA then in my opinion you might wish to consider Clustering taking into account the number of databases on a single instance(I am assuming this is the case).

If you are after DR capability and worried about your storage infrastructure letting you down then we are really talking about a second site(logical or physical) and options available around that scenario. Mirroring in this case would be out of the window for so many databases. Logshipping is an option but manageability will be an issue as rightly pointed out by Farseeker. Also, Replication falters with similar concerns.

I guess that leaves us out with possibly hardware based replication as suggested by both Farseeker and no_one above. This can be combined with Windows2008 geo-clustering capability to provide you with excellent HA and DR capabilities. Although in these rough economic times it might be a dearer solution.

Another radical option might be to invest in something like HP Polyserve or Xkoto's Gridscale.

Hope this helps.

Answer 3

I've used DoubleTake for the last year or so. This has allowed us to replicate 70 to 90 (count growing monthly) databases to a DR fairly quickly and with a high compression. As Farseeker stated it allows a block level replication which works quite well. It also allows for a queue for those times where there is a heavy transactional load.

The only issue I really see for your instance is how heavy are these databases? I have at least 5 databases over the 10gb mark with a high volume of transactional data during peak hours and jobs, around 1 to 3gb per day per database. In all transferring upwards of 800+gb over the wire monthly (compressed is MUCH less of course). Since adding an additional 20+ databases in the past 15 months I've seen excessively high IO related to disk queueing. So it may be an issue trying to use something like this if most of your databases are very busy. You may end up with missing data or the receiving end if you have to much queued up and your link goes down. Of course this would only matter for a DR deployment. But keeping a close eye on it and tuning the bandwidth will help greatly with keeping the queue down.

Answer 4

We use Log Shipping to a standby server, and because it works off the log files, impact on the server (apart from the initial copy) is negligible.

We run ours every 15 minutes (We have about 75 databases), however I suspect that on 500 databases running it every 15 minutes would mean that by the time the last one is finished, the first one would be overdue, so you might need to spread yours out a bit.

You can script the log shipping configuration, however it does need to be configured on two sides (Original and Destination). I suspect it could be quite a handful.

The other option is block-level backup as no_one has suggested. Doubletake have a product that will do block-level replication of your SQL server (I believe it is a very expensive option for an already expensive product) - you just point it to your data and log folders and it will take care of the rest.

We used DoubleTake for other non-SQL databases (BTrieve) and it worked very well, and the latency on replication was only 20 seconds or so.