Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster

Problem

In a previous tip on Step-by-step Installation of SQL Server 2016 on a Windows Server 2016 Failover Cluster - Part 1, I have seen how to install a SQL Server 2016 on a Windows Server 2016 failover cluster (WSFC). We are migrating to a SQL Server 2019 failover clusters running on Windows Server 2019. How do I go about installing and configuring a SQL Server 2019 failover cluster on Windows Server 2019?

Solution

Windows Server 2019 is the latest version of the Microsoft server operating system as part of the Windows Server family of operating systems. Windows Server Failover Clustering (WSFC) has become the platform of choice for providing high availability for SQL Server workloads - both for failover clustered instances and Availability Groups.

Some of the Windows Server 2019 failover clustering features that apply to SQL Server are listed below. This is in addition to the features introduced in previous versions of Windows Server. And while there are many features added to Windows Server 2019 Failover Clustering, not all of them are relevant to SQL Server.

Azure-aware clusters. Windows Server 2019 can now detect when you deploy a WSFC as an Azure virtual machine. This allows the WSFC to proactively avoid any downtime due to planned maintenance by automatically moving virtual machines between hosts during planned Azure maintenance schedules.
USB drive as a witness type. The ability to provide a USB drive instead of an additional server or Azure blob storage as a witness type is beneficial to small and medium-sized business. The USB drive can be attached to a network switch and accessible to all the WSFC nodes.
File share witness enhancements. Windows Server 2008 introduced the use of an SMB file share as an additional vote to achieve quorum. But because the file share witness required being hosted on a server joined to the same Active Directory domain as the WSFC, deploying workgroup-based WSFC was not an option for SQL Server high availability in organizations that do not have Active Directory. Enhancements to file share witness mean any device that supports SMB2 can create a file share that can be used as a witness.

In this series of tips, you will install a SQL Server 2019 failover clustered instance on a Windows Server 2019 failover cluster the traditional way - with Active Directory-joined servers and shared storage. Configuring TCP/IP and joining the servers to your Active Directory domain are outside the scope of this tip. Consult your systems administrators on how to perform these tasks. It is assumed that the servers that you will join to the WSFC are already joined to an Active Directory domain and that the domain user account that you will use to perform the installation and configuration has local Administrative privileges on all the servers.

Preparing the shared disks

Similar to this previous tip, you need to provision your shared storage depending on your requirement. This tip assumes that the underlying shared storage has already been physically attached to all the WSFC nodes and that the hardware meets the requirements defined in the Failover Clustering Hardware Requirements and Storage Options. Managing shared storage requires an understanding of your specific storage product which is outside the scope of this tip. Consult your storage vendor for more information.

In my environment, I've configured three (3) shared storage volumes - SQL_DISK_R, SQL_DISK_S, and SQL_DISK_T allocated for the SQL Server databases. A file share will be used as a witness instead of a shared disk and will be configured in the section Configuring the Cluster Quorum Settings.

The goal here is to provide shared storage both for capacity and performance. Perform the necessary storage stress tests to make sure that you are getting the appropriate amount of IOPs as promised by your storage vendor. You can use the DiskSpd utility for this purpose.

Adding the Failover Clustering Feature

Before you can create a failover cluster, you must install the Failover Clustering feature on all servers that you want to join in the WSFC. The Failover Clustering feature is not enabled, by default. If you plan to deploy several servers to be nodes in a WSFC, you can create a generic server OS deployment image that includes this feature. This can be done by using the Sysprep utility built into the Windows Server operating system. This reduces the amount of time and effort that goes into deploying a Windows Server operating system.

To add the Failover Clustering feature:

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

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.

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

In the Confirm installation selections dialog box, click Install to confirm the selection and proceed to do the installation. You may need to reboot the server after adding this feature.

Alternatively, you can run the PowerShell command below using the Install-WindowsFeature PowerShell cmdlet to install the Failover Clustering feature.

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

NOTE: Perform these steps on all the servers that you intend to join in your WSFC before proceeding to the next section.

