How to Set Up Windows Server Failover Cluster with SQL Server on Windows Server 2012 R2

A SQL Server Failover Cluster Instance (FCI) uses Windows Server Failover Clustering (WSFC) to provide high availability for SQL Server databases. In a traditional FCI, SQL Server runs on a single active node at a time, with shared storage accessible to all nodes. If the active node fails, the cluster automatically restarts the SQL Server service on a surviving node, minimizing downtime. This differs from SQL Server Always On Availability Groups, which can run on separate storage. This guide covers deploying SQL Server as a clustered instance on Windows Server 2012 R2 with WSFC.

Prerequisites

You need two or more Windows Server 2012 R2 servers joined to the same Active Directory domain with the Failover Clustering feature installed and validated (see the Failover Clustering configuration guide). Shared storage must be accessible to all cluster nodes — iSCSI, Fibre Channel, or SAS. SQL Server 2012 or SQL Server 2014 installation media is required. A dedicated service account for SQL Server should be pre-created in Active Directory with minimal necessary permissions. IP addresses must be available for the Windows cluster name and the SQL Server FCI virtual name. .NET Framework 3.5 and .NET Framework 4.5 must be installed on all nodes.

Step 1: Prepare the Windows Server Failover Cluster

Ensure the base WSFC cluster is already created and healthy before installing SQL Server. Verify cluster health:

Get-Cluster | Select-Object Name, Domain
Get-ClusterNode | Select-Object Name, State
Get-ClusterResource | Select-Object Name, State, OwnerNode

Ensure at least two disk resources are available in the cluster: one for SQL Server data/log files and one for the quorum (if disk witness is used). Add additional shared disks for SQL Server:

Get-ClusterAvailableDisk | Add-ClusterDisk

Step 2: Install Prerequisites on All Nodes

Install required Windows features on all cluster nodes:

$nodes = @("SQLNode01", "SQLNode02")
$nodes | ForEach-Object {
    Invoke-Command -ComputerName $_ -ScriptBlock {
        Install-WindowsFeature NET-Framework-Core, NET-Framework-45-Features -IncludeAllSubFeature
        Install-WindowsFeature Failover-Clustering -IncludeManagementTools
    }
}

Step 3: Create the SQL Server Service Account

Create a dedicated domain service account for SQL Server. This account needs specific local group memberships and permissions on cluster nodes:

# On the Domain Controller
New-ADUser -Name "svc-sqlserver" `
    -SamAccountName "svc-sqlserver" `
    -UserPrincipalName "[email protected]" `
    -AccountPassword (ConvertTo-SecureString "Str0ngP@ssw0rd!" -AsPlainText -Force) `
    -PasswordNeverExpires $true `
    -CannotChangePassword $true `
    -Enabled $true

Grant the service account local administrator rights on all cluster nodes (required for SQL Server installation). Use Group Policy or manually add it:

# On each node
Add-LocalGroupMember -Group "Administrators" -Member "CONTOSOsvc-sqlserver"

Step 4: Install SQL Server Failover Cluster Instance (First Node)

Run the SQL Server setup on the first node with the /Action=InstallFailoverCluster parameter. This example uses SQL Server 2014:

D:Setup.exe /Q /Action=InstallFailoverCluster `
    /InstanceName=MSSQLSERVER `
    /Features=SQLENGINE,REPLICATION `
    /SQLCollation=SQL_Latin1_General_CP1_CI_AS `
    /SQLSVCACCOUNT="CONTOSOsvc-sqlserver" `
    /SQLSVCPASSWORD="Str0ngP@ssw0rd!" `
    /AGTSVCACCOUNT="CONTOSOsvc-sqlserver" `
    /AGTSVCPASSWORD="Str0ngP@ssw0rd!" `
    /INSTALLSQLDATADIR="E:SQLData" `
    /SQLUSERDBDIR="E:SQLData" `
    /SQLUSERDBLOGDIR="F:SQLLogs" `
    /SQLTEMPDBDIR="G:SQLTempDB" `
    /FAILOVERCLUSTERNETWORKNAME="SQLCLUSTER01" `
    /FAILOVERCLUSTERIPADDRESSES="IPv4;192.168.1.110;Cluster Network 1;255.255.255.0" `
    /FAILOVERCLUSTERDISKS="Cluster Disk 2;Cluster Disk 3" `
    /SQLSYSADMINACCOUNTS="CONTOSODomain Admins" `
    /CONFIRMIPDEPENDENCYCHANGE=0 `
    /IACCEPTSQLSERVERLICENSETERMS

Monitor installation progress by watching the setup log:

Get-Content "C:Program FilesMicrosoft SQL Server120Setup BootstrapLogSummary.txt" -Tail 30

Step 5: Add the Second Node to the SQL Server FCI

On the second cluster node, run SQL Server setup with the /Action=AddNode parameter:

D:Setup.exe /Q /Action=AddNode `
    /InstanceName=MSSQLSERVER `
    /SQLSVCACCOUNT="CONTOSOsvc-sqlserver" `
    /SQLSVCPASSWORD="Str0ngP@ssw0rd!" `
    /AGTSVCACCOUNT="CONTOSOsvc-sqlserver" `
    /AGTSVCPASSWORD="Str0ngP@ssw0rd!" `
    /FAILOVERCLUSTERNETWORKNAME="SQLCLUSTER01" `
    /CONFIRMIPDEPENDENCYCHANGE=0 `
    /IACCEPTSQLSERVERLICENSETERMS

Step 6: Verify the SQL Server Cluster Installation

After adding all nodes, verify the FCI is correctly configured in Failover Cluster Manager and via PowerShell:

Get-ClusterResource | Where-Object {$_.ResourceType -like "*SQL*"} | Select-Object Name, State, OwnerNode

Get-ClusterGroup | Select-Object Name, State, OwnerNode

Connect to SQL Server using the virtual network name and verify it responds:

Invoke-Sqlcmd -ServerInstance "SQLCLUSTER01" -Query "SELECT @@SERVERNAME, @@VERSION, SERVERPROPERTY('IsClustered')" -Username "sa"

Step 7: Configure SQL Server Failover Settings

Configure the failure thresholds and recovery actions for the SQL Server resource group:

# Set maximum failures in a period
(Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)").FailoverThreshold = 2

# Set the period (in hours)
(Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)").FailoverPeriod = 6

# Set failback policy
(Get-ClusterGroup -Name "SQL Server (MSSQLSERVER)").AutoFailbackType = 0  # Prevent auto failback

Step 8: Test SQL Server Failover

Test failover by moving the SQL Server group to the second node:

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

Monitor the failover and verify SQL Server comes online on the new node:

Get-ClusterResource | Select-Object Name, State, OwnerNode | Where-Object {$_.Name -like "*SQL*"}

# Test connectivity via virtual name
Invoke-Sqlcmd -ServerInstance "SQLCLUSTER01" -Query "SELECT @@SERVERNAME, SERVERPROPERTY('ComputerNamePhysicalNetBIOS')"

Step 9: Configure SQL Server for Optimal Cluster Performance

Configure SQL Server memory limits to prevent the OS from being starved of memory (critical in clustered environments where multiple roles might coexist):

Invoke-Sqlcmd -ServerInstance "SQLCLUSTER01" -Query "
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory', 24576;
EXEC sp_configure 'min server memory', 4096;
RECONFIGURE;"

Enable trace flag 1117 and 1118 for improved tempdb performance in SQL Server 2014 and earlier:

Invoke-Sqlcmd -ServerInstance "SQLCLUSTER01" -Query "
DBCC TRACEON(1117, 1118, -1);"

Summary

Deploying SQL Server as a Failover Cluster Instance on Windows Server 2012 R2 provides automatic failover protection for SQL Server databases, ensuring minimal downtime during planned maintenance or unexpected node failures. The combination of WSFC shared storage architecture with SQL Server’s FCI capabilities delivers a well-established, production-grade high availability solution that is supported by Microsoft and compatible with all SQL Server features.