Installing SQL Server 2016 on a Windows Server 2016 Failover Cluster without Shared Storage - Part 1

By: Edwin Sarmiento   |   Updated: 2017-11-08   |   Comments   |   Related: 1 | 2 | 3 | > Clustering

Problem

We are planning on deploying a SQL Server 2016 failover clustered instance (FCI) for local high availability. From my understanding, a SQL Server FCI requires some form of shared storage subsystem. However, the servers that we purchased for our failover cluster nodes only have local storage. How can we install and configure a SQL Server 2016 FCI without shared storage?

Solution

SQL Server FCI requires a shared storage subsystem in the form of a storage area network (SAN), iSCSI targets or a combination of both. The shared storage subsystem is connected to all of the nodes in a SQL Server FCI and stores all of the database (MDF) and log (LDF) files.

Due to the emerging trends with both public and private cloud and software-defined data centers, several attempts were made to emulate shared storage to implement a more cost-effective solution. This is where third party solution providers come in. Their technology solution leverages locally attached storage in commodity server and configure them as shared storage that can be presented to the Windows Server Failover Cluster (WSFC). This eliminated the need for expensive, complex and single-point-of-failure risks associated with a SAN.

Microsoft’s “Cloud-first” Strategy and the Evolution of Enterprise Storage

When Satya Nadella took over as Microsoft’s CEO in 2014, he refocused the organizations strategy to move towards the cloud. Development efforts were focused on virtualization and cloud platforms, particularly, Hyper-V and Microsoft Azure. Microsoft’s experiences and learnings from deploying platforms on Azure became the basis for introducing new features in the on-premises versions of their products – Windows Server, SQL Server, Hyper-V, System Center and more. Deploying systems at a very large scale meant finding new ways to reduce total cost of ownership. One of them is eliminating the need for expensive storage solution like the SAN.

Storage virtualization, the process of presenting a logical view of the physical storage resources to a host computer system, is not a new concept. You can create logical partitions of your physical storage and present them to your computer or server. This concept has also been used in virtualization technologies where a virtual hard disk is used to represent a virtual machine. From the expensive SAN to the concept of storage virtualization, a new wave of IT virtualization platforms was introduced – converged and hyperconverged.

Converged infrastructures come as pre-packaged stack of hardware that combines storage, compute and networking that is validated and tested by the vendor. Hyperconverged infrastructures take the concept a step further by providing storage and compute virtualization of the hardware resources. Microsoft leveraged these two platforms in their recent releases of the Windows Server operating system, calling it the “cloud-ready operating system”.

Windows Server 2016 Storage Spaces Direct (S2D)

Windows Server 2016 introduced a new feature called Storage Spaces Direct (S2D). S2D uses commodity servers with locally attached drives to create highly available, highly scalable software-defined storage. Not only does this feature replaces expensive SAN but also presents local storage as if it was shared storage on a WSFC without the need for third-party applications. However, it is only included in the Datacenter Edition.

The requirements for running S2D are the same as that of a WSFC. S2D leverages Server Message Block (SMB) 3 and the Cluster Shared Volume (CSV) file system to emulate shared storage across all of the nodes in the WSFC. And because it uses ethernet to access all of the storage on the other nodes in the WSFC, it is recommended to use a remote-direct memory access (RDMA) capable network adapter on a 10 GbE or higher network switch. You certainly don’t want the WSFC nodes to only have a single network adapter. You should have a minimum of three (3) network adapters – one for production traffic, one for the S2D traffic and one for heartbeat/internal communication traffic.

Preparing the Windows Server 2016 Failover Cluster

In the previous tip on Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster - Part 1, the section on Preparing the shared disks highlights configuring the shared storage subsystem prior to building the WSFC. Not in this case, though. The servers that will be used as WSFC nodes only have locally attached storage which will be configured as part of S2D.

