Website & database hosted across multiple data centers

Question

We recently had a major data center outage from our hosting provider. They lost a fibre connection, causing a 9 hours outage, where most of our customers could not connect.

This has caused us to consider housing our website across 2 independent datacenters.

The website is an asp.net website with Sql 2008 database. I've read a few articles around round-robin IP, anycast IP, etc.

This is new terrority for me, so am fairly lost where to start.

I have a some questions:

1. How can we host the database in 2 data centers and still keep them in sync
2. Would 1 data center have to act as the primary?
3. I'd like all users to go to datacenter1 but in the case that 1 is unavailable I'd like traffic to move to data center 2. How can this be acheived?

These are just some questions to start off. If someone could give me an overview on how to tackle this or point me to some resource that will help, will be very greatly appreciated.

Answer 1

This is very difficult to achieve. Seriously.

SQL Server doesn't play that well with high latency links (high latency in this case is >=1Ms) for mirroring and clustering, which are the only two methods avaliable for guaranteeing up-to-date data. You need to switch to replication or log shipping if you have any latency. Otherwise your database will suffer damatically in terms of read and write speed.

In terms of anycasting, this requires a very robus DNS network. There are DNS providers who can do this for you.

For DNS Round Robin/Failover, this is generally accepted as a very poor method of doing failover, however it can be "good enough" if your SLA with your customers can accomodate it. If you have a low TTL (say, 5-30 minutes), then you can go in and flip your DNS records to point to your 2nd datacentre and then most of your clients will be back online in < 30 mins (although there are a lot of broken DNS cachers out there, so your mileage may vary).

Another option is to use some form of high avalibility built into a SAN and hypervisor. VMWare's SRM comes to mind. If your SAN can do block-level replication of iSCSI LUNs and you have an appropriately licensed VMWare cluster, VMWare can then boot up your disaster site when it detects the primary site has gone offline. With vSphere 5 making huge improvements in the number of vCPUs and vRAM allocations, this can now be feasable for even large SQL servers. However it requires a huge investment in terms of $ and infrastructure.