I currently have a pair of database servers up and running separately, but with the same structure and (hopefully) the same data on both of them. The applications that utilize the data from these servers have extra behavior set up in them such that every statement is run against both servers; this has (so far as we know) ensured that the data on the two servers continues to match up correctly, with some minor issues here and there when a configuration is changed incorrectly.
Currently, we have the two servers specifically for the case of hardware failure, and for disaster recovery; working out of Florida, we pack up half of our department's hardware whenever a hurricane starts to look like it's headed our way. Unfortunately, the half that leaves the state needs to continue functioning, and is not reconnected via VPN (we're trying to get that to happen).
So with that all in mind, is there any way with SQL Server (current release or otherwise) that we can set up a cluster that allows half of the nodes to just vanish and begin acting as a separate cluster? And more importantly, is there any known sane way for the two clusters to resolve any inconsistencies once they're both back on the same network? With certain tables exempted from this and requiring manual intervention, our current strategy is to truncate everything on the server that left, trusting the on-site server to hold the accurate data.
Best Answer
It sounds (from the extra comments) more like you want a way to keep two databases in sync after one is completely cut-off for a small or medium amount of time.
I'm not fully sure on how your DR scenario is going to work, and honestly at this point I'd suggest looking into the following:
Setup a cloud hosted server through something such as windows azure, Amazon AWS, etc. Then choose what fits best for you.
Move your infrastructure to the cloud - you won't have the issue of moving servers around, DR for hurricanes (I also live in FL so I get it), etc. Pick different regions (if applicable) to make sure you're covered. This is not as easy as it sounds but may pay-off for things such as this.
1.1. Have a dedicated DR site at co-lo facilities such as Rackspace, sungard, etc.
Availability Groups - This would require a lot more infrastructure than I believe you are willing to invest due to the nature of AD integration with AGs and Clustering. While Windows Server 2012R2 gives dynamic quorum and ad-detached failover clustering, I wouldn't advise using it in this situation.
Mirroring - Since AD, etc, would be an issue, it would be possible to use a local account on the hosted platform and setup the endpoints to use certificates. It also doesn't have to be part of your domain. You may have some latency issues that would need to be worked out or potentially not be acceptable. We don't know your SLAs.
Replication - There are so many ins and outs to replication, but a simple transactional setup could work for you, assuming your database meets the requirements. There are many gotchas just like the other technologies.
Log Shipping - A very simple DR method that may work for you close enough to when the server may possibly go down. This could be completed without being domain joined, but there would be a few extra hoops to jump through.
This is not, by far, an exhaustive list but more a guide to give you something to look at and think about. You'll need to meet with your business owners and decide what is and is not acceptable. Maybe they don't need seamless integration, maybe they just need best effort. We don't know, but this is a start.