For all those suggesting mirroring - it's not going to be possible to mirror 400 databases. You'll run out of worker threads way before you hit 400, whether on 32- or 64-bit. There are at least 2 worker threads per database on the principal and at least 3 per database on the mirror.
It's really got nothing to do with the skills of the DBA. It depends entirely on what the HA requirements of the business or application are - these are the driving factors, not what the DBA can cope with. If a more complex solution is required, then getting a DBA that an deal with it is also required. Limiting your HA solution because of the skills of the DBA is nonsensical.
Database mirroring is also not fast-detection and fast-failover - it entirely depends on what the failure is, what the mirroring partner timeout is, what the SEND and REDO queues are. It could be quite a while for a failover to complete one the mirror decides to do become the principal. I've helped many customers implement mirroring (both when at Microsoft when I owned database mirroring along with the rest of the Storage Engine), and since leaving in 2007.
Forget mirroring for that number of databases.
There are some fundamental questions you need to answer before we can give you a recommendation:
- Do you want to have a redundant copy of the entire databases or just portions?
- Do you want to be able to use the redundant copies? For writes or just for reads?
- What's the transaction log generation rate of the databases?
- What's the network bandwidth between the two sites?
Log shipping is probably the easiest for you, both in terms of setting it up, monitoring it, and being able to bring the redundant copies only quickly and within your data-loss limits. With log shipping you can choose to bring the redundant copies online without finishing restoring all the transaction log backups queued for restore, by accepting data-loss.
You can also effect simple failovers using a mid-tier routing technology, or even something as simple as Windows NLB with a 0/100 config, or switching to 100/0. I've also seen customers using DNS switching to effect failovers when the server name cannot change.
With replication you have to deal with unpredictable latency between the transactions being harvested from the publication db transaction log, hitting the distribution database and then being pushed/pulled to the subscription database by the distribution agent. Replication can also get itself twisted up and be a pain to figure out and reset - log shipping is dead simple.
Given that you've been using your own log shipping, I'm guessing that the tran log generation rate and network bandwidth aren't an issue - I'd stick with log shipping and shy away from replication. Don't even think about database mirroring as it won't work for your volume.
Hope this helps - this is really two day's worth of discussion when I teach HA to the DBAs inside Microsoft - boiled down to 5 mins answering this. Feel free to follow-up with more specific questions in comments.
Best Answer
Either log shipping or replication will work with DB mirroring - which one you should use depends on your requirements, but replication can be harder to set up and manage than log shipping so personally I would stick with log shipping unless there's a feature of replication that you really need. The links below give more information on how to set each one up.
Is it possible to have the failover database accept writes?
Note: I assume you mean once failover has occured, rather than during day-to-day normal operation.
Certainly with log shipping it is possible to have the failover database in the secondary DC accept writes. If you can take and apply a tail-log backup of the database running in the primary DC (to minimise data loss, and to keep the log chain intact) then you will have an up-to-date copy of the database running, however do not forget that you are running exposed in this situation. Regular log backups can help but if your goal is not to lose any transactions then this cannot be guaranteed when you are running solely on the log shipping secondary in the other DC. It may be better just to run the application in a read-only state until your HA is set up again. From this state you can copy log backups across to the primary datacentre, then reinitialise mirroring.
Useful links:
http://msdn.microsoft.com/en-us/library/ms187016.aspx - Log shipping and database mirroring http://msdn.microsoft.com/en-us/library/ms151799.aspx - Replication and database mirroring
Note: to get write access to a logshipping database, you need to RESTORE DATBASE dbname WITH RECOVERY Afterwards it can be written like the master, BUT you cannot restore any additional logs after you do this. You need to restore a new full backup to get logshipping working again. But it would at lest allow you to failover to it.