Running the Failover Cluster Validation Wizard

Next, you need to run the Failover Cluster Validation Wizard from the Failover Cluster Management console. 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 will act as nodes in your WSFC.

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

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.

In the Testing Options dialog box, accept the default option Run all tests (recommended) and click Next. This will run all the necessary tests to validate whether the nodes are OK for the WSFC.

In the Confirmation dialog box, click Next. This will run all the necessary validation tests.

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

A note on the results: In the past, the Cluster Validation Wizard may report Warning messages pertaining to network and disk configuration issues, missing security updates, incompatible drivers, etc. The general recommendation has always been to resolve all errors and issues that the Cluster Validation Wizard reports prior to proceeding with the next steps. And it still is.

With Windows Server 2016 and later, checks for Storage Spaces Direct have been included in the Cluster Validation Wizard. Despite choosing the Run all tests (recommended) option, the Cluster Validation Wizard will exclude those checks.

Hence, why you will get a Warning message in the cluster validation report despite having all selected default checks return successful results.

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.

Alternatively, you can run the PowerShell command below using the Test-Cluster PowerShell cmdlet to run Failover Cluster Validation.

Test-Cluster -Node TDPRD071, TDPRD072

Creating the Windows Server 2019 Failover Cluster (WSFC)

After validating the servers, 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 immediately 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 will act as nodes in your WSFC.

To create the WSFC:

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

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

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 because the servers are within the same network subnet, only one virtual IP address is needed. This is a typical configuration for local high availability.

In the Confirmation dialog box, click Next. This will configure Failover Clustering on both servers that will act as nodes in your WSFC, add the configured shared storage, 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 in case 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 Configuring cluster accounts in Active Directory.

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 Steps for prestaging the cluster name account documentation to your Active Directory domain administrators.

In the Summary dialog box, verify that the report returns successful results. Click Finish.

Verify that the quorum configuration is using Node and Disk Majority - Witness: Cluster Disk n. Since all the shared disks will be used for the SQL Server failover clustered instance, you need to configure a file share as a witness type.

Alternatively, you can run the PowerShell command below using the New-Cluster PowerShell cmdlet to create a new WSFC.

New-Cluster -Name TDPRD070 -Node  TDPRD071, TDPRD072 -StaticAddress 172.16.0.70

Configuring the Cluster Quorum Settings

Since the Create Cluster Wizard will automatically use the shared disk with the smallest size as the 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 Modify permissions to it.

To configure the quorum in a failover cluster:

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

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

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

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

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

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

Alternatively, you can run the PowerShell command below using the Set-ClusterQuorum PowerShell cmdlet to configure the witness type.

Set-ClusterQuorum -NodeAndFileShareMajority \\CentralFileServer\FileShareWitness\TDPRDCLS070

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.

Congratulations!

You now have a working Windows Server 2019 failover cluster. You can now validate whether your WSFC is working or not. A simple test would be to do a continuous PING test on the virtual hostname or IP address that you have assigned to your WSFC. Reboot one of the nodes and see how your PING test responds.

In this tip, you've:

Seen some of the new Windows Server 2019 failover clustering features that apply to SQL Server workloads
Had an idea of how to provision shared storage for your WSFC
Added the Failover Clustering feature on all the servers that you intend to join in a WSFC
Ran the Failover Cluster Validation Wizard
Have seen the new validation checks for Storage Spaces Direct that can cause a Warning message when running the Failover Cluster Validation Wizard
Created the WSFC
Configured the witness type to use a Node and File Share Majority.

In the next tip in this series, you will configure the WSFC according to Microsoft bet practices in preparation for installing a SQL Server 2019 failover clustered instance.

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 to see the difference in the setup experience between a SQL Server 2016 Failover Clustered Instance on a Windows Server 2016 and a SQL Server 2019 Failover Clustered Instance on a Windows Server 2019.
Read more on the following topics:

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

Article Last Updated: 2020-09-01

Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 1