Published on May 18, 2025
Windows Server Failover Clustering (WSFC) is a high-availability and disaster recovery solution that enables multiple servers to work together to provide continuous availability for applications and services. When combined with SQL Server, it provides a robust platform for mission-critical databases that require maximum uptime.
The WSFC with SQL Server solution consists of several key components:
There are two main approaches to implementing high availability with SQL Server on WSFC:
In this model, SQL Server is installed on all nodes in the cluster, but only one node owns the SQL Server resources at any given time. If the active node fails, the SQL Server instance fails over to another node in the cluster.
This feature provides database-level protection by maintaining multiple copies of user databases across different instances of SQL Server. It enables high availability and disaster recovery at the database level rather than the instance level.
Implementing WSFC with SQL Server can present several challenges:
On each node that will be part of the cluster, install the Failover Clustering feature:
# Install Failover Clustering feature
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
# Verify installation
Get-WindowsFeature -Name *Failover* | Where-Object Installed
Before creating the cluster, validate the configuration:
# Run cluster validation
Test-Cluster -Node "Node1","Node2" -Include "Storage Spaces Direct", "Inventory", "Network", "System Configuration"
Once validation passes, create the cluster:
# Create a new failover cluster
New-Cluster -Name SQLCluster -Node "Node1","Node2" -StaticAddress 192.168.1.100 -NoStorage
# Verify cluster creation
Get-Cluster | Format-List *
Set up the quorum configuration:
# Set quorum to use Node and File Share Majority
Set-ClusterQuorum -NodeAndFileShareMajority \fileserverquorum$
# Verify quorum configuration
Get-ClusterQuorum
On each node, install SQL Server with the Failover Cluster feature:
# Install SQL Server with Failover Cluster feature
# Run this from the SQL Server installation media
Setup.exe /qs /ACTION=InstallFailoverCluster /INSTANCENAME=MSSQLSERVER /FEATURES=SQLENGINE /SQLSVCACCOUNT="DomainSQLService" /SQLSVCPASSWORD="YourStrongPassword" /AGTSVCACCOUNT="DomainSQLAgent" /AGTSVCPASSWORD="YourStrongPassword" /SQLSYSADMINACCOUNTS="DomainSQLAdmins" /INSTALLSQLDATADIR="E:SQLData" /SQLUSERDBLOGDIR="F:SQLLogs" /FTSVCACCOUNT="NT AUTHORITYNETWORK SERVICE" /IACCEPTSQLSERVERLICENSETERMS
Set up an Always On Availability Group:
# Enable Always On Availability Groups on each node
Enable-SqlAlwaysOn -ServerInstance "Node1MSSQLSERVER" -Force
Enable-SqlAlwaysOn -ServerInstance "Node2MSSQLSERVER" -Force
# Create endpoint for database mirroring
$endpoint = New-SqlHadrEndpoint "Hadr_endpoint" -Port 5022 -Path "SQLSERVER:SQLNode1MSSQLSERVER"
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
# Create availability group
$primaryReplica = New-SqlAvailabilityReplica -Name "Node1" -EndpointURL "TCP://Node1:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 15 -AsTemplate
$secondaryReplica = New-SqlAvailabilityReplica -Name "Node2" -EndpointURL "TCP://Node2:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 15 -AsTemplate
New-SqlAvailabilityGroup -Name "AG1" -Path "SQLSERVER:SQLNode1MSSQLSERVER" -AvailabilityReplica @($primaryReplica, $secondaryReplica) -Database "YourDatabase"
Create a listener to provide a single network name for client connections to the availability group:
# Create a new listener for the availability group
$AGName = "AG1"
$ListenerName = "SQL-Listener"
$Port = 1433
$IPAddress1 = "192.168.1.110" # First IP address for the listener
$IPAddress2 = "192.168.2.110" # Second IP address for another subnet (if needed)
$SubnetMask = "255.255.255.0"
# Create the listener
Add-SqlAvailabilityGroupListener -Name $ListenerName -StaticIp @(
(New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerStaticIp ($IPAddress1, $SubnetMask)),
(New-Object Microsoft.SqlServer.Management.Smo.AvailabilityGroupListenerStaticIp ($IPAddress2, $SubnetMask))
) -Port $Port -Path "SQLSERVER:SQLNode1MSSQLSERVERAvailabilityGroups$AGName"
# Verify the listener was created
Get-ChildItem "SQLSERVER:SQLNode1MSSQLSERVERAvailabilityGroups$AGNameAvailabilityGroupListeners"
# Get the listener DNS name and port
$listener = Get-ChildItem "SQLSERVER:SQLNode1MSSQLSERVERAvailabilityGroups$AGNameAvailabilityGroupListeners" |
Where-Object { $_.Name -eq $ListenerName }
$listener | Format-List *
If you want to route read-only workloads to secondary replicas:
# Configure read-only routing URLs
$primaryReplica = "Node1"
$secondaryReplica = "Node2"
$AGName = "AG1"
# Set read-only routing URL for primary replica
Set-SqlAvailabilityReplica -Path "SQLSERVER:SQL$primaryReplicaMSSQLSERVERAvailabilityGroups$AGNameAvailabilityReplicas$primaryReplica" -ReadonlyRoutingList @("TCP://$secondaryReplica.contoso.com:1433")
# Set read-only routing URL for secondary replica
Set-SqlAvailabilityReplica -Path "SQLSERVER:SQL$secondaryReplicaMSSQLSERVERAvailabilityGroups$AGNameAvailabilityReplicas$secondaryReplica" -ReadonlyRoutingList @("TCP://$primaryReplica.contoso.com:1433")
# Verify the configuration
Get-SqlAvailabilityReplica -Path "SQLSERVER:SQL$primaryReplicaMSSQLSERVERAvailabilityGroups$AGName" | Select-Object Name, ReadOnlyRoutingList, ConnectionUrlInPrimaryRole, ConnectionUrlInSecondaryRole
# Test connectivity to the listener using SQL authentication
$server = "SQL-Listener,1433"
$database = "master"
$username = "sa"
$password = "YourStrongPassword"
$connectionString = "Server=$server;Database=$database;User ID=$username;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
try {
$connection.Open()
Write-Host "Successfully connected to the listener!"
$command = $connection.CreateCommand()
$command.CommandText = "SELECT @@SERVERNAME as ServerName, DB_NAME() as DatabaseName"
$reader = $command.ExecuteReader()
while ($reader.Read()) {
Write-Host "Connected to server: $($reader['ServerName']) on database: $($reader['DatabaseName'])"
}
$reader.Close()
}
catch {
Write-Host "Failed to connect to the listener. Error: $_"
}
finally {
if ($connection.State -eq [System.Data.ConnectionState]::Open) {
$connection.Close()
}
}
# Check cluster status
Get-ClusterResource | Where-Object { $_.State -eq 'Failed' }
# Monitor availability group health
Get-ClusterResource | Where-Object { $_.ResourceType -like 'SQL Server Availability Group' } | Get-ClusterResource | Get-ClusterResourceDependency
# View cluster events for troubleshooting
Get-ClusterLog -TimeSpan 15 -UseLocalTime
Windows Server Failover Clustering with SQL Server provides a robust solution for organizations requiring high availability for their database workloads. By following this guide and utilizing the provided PowerShell commands, you can set up and maintain a highly available SQL Server environment. The combination of WSFC and SQL Server Always On Availability Groups ensures that your databases remain accessible even in the event of hardware or software failures, meeting your organization's requirements for uptime and data protection.