You can use a combination of rotating disks for capacity and solid state drives (SSDs) and non-volatile memory express (NVMe) for performance. Additional information on choosing drives for S2D deployments can be found in this Microsoft documentation.

The configuration used in this tip consists of two (2) Windows Server 2016 Datacenter Edition servers, both are joined to an Active Directory domain. The servers each have three, locally attached 1 TB rotating disks.

Adding the Failover Clustering Feature

Before you can create a WSFC, you must install the Failover Clustering feature on all servers that you want to include in the WSFC. The Failover Clustering feature is not enabled, by default. If you plan to deploy several servers to be members of a WSFC, you can create a generic server OS deployment image that includes this feature.

To add the Failover Clustering feature:

1. Open the Server Manager Dashboard and click the Add roles and features link. This will run the Add Roles and Features Wizard.

2. Click thru the different dialog boxes until you reach the Select features dialog box. In the Select features dialog box, select the Failover Clustering checkbox and click Next.

When prompted with the Add features that are required for Failover Clustering dialog box, click Add Features. Click Next.

NOTE: In previous versions of SQL Server, you were required to install the .NET Framework 3.5 with SP 1. This is no longer the case with SQL Server 2016.

3. In the Confirm installation selections dialog box, click Install to confirm the selection and proceed to do the installation.

NOTE: Perform these steps on all of the servers that you intend to join as nodes in your WSFC.

Another way of installing the Failover Clustering feature is by using the Install-WindowsFeature PowerShell cmdlet as described in this previous tip

Install-WindowsFeature –Name Failover-Clustering –IncludeManagementTools

Running the Failover Cluster Validation Wizard

Next, you need to run the Failover Cluster Validation Wizard from the Failover Cluster Management console as described in this previous tip. You can launch the tool from the Server Manager dashboard, under Tools and select Failover Cluster Manager.

NOTE: These steps can be performed on any of the servers that you intend to join as nodes in your WSFC.

1. In the Failover Cluster Management console, under the Management section, click the Validate Configuration link. This will run the Validate a Configuration Wizard.

2. In the Select Servers or a Cluster dialog box, enter the hostnames of the servers that you want to add as nodes of your WSFC. Click Next.

3. In the Testing Options dialog box, select the option Run only tests I select and click Next.

4. In the Test Selection dialog box, select the Storage Spaces Direct checkbox and click Next.

5. In the Confirmation dialog box, click Next. This will run all the necessary validation tests, including those for Storage Spaces Direct.

6. In the Summary dialog box, verify that all the selected checks return successful results.

Another way of running the Failover Cluster Validation is by using the Test-Cluster PowerShell cmdlet as described in this previous tip.

Test-Cluster -Node WSFC2016-NODE1, WSFC2016-NODE2 -Include "Storage Spaces Direct", "Inventory", "Network", "System Configuration"

A note on the results: Unlike in this previous tip where running the Failover Cluster Validation Wizard will report a Warning message for excluding the tests, notice that the report returned Success. Opening the Failover Cluster Validation Report will show the Storage tests as Not Applicable and the Storage Spaces Direct tests as Success.

Scrolling down to the section on Storage will show you all the checks that have been skipped.

Scrolling down to the section on Storage Spaces Direct will show you all the checks that have been performed.

7. To create the WSFC using the servers you've just validated, select the Create the cluster now using the validated nodes...checkbox and click Finish.

Creating the Windows Server 2016 Failover Cluster (WSFC)

You will create the WSFC using the Failover Cluster Manager console. You can launch the tool from the Server Manager dashboard, under Tools and select Failover Cluster Manager. Alternatively, you can run the Create Cluster Wizard after running the Failover Cluster Validation Wizard. Be sure to check the Create the cluster now using the validated nodes... checkbox.

NOTE: These steps can be performed on any of the servers that you intend to join as nodes in your WSFC.

To create the WSFC:

1. Within the Failover Cluster Manager console, under the Management section, click the Create Cluster... link. This will run the Create Cluster Wizard.

