Data Warehouse Failover Cluster Instance
A classic hot-cold Failover Cluster Instance built on Windows Server Failover Clustering. Two SQL Server nodes share a single storage volume; only the active node serves queries at any time. The passive node takes over automatically on failure but is not accessible for reads or processing while standby. FCI rights are included with SQL Server Enterprise + Software Assurance at no additional license cost.
Recommended Edition
SQL Server Enterprise (with Software Assurance)
Version
2022
Best For
Highly available data warehouse with shared storage
Required Components
- 3 Windows Server machines (Node 1, Node 2, Witness)
- Windows Server Failover Clustering (WSFC) role on all 3 machines
- Shared storage visible to both SQL nodes (SAN, iSCSI, or shared disk)
- SQL Server Enterprise installed on both SQL nodes only
- SSMS
Optional Add-ons
- Dedicated replication NIC for cluster heartbeat
- Storage Spaces Direct (S2D) as shared storage layer
- SQL Server Reporting Services (SSRS)
Hardware / Cloud Recommendations
- 2 identical SQL nodes (64GB+ RAM, NVMe or SAN-attached storage)
- 1 lightweight witness node (can be a VM; no SQL Server required)
- Shared storage with redundant paths (multipath I/O recommended)
- Dedicated private VLAN for cluster heartbeat traffic
Licensing Notes
SQL Server Enterprise with Software Assurance includes Failover Cluster Instance rights. The passive node does not require a separate SQL Server license. Windows Server licensing is required on all 3 machines. SA must remain active to retain passive node rights.
Backup Recommendation
Full and differential backups via SQL Agent to a network share or tape; back up from the active node only. Shared storage is a single point of failure for data. Offsite backup is critical.
Monitoring Recommendation
Failover Cluster Manager for cluster health; SQL Agent alerts for failover events; Extended Events or a third-party tool for query-level monitoring.
SQLDevDBA's Take
FCI is one of the most underappreciated HA options in SQL Server. If you already have Enterprise + SA, the passive node is effectively free. Just go in with eyes open: shared storage means a single point of failure at the storage layer, and the passive node is truly cold: no read workloads, no reporting.
Startup Checklist
- Provision 3 Windows Server machines: 2 SQL nodes and 1 lightweight witness node
- Install the Windows Server Failover Clustering (WSFC) role on all 3 machines and run Validate Cluster
- Configure and present shared storage (SAN, iSCSI, or shared disk) to both SQL nodes, not the witness
- On Node 1, run the SQL Server installer and choose 'New SQL Server failover cluster installation'
- On Node 2, run the SQL Server installer and choose 'Add node to a SQL Server failover cluster'
Security Checklist
- Use domain service accounts (never local accounts) for SQL Server and SQL Agent services
- Restrict cluster heartbeat traffic to a dedicated private VLAN
- Encrypt shared storage volumes with BitLocker or storage-level encryption
- Disable or deny the SA (System Administrator) login
- Configure Windows Firewall rules for SQL Server (TCP 1433) and WSFC traffic (TCP 3343)
- Audit cluster node access and review SQL login permissions regularly
Additional (Optional) Checklist
- Install dbatools PowerShell module (dbatools.io) for automation and migrations
- Install Brent Ozar's First Responder Kit (sp_Blitz, sp_BlitzFirst, sp_BlitzIndex)
- Install Adam Machanic's sp_WhoIsActive for real-time active session monitoring
- Install Erik Darling's performance scripts (sp_PressureDetector, sp_HumanEvents)
- Install Ola Hallengren's Maintenance Solution for backups, integrity checks, and index maintenance
Architecture Diagram
Available in the Pro tier. Visual diagrams for every setup.