How do I set up active-passive database clustering between data centers?

Question

I am looking to setup an active/passive database cluster between data centers using mirroring and/or log-shipping across these two nodes. My challenge is understanding High Availability (HA) and getting the HA configuration correct.

Scenarios:

1. Hardware Failure. I have no issues with this and understand that all traffic will be diverted to another data center or another node with in the same data center.

2. Data Center 1 Failure. Again, I have no issues here. All the traffic will be diverted to another data center.

3. Database Updates. I am facing an issue here.

During a patch upgrade on the Data Center 1 database, I want to divert all traffic to the Data Center 2 database. After patching is complete, I want to revert it back to Data Center 1. This may require breaking the mirroring/log-shipping configuration. How would I then copy all the changes made on the Data Center 2 database back to Data Center 1? Is there any feature I can use to get this incremental data while minimizing downtime?

Answer 1

So you've got a couple of options when it comes to doing this.

Option 1: Use a geographically dispersed cluster which is a single cluster deployed in both data centers. This gives you one single cluster which exists in both sites. You would end up having several nodes in each site so that when doing local site patching you don't have to fail over to the second site. In the event of a site failure you would then fail the cluster over to the second site. To change to the clients would be needed as they would just connect to the same virtual name which would then resolve to a new IP address.

Option 2: Database Mirroring between sites. This is the preferred option as it's easier to setup, doesn't require expensive SAN replication software, and failover and back is all handled via SQL Server. I'd recommend a two node cluster in each site for local HA, then mirroring between the sites for your DR setup. Local HA covers server patching which the database mirroring covers you for DR. If the sites are far from each other setup the system with asynchronous mirroring, if they are close to each other the you can use synchronous mirroring which will allow for no data loss.