Home / server / Questions / 126574
Accepted
Chris W
Asked: 2010-03-27 03:44:46 +0800 CST

SQL Cluster install on Hyper V options

  • 772

I've been reading up on running a SQL Cluster in a Hyper V environment and there seems to be a couple of options:

  1. Install guest cluster on 2 VMs that are themselves part of a fail over cluster.

  2. Install SQL cluster on 2 VMs but the VMs themselves are not part of an underlying cluster.

With option 1, it's little more complex as there's effectively two clusters in play but this adds some flexibility in the sense that I'm free to migrate the VMs between and physical blades in their cluster for physical maintenance without affecting the status of the SQL guest cluster that's running within them.

With option 2, the set-up is a bit simpler as there's only 1 cluster in the mix but my VMs are anchored to the physical blades that they're set-up on (I'll ignore the fact I could manually move the VHDs for the purposes of this question).

Are there any other factors that I should consider here when deciding which option to go for?

I'm free to test out both options and probably will do but if any one has working experience of these set-ups and can offer some input that would be great.

Edit:

Good points raised about adding mirroring to the mix to add a second copy of the database. I'm considering whether to just go with 2 SQL instances and use mirroring alone since this backend will be used for a single application since it will be a fairly stable set-up with regards to users etc.

The main point of this question though was specifically to do with the cluster set-up. i.e. is it better to

a) On the Hyper V hosts, build a failover cluster of the VMs and then, within the VMs themselves set up a second cluster and install the SQL Cluster - guest cluster on top of the host cluster

or

b) Just set-up the SQL cluster within the VMs and don't have the underlying failover cluster setup on the host Hyper V machines themselves.

I've seen advocates of both options but I don't really understand the pros and cons of each approach.

sql-server-2008 hyper-v cluster

2 Answers

  1. Assuming that you're running Windows 2008 R2 and SQL 2008 then:

    1. not really that hard to setup, once your storage and host cluster are done its just a few minutes more to configure the sql cluster.

    2. live migration is not a bad solution either, that way your not pinned to a blade within the cluster.

    • 1
  2. Best Answer

    Use 2, and then use MIRRORING for high availability. This gives you more than a SQL Cluster, because it gives you two copies of the data.

    This is the one weak point of a SQL Cluster - if the database dies in a way that damages the database file (and that DOES Happen - not exactly often, but if you go for high availability you do not want this), then the cluster fails over and the new starting SQL process.... will not load the database or will crash.

    With mirroring, the second server picks over on it's own copy of the data.

    A third (SMALL) SQL server as "witness" (so that there are 3 instances, makes the failover more stable) is suggested.

    Personally, I think (after spending some months at MS SQL Server support working for Microsoft) that using a SQL Cluster for high availability in the first line of defense is pretty much gross neglect. In 3 months I had like a case a month where the failover failed for VARIOUS reasons (one including the SAN system the data was held on crashing). This really makes you appreciate the double life data copy of a mirror instance.

    • 1