Freigeben über

SQL Server 2008 Hyper-V and high availability

  • Artikel

Imagine you have a windows 2003/SQL Server 2005 cluster (active-active) and your mission is to both virtualise and maintain high availability using Hyper-V in Windows Server 2008/SQL Server 2008.  This may sound odd but I was asked the question at TechEd and I wanted to do some research on it. Fortunately I on Windows 7 training this weekend and have access to 2 of the key players Jeff Woolsey who ‘owns’ Hyper-V development and clustering product manager Dave Dion. 

So are your options? and more importantly how do they help?

I would start this by addressing what are you most worried about…

  • Server outage
  • Site Outage
  • Network issues
  • SAN issues and media loss

Also what are your goals e.g.

  • Database availability
  • User response time
  • Supported by your solution providers (e.g. hardware vendors,  Microsoft etc.)
  • Ease of Maintenance, automation diagnostics
  • Predictability that when something goes wrong you know what will happen to mitigate it.

Finally other workloads are also on this setup which will also be virtualised.

The customer who asked me this wanted to cluster the cluster by..

  • Creating a hyper-V cluster ClusterH on Node1 and Node2
  • Creating  virtual machines (VM) vmA and vmB to run on that Cluster with a resource group for each VM such that vmA runs on Node1 and vmB runs on Node2 (i.e. active active). Both of these are using pass though disks i.e. they are talking directly to a LUN on a SAN and so will perform  very well.
  • Clustering vmA and vmB to form ClusterV with SQL Server 2008 installed.

So how does this stack up against our criteria above?

Basically not very good..

Initially this solution looks quite good - if node 1 or node 2 dies then the surviving vm on the surviving node will take  ownership of the storage. But this will take time. Currently hyper-V VM’s only support iSCSI (fibre channel is on the way). The two VMs will have different routes to the same shared storage so if that is not accessible then the system will fail. However the killer is that Microsoft do not support SQL Server failover clustering of VMs as per this KB. BTW this limitation applies to all virtualisation technologies, not just Hyper-V.

Is there a better way? For example if you mirrored the critical databases between vmA and vmB then you now have a copy of the database on either shared or separate storage.  You could then script a manual failover to the mirror if the node running on the principal moved over to the other node as its storage would now be remote to the VM. This is supported, and mitigates a number of failures including:

  • Hardware failure of a node.
  • Media failure on one of the nodes or part of the SAN.
  • Site failure if the nodes are remote to each other and have shared SAN
  • An instance of SQL Server crashing

Of course this is at the database level, but the principal could be extended for more than one database and other workloads as well. 

However in my opinion the best way to consolidate here is to create a physical cluster and consolidate SQL Server workloads on into it.  Clustering is a lot easier in Windows Server 2008 and you n o longer need carefully matched hardware. though the surviving nodes in a cluster need to be powerful enough to handle all of the work of the cluster.

Let me know what you think and what your plans are in this space.

Technorati Tags: sql server,high availability,virtualisation

Comments

  • Anonymous
    November 10, 2008
    The comment has been removed
  • Anonymous
    July 23, 2009
    Clustering of SQL 2005 and 2008 is support in Windows 2008 or later Hyper-V installs per the following KB article:  http://support.microsoft.com/Default.aspx?id=956893 The following restrictions apply to support for SQL Server products in any hardware virtualization environment: Guest Failover Clustering is supported for SQL Server 2005 and SQL Server 2008 in a virtual machine for Windows Server 2008 with Hyper-V, Microsoft Hyper-V Server 2008, and SVVP certified configurations provided both of the following requirements are met: Note Guest Failover Clustering is when you create a SQL Server failover cluster inside a virtual machine where the nodes are running as a virtual machine. A non-SVVP configuration that meets these requirements will receive support from Microsoft CSS per the policies documented in 897615  (http://support.microsoft.com/kb/897615/ ) Support policy for Microsoft software running in non-Microsoft hardware virtualization software The Operating System running in the virtual machine (the “Guest Operating System”) is Windows Server 2008 or higher The virtualization environment meets the requirements of Windows 2008 Failover Clustering as documented at The Microsoft Support Policy for Windows Server 2008 Failover Clusters, as documented in the following Microsoft Knowledge Base article: 943984  (http://support.microsoft.com/kb/943984/ ) The Microsoft Support Policy for Windows Server 2008 Failover Clusters The virtual machine must be running an operating system that is supported for the specific SQL Server version and edition and that is supported for virtualization. The SQL Server product must be a supported version under its current Microsoft Support Lifecycle policy. For more information about Microsoft Support Lifecycle policies, visit the following Microsoft Support Web site: http://support.microsoft.com/?pr=lifecycle (http://support.microsoft.com/?pr=lifecycle)