Using SQL Server Failover Clustering with vSAN iSCSI Target Service: Guidelines for supported configurations

Products

• VMware vSAN

Issue/Introduction

This article outlines the supported configurations and processes to create a SQL Server Failover Clustering Instance (FCI) on VMware vSAN via. the vSAN iSCSI Target Service. The vSAN iSCSI service provides the shared disks with Persistent Reservations for Microsoft Windows Server Failover Cluster (WSFC) hosted active-passive clustered application roles like FCI.

Notes:

• vSAN native fully supports Microsoft cluster techniques with shared disks on 6.7 U3 or higher version.
• vSAN iSCSI Target Service supports Microsoft clustering with shared disks on 6.7 or higher version.
• RDMs for WSFC using iSCSI Target Service is not officially supported on vSAN.
• Initiators can be either from virtual machines or physical servers. For guest initiators in virtual machines, those virtual machines can be residing on:
  • The same vSAN cluster that provides the iSCSI target service
  • An external vSAN or vSphere cluster.
• vSAN Stretched Clusters using iSCSI Target Service are not supported.

 

Supported platform and OS versions

vSAN iSCSI is supported with Windows/SQL versions 2012 and higher

Environment

VMware vSAN 6.x
VMware vSAN 8.0.x
VMware vSAN 6.7.x
VMware vSAN 7.0.x

Resolution

Overview

The process of running SQL Server Failover Clustering on vSAN 6.7 iSCSI service requires these steps:

• Configuring the iSCSI service on vSAN.
• Configuring the Windows Cluster and iSCSI disk for the SQL Server.
• Setting up the SQL Server Cluster

We provide links where appropriate.

Please note that iSCSI traffic should have a dedicated VLAN for best performance. If you are seeing latency or APD's and are not currently utilizing a dedicated VLAN for iSCSI traffic, we recommend moving to a dedicated VLAN before any deep dive troubleshooting.

Configuring iSCSI Service on vSAN

The high-level steps to configure iSCSI service on vSAN are:

1. Enable the iSCSI Target Service on the vSAN cluster
2. Create an iSCSI Target.
3. Add one or more LUNs to an iSCSI Target, or edit an existing LUN
4. Create an iSCSI Initiator Group to provide access control for iSCSI targets. Only iSCSI initiators that are members of the initiator group can access the iSCSI targets. Without the definition of the Initiator group, any initiator can see any target.
5. Assign a Target to an iSCSI Initiator Group

For more information, see Using the vSAN iSCSI Target Service.

After configuring the vSAN iSCSI target service, you can discover the vSAN iSCSI targets from the physical or virtual servers via software initiator. To discover vSAN iSCSI targets, you can use the IP address of any host in the vSAN cluster and the TCP port of the iSCSI target.

Recommendation: To ensure high availability of the vSAN iSCSI target, configure multipath support for your iSCSI application, you can use the IP addresses of two or more hosts to configure MPIO.
 

Configuring Windows Cluster and iSCSI disks for the SQL Server

There are several steps to configure the Windows Cluster and the iSCSI shared disks. These steps must be done before installing the SQL Server failover cluster instance on the guest OS in the virtual machine. We recommend using the PowerShell and PowerCLI to automate the process and minimize potential setup errors and time spent on configuration steps. Please see Configuration in a VM to setup iSCSI initiator and cluster shared disks for SQL Server and File Server for sample code of the automatic configuration. You may also use the manual steps below:

On each host of the Windows Cluster, follow these steps:

1. Enable the Failover Clustering and MPIO features on the Windows server to be as SQL Server Failover Clustering node

The following screenshot shows the Failover Clustering feature was installed on the WSFC node.

2. Enable MPIO for iSCSI devices by clicking Start and searching for MPIO on Windows, Double click to run it.
   1. Navigate to the Discover Multi-Paths tab
   2. Check the box to Add Support For iSCSI Devices, click OK.
   3. Reboot the virtual machine when prompted.

