Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been using Microsoft’s Hyper-V technology for a little while now, but I am just now dipping my toe into clustering.

In particular, I am trying to implement a fault-tolerant SQL DB. This involves setting up two VMs, clustering them via Failover Cluster, and then installing SQL Server in some fashion. I have two physical machines - one high-end and rather beefy “heavy lifter” to contain the majority of the VMs, and another “backup” (a repurposed desktop) to hold the essential “secondary” (or failover) AD-DC, SQL and FS VMs.

The main reason why I find the failover cluster at the VM level so attractive is that it presents a single IP and DNS entry to the network as a whole - if one machine (physical or virtual) goes down, you might loose some ping and the connections get reset, but the network applications (Microsoft RMS connection to backend SQL) can still connect to a viable DB without having to mess around with the settings at all.

My first question is in terms of SQL Server itself. If I have a cluster between two VMs, does it make more sense to install the SQL Server in Failover Cluster configuration or should I simply install it in a stand-alone config and mirror the DBs? For example, this post suggests just mirroring the DBs, but do I just mirror standalone DBs on standalone VMs, or can I get the network and failover benefits of clustered VMs while still utilizing (on each clustered VM) standalone DBs that have been mirrored between each other?

As well, I have come across a lot of documentation about SQL clustering, but most assume a number (#>2) of physical machines to hold not only the actual SQL VMs but also the Quorum and Witness stores. I will not be able to muster more than two physical machines. As such, I will have to be satisfied with a VM cluster that does not exceed two VMs (one for each physical machine).

Another issue involves MSDTC - the Distributed Transaction Coordinator. When attempting to install the SQL Failover Cluster (I never completed it for this reason) it threw a hissy fit because MSDTC had not been clustered. Search as I might, I have not yet found a way to do so under Windows Server 2012 R2. I have found plenty of docs for Windows 2008 and 2008 R2, but these instructions don’t align with 2012 R2 (at least, not in a way that allows me to successfully cluster MSDTC). Plus, some of the instructions that I have found for SQL Server Failover Cluster installation suggest that a third “network device” - shared network storage (a SAN) - is required for the DB itself (and other functionality). I do not have this, and won’t be getting this. Most of my storage exists on the “heavy lifter” that was designed for all of the “primary” VMs. If that physical machine goes down, so does the storage. The secondary server does have enough resources for an AD-DC Server, an SQL server and a File Server, so it will handle the “secondary” failover versions of those VMs (clustered or not).

My final question involves file servers. If I cluster file servers between two VMs (one on my “heavy lifter” and another on my “backup”, how do I mirror the data between them? Clustering VMs only provides a single point of access on the network for a resource, it doesn’t exactly replicate data between the two - that is left to the services that serve up that data. I am unsure how I can ensure that file server data between two clustered file server VMs can be properly mirrored. Remember, I only have two devices to be used here - my primary machine and a backup secondary. There is no chance of me obtaining a SAN or any other type of network attached storage. What exists on the machines must act as the storage.

Thanks in advance for any suggestions.

share|improve this question
    
Hmmm… I just found Distributed File System Replication‌​. I’ve heard about it before, but totally forgot about it until I stumbled across it. Thoughts? Would this solve my last question? –  René Kåbis Oct 29 '13 at 17:03

2 Answers 2

up vote 2 down vote accepted

You may want to break this up into a series of more-specifically-targeted questions.

  1. If you are looking for seamless, app-transparent failover then you need to use either Failover Clustering or AlwaysOn Availability Groups. AOAG will only be an option if you are using SQL 2012 Enterprise. Mirroring will provide you with simple (manual) failover; if you want automatic failover you'll have to add a witness server to the mix, but even then your application will have to be failover-aware because it will need to use a new IP/name to connect to the secondary server, unlike with a failover cluster/AOAG. In fact, Microsoft intends Availability Groups to replace traditional mirroring going forward, so for the sake of future-proofing I recommend not using mirroring for new deployments if possible.

  2. SQL Server 2012 on Windows 2012 does not absolutely require a clustered MSDTC, but if you need one it is as simple as adding the DTC clustered role: Add clustered DTC

  3. For a failover cluster you need some type of external storage for the DB. This can be SAN, DAS, or (if you have SQL 2012) NAS. If you are not going to use external storage, you should use AlwaysOn Availability Groups as it will replicate your data for you while providing cluster-like failover capabilities. This still requires a Windows cluster but SQL itself will not be clustered.

  4. Clustered file servers are designed to work with shared storage at the back end, much like clustered SQL instances. Another option would be to use DFS Replication with DFS Namespaces to provide a highly-available file share without clustering.

share|improve this answer
    
Re 4 - that does mostly not work s DFS does not replacte changes for open files. If you need this, you prett much need a real time replicating high available ISCSI target.... then you can run a CSV on that. Starwind has an offer for that - among other free alternatives. stock windows has no means of high avaialble storage outside a standard file server role, ill suited for virtual machines, databases etc. (which keepo files open for a long time). –  TomTom Oct 29 '13 at 19:12
    
@phoebus thank you very much for that explanation. I will have to go through it piece by piece. With that said, I am limited by not having any external storage. Only storage internal to the host VM. I can create “external” drives for the VMs, but these would be additional virtual hard drives without an OS. There is also no budget for purchasing additional external storage. –  René Kåbis Oct 29 '13 at 19:20
    
@phoebus - so what you are saying is, if I only have two physical machines to put the two virtual SQL VMs onto, and if I do not have any NAS or SAN to add, I need to use ALwaysOn Availability Groups in order for the SQL databases to synchronize between each other. If correct, this is what I am looking for; but by the looks of the screenshots in the link provided in #3, the setup procedure for AOAG for 2012 R2 is considerably different than for 2012. I’m gonna need help. –  René Kåbis Oct 29 '13 at 19:35
    
@TomTom - so what you are saying is that if I am looking for a truly redundant File Server where one node out of two can go down, and the files remain available, I will need a third machine to host a Cluster Shared Volume. But if I am not concerned about open files, a standard File Server VM on each physical machine with DFS Replication set up should work? –  René Kåbis Oct 29 '13 at 19:45
    
Yes. Basically DFS replicates files when they close - standard approach for file based replication. Good for regular file servers, bad for running databases, vm's of that. We use DFS like that (regular files) and it works perfectly. –  TomTom Oct 29 '13 at 19:51

One "low tech" option would be to implement Hyper-V Replica between the two hosts. If you don't have shared storage for them and you're unable to configure (or finance) shared storage and application level failover (SQL Server Enterprise Edition for AOAG) then Hyper-V Replica can be used to replicate a VM from a source host to a destination host and keep the two VM's in sync. You can enable application consistent replication by using the VSS option of Hyper-V Replica.

The downsides to Hyper-V replica is that it can't be used between hosts in the same failover cluster and there is no automatic failover.

share|improve this answer
    
If they can both share the same IP address (or share the same hostname within DNS), then it is a workable solution for me. Otherwise, a failure in one will mean that users would have to manually switch over to the other. And this is not what I want to see happen. –  René Kåbis Oct 29 '13 at 20:28
    
A Hyper-V Replica VM is an identical duplicate of the source VM, so clients will not have to reconfigure their connections to the resource/VM. The downside is that there's no automatic failover. If the source host fails then the Replica VM has to be manually started on the destination host. –  joeqwerty Oct 29 '13 at 20:35
    
So essentially you have the same VM, with the same IP address, just in a shut-down form on the “replica machine”. If the primary machine fails, service can be re-established by manually starting the shut-down VM on the replica machine. I was hoping for a more automatic failover, but okay. This can serve as an alternative solution if all else fails. –  René Kåbis Oct 29 '13 at 21:23
    
And with 2012 R2 I tink sync interval can be down to 30 seconds... –  TomTom Oct 29 '13 at 21:24
    
@RenéKåbis - That's it in a nutshell. –  joeqwerty Oct 29 '13 at 21:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.