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.

This is a re-hash of a question I asked a while back - after a consultant has come in firing ideas in to other teams in the department the whole issue has been raised again hence I'm looking for more detailed answers.

We're intending to set-up a multi-instance SQL Cluster across a number of physical blades which will run a variety of different systems across each SQL instance. In general use there will be one virtual SQL instance running on each VM host. Again, in general operation each VM host will run on a dedicated underlying blade. The set-up should give us lots of flexibility for maintenance of any individual VM or underlying blade with all the SQL instances able to fail over as required.

My original plan had been to do the following:

  1. Install 2008 R2 on each blade
  2. Add Hyper V to each blade
  3. Install a 2008 R2 VM to each blade
  4. Within the VMs - create a failover cluster and then install SQL Server clustering.

The consultant has suggested that we instead do the following:

  1. Install 2008 R2 on each blade
  2. Add Hyper V to each blade
  3. Install a 2008 R2 VM to each blade
  4. Create a cluster on the HOST machines which will host all the VMs.
  5. Within the VMs - create a failover cluster and then install SQL Server clustering.

The big difference is the addition of step 4 whereby we cluster all of the guest VMs as well. The argument is that it improves maintenance further since we have no ties at all between the SQL cluster and physical hardware. We can in theory live migrate the guest VMs around the hosts without affecting the SQL cluster at all so we for routine maintenance physical blades we move the SQL cluster around without interruption and without needing to failover.

It sounds like a nice idea but I've not come across anything on the internet where people say they've done this and it works OK. Can I actually do the live migrations of the guests without the SQL Cluster hosted within them getting upset?

Does anyone have any experience of this set up, good or bad? Are there some pros and cons that I've not considered?

I appreciate that mirroring is also a valuable option to consider - in this case we're favouring clustering since it will do the whole of each instance and we have a good number of databases. Some DBs are for lumbering 3rd party systems that may not even work kindly with mirroring (and my understanding of clustering is that fail overs are completely transparent to the clients).

Thanks.

share|improve this question
add comment

6 Answers

If those blades will be fully dedicated to running SQL Server only, why are you even bothering with virtualization?

Why don't you simply install Windows Server and SQL Server on each of them and set up your cluster accordingly, without the additional virtualization overhead?

share|improve this answer
    
The argument that's put across is flexbility - we can maintain any physical server without needing to failover the cluster. In theory we can live migrate the active VM to another blade without any downtime at all. I'm here to get the info to allow me to make the right recommendation and back it up if challenged. We have a "standard" here which is run everything in Hyper V... regardless of whether the blade is dedicated. That's where the original idea came from and since then it got more mileage from the external consultant who thinks it's a great idea too! –  Chris W Jun 7 '10 at 11:52
1  
That flexibility is gained by the SQL cluster (single machine independence), Hyper-V should be left out of it. Also, the last time we spoke to them, Microsoft does not support SQL Clusters spread across multiple Hyper-V machines (regardless of their being clustered or not). –  Chris S Jun 7 '10 at 12:35
    
if your trying to be cheap then go the consultants way, if not then just cluster sql. Don't run the hyper-v role and sql (or any service as far as that goes) on the same server since hyper-v drastically changes the way r2 runs and these changes will impact applications like sql server. –  tony roth Jun 7 '10 at 13:59
    
I don't really see why Hyper-V should be left out of it. I agree clustering SQL under a Hyper-V cluster probably doesn't make sense but that doesn't mean virtualizing SQL doesn't work. –  icky3000 Jun 7 '10 at 16:39
1  
From what I've seen published MS do support SQL across Hyper V machines from R2 onwards. Whether it's the best idea is another matter entirely... –  Chris W Jun 7 '10 at 17:09
add comment

Sounds complicated.

I would have to weigh up the "complexity" of your solution with the reliability and relative simplicity of a bog standard physical clustered SQL server implementation.

Are ALL the databases mission critical? In my experience, usually not, so I tend to host the most important databases on servers that are setup with full resilience and the rest (usually a large proportion) on simple SQL servers.

This allows you to concentrate on keeping the most important systems up and running, and not trying to keep "all the balls in the air at the same time".

