0

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.

Community
  • 1
René Kåbis
  • 107
  • 2
  • 3
  • 9
  • Hmmm… I just found [Distributed File System Replication](http://msdn.microsoft.com/en-us/library/bb540025%28v=vs.85%29.aspx). 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 Answers2

2

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.

phoebus
  • 8,370
  • 1
  • 31
  • 29
  • 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](http://blogs.msdn.com/b/srinivas-v-v/archive/2013/06/25/setting-up-always-on-availability-group-on-sql-server-2012.aspx), 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
  • @TomTom I will have to run with DFS on my file servers, and use AOAG with my SQL server. I simply cannot add a third machine for any quorum/witness/CSV capabilities at this time. Maybe in the future, but not now. – René Kåbis Oct 29 '13 at 20:26
  • @RenéKåbis I think you are completly missing the point of Hyper-V. If you have windows 2012 standard licenses, you can create two virtual machines per license. Two hosts = two windows 2012 standard licenses = two hosts running Hyper-V = two virtual machines per host. With that, why can't you spin up a 3rd virtual machine for quroum? – Rex Oct 29 '13 at 22:00
  • @TomTom Because I would need a third physical machine for quorum. The whole point about Quorum is that if one VM goes down, there are a majority of “votes” left to maintain quorum and cause a successful failover from one node to another. However, you can have more than a VM fail -- you can have the Host fail as well. If there are only two Hosts, and one contains both the VM and the Quorum, and it goes down (say, a hardware fault) -- all nodes shut down. And that kind of defeats the purpose of having a Quorum in the first place. – René Kåbis Oct 29 '13 at 22:13
  • @RenéKåbis Just FYI in order to do AOAG you will still need a Windows cluster, meaning you'll need either an odd number of nodes, or a file share or shared disk external to the nodes to use as a quorum witness. You seem to have a decent grasp of the concept of a quorum, so you must realize that with only two machines and no external storage (e.g. SAN or NAS) you can never truly have guaranteed automatic failover as there will only be two "real" votes. – phoebus Oct 29 '13 at 22:21
  • @TomTom You seem to have a better grasp than I. Can a simple NAS like the Western Digital MyBook Live (3TB) be sufficient? Not sure how to configure NAS to use Active Directory in its permissions, but hey. – René Kåbis Oct 29 '13 at 22:35
  • No idea. See, I do not use "a simple NAS like the Western Digital MyBook". The smallest standard system I have here has 24 hard discs slots and I in generally stay away from non-professional equipment. Not worth the hassle. – TomTom Oct 30 '13 at 06:24
2

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.

joeqwerty
  • 108,377
  • 6
  • 80
  • 171
  • 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