Introduction to WSFC with SQL Server on Windows Server 2019

Windows Server Failover Cluster (WSFC) with SQL Server provides high availability for SQL Server databases through SQL Server Failover Cluster Instances (FCI) and SQL Server Always On Availability Groups. An FCI presents SQL Server as a single virtual instance that runs on one node at a time and fails over to another node if the active node fails. Always On Availability Groups (AG), by contrast, maintain synchronised copies of databases on secondary replicas that can serve as readable secondaries or take over immediately upon failure.

This guide focuses on building a SQL Server 2019 FCI on a two-node Windows Server 2019 failover cluster with shared iSCSI or SAN storage, then extends it with a basic Availability Group configuration.

Preparing the Windows Cluster

Install Failover Clustering on both nodes:

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

Validate the cluster configuration:

Test-Cluster -Node "SQLNode1","SQLNode2" -Include "Storage","Network","Inventory","System Configuration"

Create the cluster with a dedicated cluster IP (separate from any SQL virtual IP):

New-Cluster -Name "SQLCluster" -Node "SQLNode1","SQLNode2" -StaticAddress 192.168.1.60

Configure quorum with a file share witness:

Set-ClusterQuorum -Cluster "SQLCluster" -FileShareWitness "\FileServerSQLWitness"

Add and prepare shared cluster disks for SQL Server (one for data, one for logs, one for backups, one for the quorum disk if needed):

Get-ClusterAvailableDisk -Cluster "SQLCluster" | Add-ClusterDisk

Installing SQL Server FCI — First Node

Run the SQL Server 2019 installer on the first node. From an elevated command prompt, launch a silent installation of the FCI:

Setup.exe /ACTION=InstallFailoverCluster /FEATURES=SQLENGINE,REPLICATION,FULLTEXT /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="CONTOSOsqlsvc" /SQLSVCPASSWORD="SQLSvcP@ss!" /AGTSVCACCOUNT="CONTOSOsqlagent" /AGTSVCPASSWORD="AgentP@ss!" /INSTALLSQLDATADIR="S:SQLData" /SQLUSERDBDIR="S:SQLDataData" /SQLUSERDBLOGDIR="L:SQLLogs" /SQLTEMPDBDIR="T:TempDB" /SQLTEMPDBLOGDIR="T:TempDB" /INSTALLSHAREDDIR="C:Program FilesMicrosoft SQL Server" /INSTANCEDIR="C:Program FilesMicrosoft SQL Server" /SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS" /SQLSYSADMINACCOUNTS="CONTOSODomain Admins" /INDICATEPROGRESS /CONFIRMIPDEPENDENCIES=0 /FAILOVERCLUSTERNETWORKNAME="SQLVCN" /FAILOVERCLUSTERIPADDRESSES="IPv4;192.168.1.70;Production;255.255.255.0" /FAILOVERCLUSTERDISKS="SQL Data Disk" /IACCEPTSQLSERVERLICENSETERMS /QUIET

Wait for the installation to complete and verify no errors in the summary log located at C:Program FilesMicrosoft SQL Server150Setup BootstrapLogSummary.txt.

Adding the Second Node to the FCI

On the second node, run the AddNode action to join the existing FCI:

Setup.exe /ACTION=AddNode /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="CONTOSOsqlsvc" /SQLSVCPASSWORD="SQLSvcP@ss!" /AGTSVCACCOUNT="CONTOSOsqlagent" /AGTSVCPASSWORD="AgentP@ss!" /CONFIRMIPDEPENDENCIES=0 /IACCEPTSQLSERVERLICENSETERMS /QUIET /INDICATEPROGRESS

After the second node is added, verify that both nodes appear as possible owners of the SQL Server cluster role:

Get-ClusterResource -Cluster "SQLCluster" -Name "SQL Server" | Get-ClusterOwnerNode

Testing FCI Failover

Test that the SQL Server FCI fails over correctly by moving the cluster group:

Move-ClusterGroup -Cluster "SQLCluster" -Name "SQL Server (MSSQLSERVER)" -Node "SQLNode2"

Connect to SQL Server using the virtual network name after failover to verify connectivity:

sqlcmd -S "SQLVCN" -Q "SELECT @@SERVERNAME, SERVERPROPERTY('ComputerNamePhysicalNetBIOS')"

Setting Up Always On Availability Groups

Always On AGs provide database-level high availability and can run on a WSFC. Enable the Always On feature on both SQL instances:

Enable-SqlAlwaysOn -ServerInstance "SQLVCN" -Force
Enable-SqlAlwaysOn -ServerInstance "SQLNode2MSSQLSERVER" -Force

Create a database to add to the AG, take a full backup, and restore it with NORECOVERY on the secondary. Then create the AG:

$primaryServer = Connect-SqlInstance -ServerInstance "SQLVCN"
$secondaryServer = Connect-SqlInstance -ServerInstance "SQLNode2"

New-SqlAvailabilityReplica -Name "SQLVCN" -EndpointUrl "TCP://SQLNode1.contoso.com:5022" -FailoverMode Automatic -AvailabilityMode SynchronousCommit -ConnectionModeInSecondaryRole AllowAllConnections -AsTemplate -Version 15

New-SqlAvailabilityReplica -Name "SQLNode2" -EndpointUrl "TCP://SQLNode2.contoso.com:5022" -FailoverMode Automatic -AvailabilityMode SynchronousCommit -ConnectionModeInSecondaryRole AllowAllConnections -AsTemplate -Version 15

Create the availability group on the primary with the database and listener:

New-SqlAvailabilityGroup -InputObject $primaryServer -Name "ProdAG" -AvailabilityReplica @($primaryReplica, $secondaryReplica) -Database "ProdDatabase"

New-SqlAvailabilityGroupListener -Name "ProdListener" -StaticIp "192.168.1.80/255.255.255.0" -InputObject "ProdAG" -Port 1433

Monitor the AG health:

Test-SqlAvailabilityGroup -Path "SQLSERVER:SQLSQLVCNDEFAULTAvailabilityGroupsProdAG"

Monitoring and Maintaining the WSFC SQL Deployment

After the WSFC and SQL Server configuration is in place, ongoing monitoring is essential. Check the FCI cluster group status regularly:

Get-ClusterGroup -Cluster "SQLCluster" | Select Name, State, OwnerNode

View SQL Server error log entries for any cluster-related events:

Get-EventLog -LogName "Application" -Source "MSSQLSERVER" -Newest 50 | Where-Object {$_.Message -like "*cluster*" -or $_.Message -like "*failover*"} | Select TimeGenerated, EntryType, Message

Test an FCI failover during a maintenance window to confirm everything works as expected. Move the SQL Server cluster group back to the original node:

Move-ClusterGroup -Cluster "SQLCluster" -Name "SQL Server (MSSQLSERVER)" -Node "SQLNode1"

For Always On Availability Groups, monitor synchronisation health using the AG dashboard in SQL Server Management Studio, or via T-SQL queries against the dynamic management views:

sqlcmd -S "SQLVCN" -Q "SELECT ag.name, ars.role_desc, ar.replica_server_name, drs.synchronization_state_desc, drs.synchronization_health_desc FROM sys.availability_groups ag JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id"

Combining Windows Server Failover Clustering with SQL Server 2019 FCI and Always On Availability Groups provides a multi-layer high availability architecture that protects both the SQL Server instance and individual databases, minimising downtime for mission-critical applications.