Go Premium for a chance to win a PS4. Enter to Win


SQL Clustering or HyperV Clustering

Posted on 2014-02-25
Medium Priority
Last Modified: 2016-10-27

We will soon be implementing our Dynamics AX installation.
Our aim is to have high availability and we are working closely with our implementers.

We raised the question to them about SQL and Hyper-V Clustering.
They recommended that when running in a complete virtualized platform there is no real reason to virtualize at application layer (SQL) and that just setting up Hyper V clustering in Windows Server and creating your SQL and all other related Application VM's on LUN's to back-end storage (in our case HP P2000) and just running the VHD files in high availability mode using CSV's.

Is this true? Or should we be clustering SQL separately at VHD level?

Many thanks!
Question by:dqnet
  • 3
  • 2
LVL 10

Expert Comment

ID: 39885876
This is an argument that will rage on for a a couple more years.  In my mind, it depends on what level of availability you are really trying to accomplish.  If you have one SAN / shelf, then your storage is a single point of failure anyway, so you are looking at accounting for physical failure of the hyper-visors.  That said, do you need your SQL Server instance to be instantly (at least as close to instantly as possible, anyway) available after the failure of one of those hyper-visors?  I would argue you're probably going to be sustaining downtime on one of the application servers that would cause system downtime anyway, so SQL would just be waiting for the highly available app servers to start back up anyway.  The general rule of thumb that I have heard, and rather like, is that if you need other services to fail over with your SQL Server instance, then you should be looking more seriously at relying on high availability/replication at the VM/SAN level vs. the application level.  And again, this is dependent on what level of availability you need.  If you absolutely must have it running as close to immediately as possible, then you're looking at clustering / sql server AGs vs. VMs.  Just my two cents!

Author Comment

ID: 39895321
Hello Padawan,

Thanks for your detailed response. However I am a little confused here.
Going by your response I am guessing you are saying that not clustering at SQL level (using Always on) or similar is a completely supported configuration providing Hyper V is clustered using the default Windows Clustered Services and creating the SQL virtual machine using the highly available cluster manager interface?

Question 2, Can we use Acronis installed on the host server to occasionally image backup the entire VHD whilst it is stored on our iSCSI volume? This should be fine right?  Provinding We will run SQL to backup the databases using maintenance plans individually.

Thanks a million.

Author Comment

ID: 39900694
LVL 10

Accepted Solution

PadawanDBA earned 2000 total points
ID: 39900906
Sorry for the delay on getting back to you!  Was a busy couple of days at work for me.

1)  I don't know if I would call it completely supported, you're going to have to account for more longer downtime w/hyper-v (from a SQL Server support perspective, but certainly should be from a hyper-v).  Unless there is drive failure on the san that causes data corruption, the data loss should be comparable to asynchronous high availability groups (aka - rolling back of uncommitted/incomplete transactions and roll forward on completed transactions that haven't been flushed to disk due to the shared disk).  Not sure if synchronous ha groups behave any differently.  But a highly available VM would certainly help you recover from a physical host failure.

2) As long as you are running in conjunction with native SQL Server backups, you should be fine to do whatever you want at the VHD backup level (assuming there's no adverse impact on SQL Server while you're doing it).  Key here is, as you mentioned, running native SQL Server backups for the databases as well =)

Author Comment

ID: 39914474
Perfect - thanks
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article outlines the struggles that Macs encounter in Windows-dominated workplace environments – and what Mac users can do to improve their network connectivity and remain productive.
Check out what's been happening in the Experts Exchange community.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question