All servers will need regular routine maintenance. Given the regularity and severity and importance of security patching, we've moved away from trying to maintain a theorectical 5 nine's up time (that the users liked, but didn't really NEED) to a more realistic "we will keep the servers safe and secure - but there WILL be short MANDATORY maintenance windows to allow us to keep servers properly patched."

share|improve this answer
    
... I'm still arguing for mandatory (& published) maintenance windows. I've not won that argument yet. My experience is the traditional physical cluster route but as I say, I joined at a time when everything runs on Hyper V regardless. I've pointed out the performance hit of running SQL on virtual given that the box is dedicated anyway but I'm up against the advice of consultants who have more experience. I'm new to the Hyper V world so hence I'm here looking for good collateral to argue my case with. –  Chris W Jun 7 '10 at 17:13
    
Virtualizing makes sense in order to improve resource utilization; it's completely useless if you're only going to run a single application in a single VM on each server. You don't even get the added benefit of better availability, as SQL Server is going to be clustered anyway. –  Massimo Jun 7 '10 at 17:25
1  
If you read my other posts, I'm a HUGE fan of virtualisation. I prefer vmware ESX (I still feel Hyper-V is playing catch up). Big wins for availability (HA, DRS), maintenance and flexiblity. But - you still need maintenance windows! Given MS publishes updates on "patch Tuesday", we have a "patch Sunday" each month that gives us enough time to test the patches on test servers, and than schedule reboots (if required) with the business. –  Guy Jun 7 '10 at 21:40
1  
You do need to ensure that your storage infrastructure can cope with 20 or 30 SQL servers running at the same time. It's easy for a storage expert to say "yeah, it's fine" but you have to multiply everything by the PEAK of ALL the servers (How long is your backup window?) and still have enough left for growth. My storage and back-end requirements have 10x in the last 3 years! –  Guy Jun 7 '10 at 21:42
1  
@Massimo: Agreed that with clustering SQL it is less appealing. I've kind of been assuming we had talked him out of doing clustering at both layers which I probably shouldn't have. @Guy: I agree Hyper-V is playing catch-up. I also agree with your point on maintenance windows. We have a regularly scheduled monthly window too. –  icky3000 Jun 8 '10 at 5:46
show 2 more comments

The advantage of your consultant's setup is that if you failover the hardware only, it won't protect you against a faulty service or what not. It's always good to have redundancy in both the hardware and the software, so if you ask me, I'd go for option 2 (considering you have the budget and knowledge for it, of course)

share|improve this answer
add comment

Of the listed options, I'd pick #2 but I wouldn't cluster SQL (step 5) because it's adding a layer of complexity that you don't gain much from. The Hyper-V clustering will already allow you to run that VM on either host so you're covered for hardware failures.

I assume you're planning to use fixed-size VHDs for the SQL log and database volumes.

I completely understand the comments from others about skipping Hyper-V altogether and just using the 2 blades as a normal SQL cluster - that would certainly be the traditional approach. However, the flexibility advantages to virtualizing workloads is huge for maintenance, upgrades, and hardware failures. The portability of VMs is very appealing.

Note though that the value of this solution also depends on your environment. If you have no other Hyper-V servers and your staff aren't too experienced with Hyper-V, virtualizing one of your most critical workloads might not be a good idea. However, if you're like many IT shops and started virtualizing less critical servers, have built up a few hosts and have the skills and procedures to reliably run Hyper-V, expanding that focus to more critical workloads is completely reasonable. Personally, I'd rather manage clustering at the host level vs at the SQL level and I think we'll see this being done more and more though it isn't yet as common.

Finally, your questions about running SQL on Hyper-V: yes, live migration will work fine with SQL and it won't notice - AND - SQL db mirroring is great but yes, it isn't universally supported so doesn't fit every situation.

share|improve this answer
    
Experienced staff is the key point - we're lacking in it. We already have SQL Servers running on a simple Hyper V cluster. We've had what I think is a driver problem where the VM NIC stopped responding to LAN traffic but the VM didn't failover automatically but the technical guys have been unable to explain it. A SQL cluster would have stayed available in that scenario hence the pressure to follow the consultant's advice. –  Chris W Jun 7 '10 at 17:05
    
Yeah, I see where clustering SQL could add another layer of redundancy but at the cost of significantly increased complexity and when your IT resources are limited, simple is better. I don't know your environment as well as you obviously so take my advice with a grain of salt. The primary thing I was trying to accomplish with my answer was to point out that virtualizing SQL in general is fine and it was mostly in response to other posts to this question that seemed eager to have you abandon Hyper-V as part of the solution altogether which I think is unwarranted. –  icky3000 Jun 7 '10 at 17:59
add comment

for the nic issue, you should be able to solve that with teamed nics, I dont know what your hardware is but there have been alot of issues like that with the Dell power edge 1950s, 2950s, R710s etc. where the nic becomes a recieve only nic and doesnt properly send traffic when in HyperV. Latest drives and correct teaming configuration will not only provide additional redundancy but it will increase performance aswell.

Really Option 1 and Option 2 are very near the same thing. The biggest question is how do you want your data to be accessed and what is your SAN designed for. SQL 2008 actually has performance increases when run in virtualization across clustered shared volumes and again clustering SQL the reason being is that SQL is smart enough to offload its processes to multiple SQL Nodes. This not only gives you a big boost in performance (imagine when intel first came out with true hyper threading) but it also increases your infrastructure performance for high overhead networks by being able to split up the traffic and use packet redirection.

If your looking for a hands on consultant or just an advisor please feel free to drop me an email.

-Josh.Smith@viridianit.com

share|improve this answer
    
Josh, welcome to Server Fault! You're well within the bounds of acceptable contributions; but just a note that self serving advertisements is generally frowned upon. See the FAQ (link in the top right of every page) if you have any questions. –  Chris S Sep 13 '10 at 20:23
add comment

I think your consultant has it right. I have the exact idea as he does, that I wish to implement in my current environment. 2 physical pieces of hardware each piece of hardware running Hyper-V with 2x W2k8 installations. 1.) Install 2x VM's on 1st physical Host. 2.) Install SQL on VM1 and Mirror or cluster to/with VM2 with fault tollerance setup on O/S level for failover to replicated environment. 3.) Install W2k8 on 2nd physical server and Failover cluster Hyper-V.

This gives your Complete level of failiver clustering and Complete H/A in your SQL environment.

Or maybe my idea is stupid? Regards Nick (

share|improve this answer
    
This is comment not an answer –  James A Mohler Dec 12 '12 at 17:52
add comment

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.