DB-유

SQL Server Failover Cluster Instances Overview

· mssql, high-availability, windows

A Failover Cluster Instance (FCI) is the older of SQL Server's two high-availability technologies and, in many environments, still the right one. A single SQL Server instance is installed onto a Windows Server Failover Cluster (WSFC); its data lives on storage that all nodes can reach; and when the active node fails, the cluster starts the same instance, with the same name, IP and databases, on another node. Applications see a reconnect after 30 to 90 seconds and nothing else changes.

Compared to Availability Groups, an FCI protects the whole instance including master, msdb, logins, Agent jobs and linked servers, and it does so with Standard Edition (two nodes). What it does not do is protect against storage failure or provide readable copies. I still choose an FCI more often than people expect, mostly because applications that lean on msdb jobs and linked servers just keep working. Here's how it fits together, how to build a two-node cluster on Windows Server 2016 with SQL Server 2017, and what trips people up.

How an FCI works

  • WSFC provides node membership, heartbeat, quorum and the resource model.
  • Shared storage: iSCSI or Fibre Channel LUNs presented to both nodes, Cluster Shared Volumes, an SMB 3.0 file share, or Storage Spaces Direct (Windows Server 2016 and later). Only the active node mounts a given disk at a time.
  • A clustered role contains the SQL Server service, the Agent service, a network name (the virtual server name clients use), an IP address and the disk resources.
  • Failover is the cluster moving the whole role to another node. SQL Server starts, runs crash recovery on every database, and is back.

There is one copy of the data. Failover is fast because nothing is copied, but a corrupt LUN takes both nodes down with it, so an FCI is always paired with backups and often with an AG or log shipping to a second site.

What to line up first

Two identical Windows Server 2016 nodes, domain-joined, with Failover Clustering installed. Shared storage visible to both: at minimum a data disk, a log disk, a tempdb disk and a small quorum disk or file share witness. Two network adapters per node is traditional but no longer required; one teamed adapter is acceptable. You also need a domain account for the SQL Server service and permission to create computer objects for the network name.

Validate and create the cluster

Install-WindowsFeature Failover-Clustering -IncludeManagementTools
Test-Cluster -Node sqlfci1, sqlfci2 -Include "Storage", "Inventory", "Network", "System Configuration"
New-Cluster -Name SQLFCI-CLU -Node sqlfci1, sqlfci2 -StaticAddress 10.0.0.70
Set-ClusterQuorum -NodeAndFileShareMajority \\fileserver\SQLFCI-Witness

Never skip validation. Microsoft only supports clusters that passed it, and it catches multipath and firmware mismatches that will otherwise appear as random failovers.

Disks

Get-ClusterAvailableDisk | Add-ClusterDisk
Get-ClusterResource | Where-Object ResourceType -eq "Physical Disk" | Format-Table Name, State, OwnerNode

Format the disks with a 64 KB allocation unit for data and log volumes. Rename the cluster disk resources to something meaningful (SQL-Data, SQL-Log, SQL-TempDB) before SQL Server setup. "Cluster Disk 4" at 3 a.m. is no fun.

Installing on the first node

Run setup on the node currently owning the disks and choose "New SQL Server failover cluster installation". The important pages:

  • SQL Server Network Name: the virtual name, for example SQLFCI01. This is what clients connect to.
  • Cluster Disk Selection: pick the disks to include in the role.
  • Cluster Network Configuration: a static IP for the virtual name.
  • Data Directories: put data, log and tempdb on the respective clustered disks. Since SQL Server 2012, tempdb may instead be on local disk on each node, which is good for performance; the same path must exist on every node.

The unattended equivalent uses /ACTION=InstallFailoverCluster with /FAILOVERCLUSTERNETWORKNAME, /FAILOVERCLUSTERIPADDRESSES and /FAILOVERCLUSTERDISKS.

Adding the second node

On the second node, run setup and choose "Add node to a SQL Server failover cluster". Supply the same service account passwords. This installs the binaries locally and adds the node as a possible owner of the role. Patch both nodes with the same cumulative update; since SQL Server 2012, patching is done on the passive node first, then fail over and patch the other.

Failover policy

Get-ClusterGroup "SQL Server (MSSQLSERVER)" | Set-ClusterOwnerNode -Owners sqlfci1, sqlfci2
(Get-ClusterGroup "SQL Server (MSSQLSERVER)").FailoverThreshold = 2
(Get-ClusterGroup "SQL Server (MSSQLSERVER)").FailoverPeriod = 6

Set the health check parameters on the SQL Server resource, HealthCheckTimeout (default 30000 ms) and FailureConditionLevel (default 3), only if you have a specific reason. I leave them alone; the defaults are sensible.

Pull the plug (politely)

Move-ClusterGroup "SQL Server (MSSQLSERVER)" -Node sqlfci2
Get-ClusterGroup "SQL Server (MSSQLSERVER)"

Time how long it takes for the instance to accept connections on the virtual name. Then from SQL Server:

SELECT SERVERPROPERTY('IsClustered') AS clustered, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS active_node;
SELECT NodeName, status_description FROM sys.dm_os_cluster_nodes;

Move the role back and check the Windows cluster log with Get-ClusterLog -Destination C:\Temp -TimeSpan 15 if anything took longer than expected.

Gotchas

Setup fails with "The cluster resource 'SQL Network Name (SQLFCI01)' could not be brought online." The cluster name object lacks permission to create computer objects, or a computer object with that name already exists and is not pre-staged correctly. Fix the OU permission or pre-stage and disable the object.

Unexpected failovers with event 1069 and "The SQL Server service terminated unexpectedly". Look for HealthCheckTimeout being exceeded, often due to storage latency or the node paging heavily. Check sp_server_diagnostics output in the cluster log.

Role fails over but SQL Server will not start on the other node. Common causes: a local path (tempdb, a full-text catalog, a backup device) that does not exist on the second node, or a service account password changed on one node only.

Databases take minutes to come online after failover. Crash recovery on databases with large active transactions. Enable instant file initialisation for the service account and, on 2016 and later, consider indirect checkpoints (TARGET_RECOVERY_TIME = 60 SECONDS, the default for new databases) to shorten redo.

Just remember the storage is the single point you're trusting. Pair an FCI with good backups and a second copy of the data somewhere else.