2

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.

Improve this question
5
  • Could you elaborate a bit more. What you're describing isn't clustering as that would use the same database files (there would be no duplication of databases or data). Are you asking about cluster or a way to keep SQL Server databases in sync?
    – Sean Gallardy
    Commented Aug 4, 2014 at 16:15
  • A cluster has to use the same storage for all nodes? Ah, well that complicates things. I suppose I'm asking for a way to keep them in sync then.
    – user44956
    Commented Aug 4, 2014 at 16:16
  • Taking a look at some different articles, I suppose it would be more correct to assume we need something along the lines of database mirroring, or "AlwaysOn Availability Groups" if we upgrade to a recent release.
    – user44956
    Commented Aug 4, 2014 at 16:31
  • Gotcha - ok this makes more sense now. So Mirroring and AGs can continue to work assuming they are setup to do so. AGs require windows clustering as a pre-req and if using windows server 2012R2 can make use of dynamic quorum which is what you are looking for I believe. The question would remain though, do you have AD connectivity, etc, from the out-of-state provider?
    – Sean Gallardy
    Commented Aug 4, 2014 at 19:05
  • We don't currently, no; the out-of-state setup is an effective island with no external connections at all. I'm currently setting up three VMs with one as an AD controller so I can set up the windows clustering and try an AG. Would it be possible/advisable to set up both of the physical servers (after I've done this a few times in VMs) as AD controllers?
    – user44956
    Commented Aug 4, 2014 at 19:22

1 Answer 1

Reset to default
2

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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

Improve this answer
3
  • Thanks for the info, Sean. The first option (and cloud-hosting in general) isn't something I can pursue right now due to the completely isolated network at our DR site. With the second option, I'm pretty happy with the AG setup I put together in VMs yesterday, but you're right about the extra infrastructure; maybe I could swing a VM long-term, but I doubt I can get us another machine to play AD controller. If we can convince the uppers to give the DR site a connection, this should all get much easier; in the meantime I'll research the other options you mentioned.
    – user44956
    Commented Aug 5, 2014 at 17:04
  • I'm saying, get rid of the DR site and use a cloud based hosting provider as your DR. This is especially true if you're only going to use it every once and a while (like for on-coming natural items) which let's you pay by usage. Turn it on a little before hand and get things in sync - let it run until disaster passes and only pay for what you used. It might actually be more cost effective.
    – Sean Gallardy
    Commented Aug 5, 2014 at 18:21
  • Unfortunately, the DR site is an absolute requirement for other business hardware. If (again) the DR site had any external network connection at all, I'd gladly push for a cloud-based system -- it's what our main IT dept. already does. As it stands, the hardware my team works with is useless without our DB, and the majority of the company's sales cease without that hardware. Annoying requirements to work around, but it's what I'm stuck with.
    – user44956
    Commented Aug 5, 2014 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.