2. In the Select Servers dialog box, enter the hostnames of the servers that you want to add as nodes of your WSFC. Click Next.

3. In the Access Point for Administering the Cluster dialog box, enter the virtual hostname and IP address that you will use to administer the WSFC. Click Next. Note that the servers have three network adapters – one for production traffic, one for heartbeat/internal communications and one for S2D traffic.

4. In the Confirmation dialog box, uncheck the Add all eligible storage to the cluster checkbox and click Next. This will configure Failover Clustering on both of the servers that will act as nodes in your WSFC, add Active Directory and DNS entries for the WSFC virtual server name.

A word of caution before proceeding: Before clicking Next, be sure to coordinate with your Active Directory domain administrators on the appropriate permissions that you need to create the computer name object in Active Directory. It will save you a lot of time and headache troubleshooting why you cannot create a WSFC. Local Administrator permission on the servers that you will use as nodes in your WSFC is not enough. Your Active Directory domain account needs the following permissions in the Computers Organizational Unit. By default, this is where the computer name object that represents the virtual hostname for your WSFC will be created.

• Create Computer objects
• Read All Properties

For additional information, refer to the Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory documentation.

In a more restrictive environment where your Active Directory domain administrators are not allowed to grant you those permissions, you can request them to pre-stage the computer name object in Active Directory. Provide the Prestage Cluster Computer Objects in Active Directory Domain Services documentation to your Active Directory domain administrators.

5. In the Summary dialog box, verify that the report returns a Warning message about a witness. Click Finish.

Note on the Warning message: Because the servers only have locally attached storage, the cluster creation process could not assign an appropriate disk to configure as a disk witness. The witness type can be configured at a later time.

Another way of creating the WSFC is by using the New-Cluster PowerShell cmdlet as described in this previous tip.

New-Cluster -Name WIN2K16-CLUSTER -Node WSFC2016-NODE1, WSFC2016-NODE2 -StaticAddress 172.16.0.103 -NoStorage

Configuring the Cluster Quorum Settings

Since the Create Cluster Wizard could not assign an appropriate disk to configure as a disk witness, you will configure the cluster quorum settings to use Node and File Share Majority. Before you change the configuration, you need to create a file share and grant the WSFC virtual server name Read/Write permissions to it.

To configure the quorum in a failover cluster:

1. Open the Failover Cluster Management console
2. Select the name of the WSFC you have just created. Right-click, select More Actions, and click Configure Cluster Quorum Settings... This will open up the Configure Cluster Quorum Wizard.

3. In the Select Quorum Configuration Option dialog box, select the Select the quorum witness option. Click Next.

4. In the Select Quorum Witness dialog box, select the Configure a file share witness option. Click Next.

5. In the Configure File Share Witness dialog box, provide the file share location that you want your WSFC to use as the quorum/witness. Click Next

6. In the Confirmation dialog box, verify that the file share configuration for the quorum/witness is correct. Click Next.

7. In the Summary dialog box, verify that the entire configuration is successful.

Another way of configuring the WSFC quorum configuration is by using the Set-ClusterQuorum PowerShell cmdlet.

Set-ClusterQuorum -NodeAndFileShareMajority \\HA-FileShare\FileShareWitness

You can verify that the cluster quorum setting is now configured to use the file share witness by looking at the Cluster Core Resources section.

In this tip, you have created a Windows Server 2016 failover cluster without shared storage and configured the quorum settings. In the next tip, you will proceed to enable and configure Storage Spaces Direct as well as configure a dedicated network adapter for the S2D traffic.

Next Steps

• Review the previous tips on Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster - Part 1, Part 2, Part 3 and Part 4.
• Read more on the following topics
  • Storage Spaces Direct in Windows Server 2016
  • Choosing drives for Storage Spaces Direct
  • SQL Server 2016 now supports Windows Server 2016 Storage Spaces Direct

About the author

Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips