Hyper-V physical node with workgroup and Guest VMs in Active directory environment will support SQL clustering?

Question

Hello,

I have 2 physical HP node running with Windows server 2022 and one HP MSA storage fiber channel for sharing the volume with these physical nodes.

 

Planning to configure Hyper-V on physical node in workgroup due to the security requirement.

 

I want to configure MS SQL failover cluster in virtual machines. These virtual machines will be in Active directory environment and will be created in these physical nodes.

 

I am NOT sure cluster volume can be shared directly to VMs for SQL clustering as my physical node running in workgroup.

 

I know for sure volume can be shared directly to VMs when the physical node running in same active directory. 

Please help me if anyone has seen this scenario.

Thank you.

Answer 1

Hello, I am Henry, I will help with this.

You can have your Hyper-V physical nodes in a workgroup while the guest VMs running on them are members of an Active Directory domain and form a SQL Server Failover Cluster Instance (FCI).

The domain membership of the Hyper-V hosts is not a strict requirement for the guest VMs to access shared storage for their own cluster, especially when using Shared VHDX.

Recommended Approach: Using Shared VHDX on a Host-Level Cluster CSV

Even though your Hyper-V hosts are in a workgroup, you should still cluster them at the Hyper-V level. This provides high availability for your VMs and is the best way to utilize your HP MSA shared storage for Shared VHDX.

1. Create a Hyper-V Failover Cluster (Workgroup Cluster):

• On your two Windows Server 2022 physical nodes, install the Failover Clustering feature.
• You can create a failover cluster even if the nodes are in a workgroup. This has been supported since Windows Server 2016. You'll use local accounts for cluster authentication or certificate-based authentication if preferred for management. DNS registration for the cluster name object (CNO) will need to be handled manually or with specific DNS configurations, as there's no AD CNO.
• Why cluster the hosts? This allows you to create Cluster Shared Volumes (CSVs) which are necessary for Shared VHDX, and it provides HA for your VMs (if one host goes down, the VMs can failover).

2 Configure Shared Storage (HP MSA) for the Host Cluster:

• Present LUNs from your HP MSA via Fibre Channel to both physical Hyper-V nodes.
• Initialize and format these disks on one of the hosts.
• Add these disks to the Hyper-V host cluster as available storage.
• Convert these cluster disks to Cluster Shared Volumes (CSVs).

3. Create Guest VMs for SQL Server:

• Create your SQL Server virtual machines on the Hyper-V host cluster. These VMs will reside on the CSVs.
• Install Windows Server on these VMs.
• Join these guest VMs to your Active Directory domain.

4. Configure Shared VHDX for the Guest SQL Cluster:

• On the CSV of your Hyper-V host cluster, create the VHDX files that will be used as shared disks by your SQL Server VMs (e.g., for SQL data, logs, TempDB, MSDTC).
• In the settings of each SQL Server VM:
• Add a new SCSI Controller if one doesn't exist (generation 2 VMs have one by
• Add Hard Drive, selecting "Virtual hard disk."
• Browse to the VHDX file you created on the CSV.
• Go to the "Advanced Features" of this newly added virtual hard disk and check the box "Enable virtual hard disk sharing."
• Repeat this for all VHDX files and for all VMs that will be part of the SQL Server cluster. Each SQL VM will have the same set of shared VHDX files attached.

5. Configure Windows Server Failover Clustering Inside the Guest VMs:

• Install the Failover Clustering feature on all SQL Server guest VMs.
• Create a new cluster using these AD-joined guest VMs. The shared VHDX files will appear as regular shared disks to the guest OS and can be added to the guest cluster.
• Validate the cluster configuration.

6. Install SQL Server Failover Cluster Instance:

• Run the SQL Server setup on the first guest VM node and choose the "New SQL Server failover cluster installation" option.
• Use the shared disks (which are the Shared VHDX files) for your SQL Server data, logs, etc.
• Add the other guest VMs as nodes to this SQL Server FCI.

Why this works:

• Separation of Concerns: The Hyper-V host cluster (workgroup) manages the physical infrastructure and the CSVs. The guest SQL cluster (AD-joined) operates independently within the VMs, using the Shared VHDX files presented by the Hyper-V layer as its shared storage.
• Shared VHDX Mechanism: Shared VHDX is a Hyper-V feature that allows multiple VMs to access the same VHDX file simultaneously. It doesn't rely on the host's domain membership for its core functionality when the VHDX is on a CSV.
• Guest Cluster Authentication: The SQL guest cluster will use Active Directory for its authentication and Kerberos, which is perfectly fine.

Important Considerations:

• Host Cluster is Highly Recommended: While you could potentially use iSCSI presented from each workgroup host directly to the VMs (if the MSA can present LUNs that way or if you use Windows iSCSI Target on hosts), it's more complex and less robust than Shared VHDX on a host-level CSV. Clustering the hosts provides VM HA.
• DNS: Ensure proper DNS resolution for the guest VMs and the SQL Server FCI virtual name within your Active Directory DNS. For the workgroup host cluster, you'll need to manage its CNO and VCOs' DNS records manually or through specific configurations if you don't have write permissions to AD DNS from the workgroup.
• Quorum for Host Cluster: Configure the quorum for your workgroup Hyper-V host cluster appropriately (e.g., disk witness on the MSA, or a cloud witness/file share witness if applicable).
• Live Migration: With a Hyper-V host cluster and CSVs, you can Live Migrate your SQL VMs between the physical nodes without downtime (for the VM itself, SQL might have a brief failover if it's the active node being moved).

Let me know if you want more information.