The following screenshot shows MPIO for iSCSI devices is enabled already.

3. Discover the target on each node of the Windows cluster and connect them. When connecting to a target, you need to make sure enable multi-path, as below

4. Connect the initiator to multiple target portal IP addresses to ensure multiple paths from the initiator to the target/owner of the LUNs.

5. Verify the MPIO policy is Fail Over only for VMware vSAN iSCSI disks which is set in the Devices Details below

 

6. Create the Windows cluster on node of the Windows cluster, test the nodes and create the cluster. The following screenshot shows the SQL Server cluster.

 

7. Format the disks using 64k allocation unit size.
8. Add disks to the Windows cluster so they are ready for usage by the SQL Server.

At this point, the Windows cluster is ready for a cluster application or service such as SQL Server FCI.
 

Setting Up the SQL Server Cluster

A Windows Server Failover Cluster (WSFC) is a group of independent servers that work together to increase the availability of applications and services. A Failover Cluster Instance (FCI) is a SQL Server instance that is installed across nodes in a WSFC. In the event of a failover, the WSFC service transfers ownership of instance's resources to a designated failover node. The SQL Server instance is then restarted on the failover node, and databases are recovered as usual. At any given moment, only a single node in the cluster can host the FCI and underlying resources. That means the FCI is active/passive high availability solution.

Please follow the details in Create a New SQL Server Failover Cluster (Setup) to set up a clustered SQL Server.

Best Practices

The following best practices are recommended for configuring the vSAN iSCSI service for SQL Server Failover Cluster Instance on a vSAN Cluster.

• Do not have more than 16 targets in one vSAN cluster is highly recommended
• Do not have more than 128 LUNS in one vSAN cluster is highly recommended
• Do not have more than 128 sessions on each host in a vSAN cluster
• Separate the vSAN traffic and iSCSI traffic on different physical network cards is highly recommended
• For a mission-critical application, we recommend using one target with one LUN mapping, although multiple LUNs under one target can have management convenience.
• We recommend setting multiple paths for each target on the guest OS. And set the following recommended values using PowerShell. See the Set-MPIOSetting for the default settings. We highly recommend using the script found at Configuration in a VM to setup iSCSI initiator and cluster shared disks for SQL Server and File Server to set values.

Settings	Description	Recommended Value
Custom Path Recovery	The value to specify whether MPIO performs custom path recovery	Enabled
NewPathRecoveryInterval	The value to specify a customer path recovery time in seconds.	20 seconds
NewRetryCount	The value to specify the number of times to retry an IO request	60
NewPDORemovePeriod	The value to specify the physical device object (PDO) removal period in seconds.	60 seconds
NewPathVerificationPeriod	The value to specify the length of time to verify a path in seconds.	30 seconds
NewDiskTimeout	The value to specify the length of time the server waits before it marks an IO request as timed out.	60 seconds

• We only support active/passive high availability of iSCSI service, the only supported failover policy is Failover Only (FOO). We highly recommend using the script found at Configuration in a virtual machine to setup iSCSI initiator and cluster shared disks for SQL Server and File Server to set the policy so that the newly discovered disks can identify and leverage the policy automatically
• Do not set the multiple path number of each virtual disk is to more than the value of FTT+1. This setting is to limit the session number while keeping multiple paths alive if there is host failure
• iSCSI vSAN allows multiple clustered Windows nodes to reside on the same host; this is one of the benefits of the solution. However, it might hurt the mission-critical application workloads when multiple nodes are on the same host. To avoid this happened, we suggest using DRS VM-Host Affinity Rule or using PowerShell script to balance the virtual machine placement automatically.
• We recommend enabling Jumbo Frame on both the physical switch and distributed switch by setting the MTU to 9000 for better performance.

Additional Information

• Configuration in VM to set up iSCSI initiator and cluster shared disks for SQL Server and File Server
• Set-MPIOSetting

Impact/Risks:
https://docs.microsoft.com/en-us/powershell/module/mpio/set-mpiosetting?view=win10-ps