How to Set Up Windows Server Failover Cluster (WSFC) with SQL Server on Windows Server 2016

A Windows Server Failover Cluster (WSFC) forms the foundation for SQL Server high availability solutions including SQL Server Failover Cluster Instances (FCI) and Always On Availability Groups. An FCI presents a single SQL Server instance that is hosted on clustered nodes with shared storage. When the active node fails, SQL Server restarts automatically on another node, providing high availability with a shared database. This guide covers setting up WSFC with SQL Server 2016 in an FCI configuration on Windows Server 2016.

Architecture Overview

In a SQL Server FCI, all cluster nodes share access to the same storage (SAN, iSCSI, or S2D). The SQL Server binaries are installed on each node but the data and log files reside on shared cluster storage. The cluster presents a virtual network name and IP address, and SQL clients connect to this virtual name rather than to a specific node. When failover occurs, the virtual name moves to the surviving node and SQL Server restarts against the shared storage.

Step 1: Prepare Active Directory and DNS

Before creating the cluster, ensure the computer accounts for the cluster nodes exist in Active Directory and that the account used to create the cluster has permissions to create computer objects. Pre-stage the cluster name object (CNO) in AD if your account lacks permissions to create computer objects:

# Run on a domain controller or machine with AD RSAT tools
Import-Module ActiveDirectory
New-ADComputer -Name "SQLCLUSTER01" -Path "OU=Servers,DC=domain,DC=local" -Enabled $false

Step 2: Install Failover Clustering on All Nodes

Install the Failover Clustering feature on all nodes that will participate in the SQL cluster. Also install the .NET Framework 3.5 which is required by SQL Server:

Install-WindowsFeature -Name Failover-Clustering, NET-Framework-Core -IncludeManagementTools
Restart-Computer -Force

Step 3: Configure Shared Storage

Ensure all cluster nodes can see the shared storage. For iSCSI-based shared storage, connect all nodes to the iSCSI target before creating the cluster. Only bring the disks online on one node at a time at this stage. Verify shared disks are visible on all nodes:

Get-Disk | Where-Object {$_.BusType -eq "iSCSI"}
Get-Disk | Sort-Object Number | Format-Table Number, FriendlyName, Size, PartitionStyle, OperationalStatus

Step 4: Validate and Create the Cluster

Run cluster validation and then create the cluster. Note that during validation, ensure all shared disks are brought offline so the test can properly evaluate them:

Test-Cluster -Node "SQLNode01","SQLNode02" -Include "Storage","Network","System Configuration"
New-Cluster -Name "SQLCLUSTER01" -Node "SQLNode01","SQLNode02" -StaticAddress "192.168.1.150" -NoStorage

After creating the cluster, add the shared disks to the cluster resource pool:

Get-ClusterAvailableDisk | Add-ClusterDisk

Step 5: Configure Quorum

Set an appropriate quorum for your two-node cluster. A disk witness using a small dedicated LUN is common for SQL clusters:

Set-ClusterQuorum -DiskWitness "Cluster Disk 1"

Alternatively, use a file share witness hosted on a server outside the cluster:

Set-ClusterQuorum -FileShareWitness "\FileServer01SQLWitness"

Step 6: Install SQL Server as a Failover Cluster Instance

Run the SQL Server installer on the primary node. Select New SQL Server failover cluster installation from the installation type screen. Key settings during installation include the SQL Server Network Name (the virtual name clients use to connect), the IP address for the cluster, and the selection of shared disks for data and log files.

From an elevated command prompt, you can also use an unattended installation. The following is a representative command (adjust paths and names for your environment):

Setup.exe /ACTION=InstallFailoverCluster /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="domainsqlservice" /SQLSVCPASSWORD="ServiceP@ss!" /AGTSVCACCOUNT="domainsqlagent" /INSTALLSQLDATADIR="S:SQLData" /INSTALLSQLSHAREDDIR="C:Program FilesMicrosoft SQL Server" /SQLUSERDBDIR="T:SQLData" /SQLUSERDBLOGDIR="L:SQLLogs" /FAILOVERCLUSTERNETWORKNAME="SQLCLUSTER01" /FAILOVERCLUSTERIPADDRESSES="IPv4;192.168.1.155;Cluster Network;255.255.255.0" /FAILOVERCLUSTERDISKS="SQL Data Disk" /SQLSYSADMINACCOUNTS="domainsqladmins" /IACCEPTSQLSERVERLICENSETERMS /QUIET

Step 7: Add the Second Node to the SQL FCI

After completing the primary node installation, run the SQL Server installer on the second node and select Add node to a SQL Server failover cluster. Use the same instance name and service accounts. From the command line:

Setup.exe /ACTION=AddNode /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="domainsqlservice" /SQLSVCPASSWORD="ServiceP@ss!" /AGTSVCACCOUNT="domainsqlagent" /FAILOVERCLUSTERNETWORKNAME="SQLCLUSTER01" /IACCEPTSQLSERVERLICENSETERMS /QUIET

Step 8: Test SQL Server Failover

Verify SQL Server is running and test failover. Connect to the SQL FCI using the virtual network name, then move the SQL Server resource group to the secondary node:

Move-ClusterGroup -Name "SQL Server (MSSQLSERVER)" -Node "SQLNode02"
Get-ClusterGroup -Name "SQL Server*"

After failover, verify SQL Server is accessible by connecting from a client using the virtual name. Verify databases are online and accessible:

Invoke-Sqlcmd -ServerInstance "SQLCLUSTER01" -Query "SELECT @@SERVERNAME, @@VERSION"

With WSFC and SQL Server FCI configured on Windows Server 2016, your SQL Server instance now automatically fails over to a secondary node during planned maintenance or unplanned outages, ensuring continuous availability for database-dependent applications.