Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster

Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster.md

This document targets Windows Server 2022 and SQL Server 2022. See Update Notes at the end of this document. That section lists what changed from the original 2012 version, and the reason for each change.

Table of Contents

Overview

This document covers the SQL Server AlwaysOn feature. This includes the Failover Clustered Instance, the Availability Group, or both. Before you configure AlwaysOn on a Windows Server Failover Cluster (WSFC), complete the following requirements.

Terminology

This section briefly describes the terms used in the rest of this document.

Windows Server Failover Clustering (WSFC)

WSFC is a group of independent Windows Server nodes. These nodes work together to increase the availability of applications and services. WSFC supports multi-site/multi-subnet configurations. In a multi-site/multi-subnet configuration, all nodes must be in the same Active Directory domain.

Note: Since Windows Server 2016, a cluster can also run without a domain-joined Cluster Name Object. This is called an Active Directory-detached cluster. This document assumes the standard domain-joined model. If a detached cluster becomes a requirement, see Configure failover cluster accounts in Active Directory.

Cluster Name Object (CNO)

The CNO is the computer object that Active Directory creates for the WSFC cluster name resource. It is a Kerberos-enabled object. It acts as the cluster's identity and provides its security context. It has full control over every Virtual Computer Object (VCO) associated with the WSFC.

Virtual Computer Object (VCO)

The VCO is the computer object that Active Directory creates for a network name resource. Examples are the SQL Server network name and the SQL Server Availability Group Listener. The WSFC CNO has full control over each associated VCO.

Microsoft does not recommend automatic SPN registration on a clustered SQL Server instance. Re-registration on failover can take longer than SQL Server needs to come online, and this can delay startup. See Register a Service Principal Name for Kerberos Connections for the two supported approaches. Approach one: use a static SQL Server port. Approach two: grant the service account permission to register and unregister its own SPN at every service start.

Symmetric / Asymmetric Storage

Symmetric storage means every node shares the same storage. Asymmetric storage means a subset of nodes shares storage. In some cases, no node shares storage at all. Windows Server has supported both models natively since Windows Server 2012. Windows Server 2022 also supports both models.

SQL Server Failover Clustered Instance (FCI)

SQL Server also calls this an AlwaysOn Failover Clustered Instance. An FCI is a clustered resource. It manages the virtual IP address, network name, symmetric storage, and other resources that SQL Server needs to run. These resources fail over together, as a group, from one WSFC node to another. This provides high availability.

SQL Server Failover Clustered Instance CNAME (FCI CNAME)

The FCI CNAME is a friendly DNS name for users and applications. It points to the SQL Server FCI network name. A DNS CNAME makes it easier to do a SQL Server upgrade, or a similar migration.

SQL Server Availability Group (AG)

SQL Server also calls this an AlwaysOn Availability Group. An AG is a logical group of related SQL Server databases. These databases fail over together, from one SQL Server FCI to another. SQL Server calls each FCI in this role an Availability Replica (AR). We recommend asymmetric storage for an Availability Replica used for disaster recovery.

Replica limits for SQL Server 2022:

  • Enterprise Edition: 1 primary replica plus up to 8 secondary replicas, for 9 total. Up to 5 of the secondary replicas can run in synchronous-commit mode.
  • Standard Edition (Basic Availability Groups): 1 primary and 1 secondary replica, for 2 total. A Basic AG holds a single database only.

Note: a unique SQL Server FCI can host multiple AGs.

SQL Server 2022 introduces a new option: the Contained Availability Group. It replicates the master and msdb system databases to every replica automatically. This includes logins, SQL Agent jobs, and other server-level objects. This removes the need to synchronize these objects across replicas manually. Login behavior differs from a standard AG. Before you choose this option, review What is a Contained Availability Group?.

SQL Server Availability Group Listener (AGL)

The AGL is a friendly network name. It directs incoming client connections to the primary replica, or to a readable-intent secondary replica. For disaster recovery, the AGL needs an IP address assigned in each site/subnet.

SQL Server Availability Group Listener CNAME (AGL CNAME)

The AGL CNAME is a friendly DNS name for users and applications. It points to the specific AGL network name. Some databases stay local to one data center but still need an AG. For those, point the DNS CNAME to a different network name, as part of a disaster recovery plan.

WSFC Requirements

The DBA team works with the Windows team and the Storage team. Together, they request these items for a WSFC (single-site or multi-site/subnet). The cluster needs these items to function.

  • WSFC Domain — The Windows Team supplies the Active Directory domain for the WSFC cluster. Example: TEST
  • WSFC Network Name — The Windows Team supplies a DNS A record for the WSFC cluster network name. Example: MYTESTCNO1
  • WSFC Cluster Name Object (CNO) — The Windows Team supplies the Active Directory CNO object for the WSFC network name. Example: MYTESTCNO1$
  • WSFC Network IP Address — The Windows Team supplies the IP address for the WSFC network name. Note: For a multi-site/subnet WSFC, reserve an IP address for the cluster network name in each site/subnet. Example: 10.10.50.30
  • WSFC Physical Node — The Windows Team supplies a DNS A record for each physical node in the WSFC. Note: For a multi-site/subnet WSFC, at least one physical node must exist in each site/subnet, to run a SQL Server FCI. Example: MYTESTNODE1N1
  • WSFC Physical Node Virtual Computer Object (VCO) — The Windows Team supplies the Active Directory VCO object for each physical node. Note: The VCO object name must match the physical node's network name. Example: MYTESTNODE1N1
  • WSFC Physical Node IP Address — The Windows Team supplies the IP address of each physical node in the WSFC. Example: 10.10.50.31
  • WSFC Quorum Witness — The Windows Team supplies a witness resource for cluster quorum. Note: Use a File Share Witness for a multi-site/subnet WSFC. A Disk Witness needs storage reachable from every node. This conflicts with an asymmetric, multi-site storage layout. If the environment already has Azure Storage access, a Cloud Witness (Azure Storage-backed) is an alternative. See Deploy a quorum witness for a failover cluster. Example: \\myfileserver\WSFCWitness$
  • WSFC Firewall Rules — The Windows Team supplies a list of all firewall ports (TCP/UDP) needed for WSFC and SQL Server functions. Note: See the firewall document for the specific ports.
  • WSFC Microsoft Distributed Transaction Coordinator (MSDTC) Network Name — The Windows Team supplies a DNS A record for the WSFC MSDTC network name. Note: For a multi-site/subnet WSFC, request a unique MSDTC network name. Example: MYTESTMSDTC01
  • WSFC MSDTC VCO — The Windows Team supplies the Active Directory VCO object for the WSFC MSDTC. Note: The VCO object name must match the MSDTC network name. Example: MYTESTMSDTC01
  • WSFC MSDTC IP Address — The Windows Team supplies the IP address for the cluster MSDTC. Note: For a multi-site/subnet WSFC, reserve an IP address for the WSFC MSDTC in each site/subnet. Example: 10.10.50.35
  • Cluster MSDTC Storage — The Windows Team and the Storage Team supply the WSFC storage and mount point required to install a WSFC MSDTC. Note: For a multi-site/subnet WSFC, request a unique storage and mount point.
Mount Point Size (GB)
Z:\ 1

SQL Server FCI Requirements

The DBA team works with the Windows team and the Storage team. Together, they request these items for a SQL Server FCI to install and maintain on a WSFC.

  • DBA Team Security Group — The Windows Team supplies an Active Directory Security Group. This group contains the elevated Active Directory security accounts for all DBA team members in the domain. Note: Add this group with elevated rights (sysadmin) during the SQL Server FCI install. Example: TEST\G-SQLAdmins
  • SQL Server Security Group — The Windows Team supplies an Active Directory Security Group. This group contains the SQL Server service accounts for the domain. Note: Add this group with elevated rights (sysadmin) during the SQL Server FCI install. Example: TEST\G-SQLServiceAccounts
  • SQL Server Service Account — The Windows Team supplies an Active Directory service account. This account owns and operates (starts and stops) all SQL Server FCIs on a WSFC, in each site/subnet. Add this account to the SQL Server Security Group. Note: Add this account with elevated rights (sysadmin) during the SQL Server FCI install. Consider a group Managed Service Account (gMSA) instead of a standard service account. A gMSA removes manual password rotation. SQL Server has supported gMSAs for clustered instances and Availability Groups since SQL Server 2014, on Windows Server 2012 R2 and later. Example: TEST\S-MYTESTSQLSVC01
  • SQL Server Service Password — The Windows Team supplies the password for the service account above. Note: For security reasons, retrieve this password from a secure source, for example a password vault. If you use a gMSA, this item is not necessary. Example: ########
  • SQL Server FCI Network Name — The Windows Team supplies a DNS A record for the SQL Server FCI network name. Note: This name must be unique within the WSFC. Example: MYTESTDBVSQL01
  • SQL Server FCI VCO — The Windows Team supplies the Active Directory VCO object for the SQL Server FCI network name. Note: This VCO object name must match the SQL Server FCI network name. Example: MYTESTDBVSQL01
  • SQL Server FCI IP Address — The Windows Team supplies the IP address of the SQL Server FCI network name. Example: 10.10.50.38
  • SQL Server FCI CNAME — The Windows Team supplies a DNS CNAME record. This record points to the SQL Server FCI network name. Example: MYTESTDB01
  • SQL Server FCI Storage — The Windows Team and the Storage Team supply the WSFC storage and mount points required to install a SQL Server FCI. Each request includes a root drive letter, plus 2 data volumes, 1 log volume, and 1 tempdb volume, as mount points under that root drive letter. Note: All requests start with drive letter G. Requests can use any drive except C-E, W (Witness), and Z (MSDTC).
Mount Point Size (GB)
G:\ 1
G:\Admin 25
G:\Data1 50
G:\Data2 50
G:\Logs 100
G:\TempDB 50

SQL Server Availability Group Requirements

The DBA team works with the Windows team. Together, they request these items for a SQL Server Availability Group configuration on a WSFC.

Note: SQL Server Database Mirroring has a similar restriction. It still applies today: the path of the data and log files must be the same on every replica. This still applies to the replicated system databases, even if the deployment uses a Contained Availability Group.

  • SQL Server Availability Group — The DBA Team supplies the SQL Server Availability Group name. SQL Server uses this name internally only. Example: AG_MYTEST
  • SQL Server Availability Group Listener Network Name — The Windows Team supplies a DNS A record for the SQL Server Availability Group Listener network name. Note: This name must be unique within the WSFC. Example: MYTESTVAG
  • SQL Server Availability Group Listener VCO — The Windows Team supplies the Active Directory VCO object for the Availability Group Listener network name. Note: This VCO object name must match the Availability Group Listener network name. Example: MYTESTVAG
  • SQL Server Availability Group Listener IP Address — The Windows Team supplies the IP address of the Availability Group Listener network name. Note: For a multi-site/subnet configuration, assign an IP address in every site/subnet. Example: 10.10.50.58, 10.20.50.58
  • SQL Server Availability Group Listener CNAME — The Windows Team supplies a DNS CNAME record. This record points to the Availability Group Listener network name. Example: SQLMYTESTVAG

Update Notes

This section lists what changed in this update, and why. The original document targeted SQL Server 2012 and Windows Server 2012. This version targets Windows Server 2022 and SQL Server 2022.

Item Original Updated Reason
AG replica limit Maximum 5 replica members (1 primary, 4 secondary) Enterprise Edition: 9 total (1 primary, up to 8 secondary, up to 5 synchronous). Standard Edition (Basic AG): 2 total The 5-replica limit applied to SQL Server 2012 only. SQL Server 2014 raised the secondary count to 8. SQL Server 2019 raised the synchronous-replica cap to 5. Basic AGs (Standard Edition, 2016 and later) stay capped at 2 total.
Contained Availability Groups Not present Added as a noted SQL Server 2022 option New in SQL Server 2022. It changes login and permission handling. For this reason, it needs its own decision, not automatic adoption.
WSFC Quorum Witness Not listed as a requirement item; only implied by a reserved W drive letter Added as an explicit requirement item, recommending a File Share Witness over a Disk Witness for multi-site/subnet clusters A Disk Witness needs storage reachable from every node. That conflicts with the asymmetric, multi-site storage model this document already describes. Dynamic quorum management (Windows Server 2012 R2 and later) still benefits from an explicit witness.
SQL Server Service Account Standard Active Directory service account only Added a note recommending a group Managed Service Account (gMSA) A gMSA removes manual password rotation and storage. SQL Server has supported gMSAs for clustered service accounts since SQL Server 2014 and Windows Server 2012 R2. Windows Server 2022 continues this support.
Reference links msdn.microsoft.com and technet.microsoft.com links Replaced with current learn.microsoft.com equivalents The original MSDN and TechNet domains no longer host these pages.
Active Directory domain requirement Stated as an unconditional requirement Added a note on Active Directory-detached clusters (Windows Server 2016 and later) This is an added option, not a correction. Most deployments, including a standard domain-joined CNO, still work as originally described.
Version framing "WSFC 2012," "SQL Server 2012 AlwaysOn" throughout Removed version-specific framing where the underlying behavior is now standard Multi-subnet clustering and symmetric/asymmetric storage have been the default behavior since Windows Server 2012. They no longer need a caveat as new features.

Windows Server 2025 / SQL Server 2025 Notes

This document targets Windows Server 2022 and SQL Server 2022. This section notes what changes if a project moves to Windows Server 2025 and SQL Server 2025. This document does not apply any of these items to the requirements above. Review them. If the project adopts one of these items, update the relevant section.

Item What changes in the 2025 versions Applies to Notes
Workgroup Clusters (domain-independent AG) A SQL Server Availability Group can run in a Windows Server 2025 workgroup cluster, without a domain-joined CNO. This needs a domain-independent AG configuration. AG only Windows Server 2025 still does not support SQL Server FCI in a workgroup cluster. The FCI section of this document still needs a domain-joined CNO.
Mixed storage in one cluster Windows Server 2025 lets Storage Spaces Direct (S2D) volumes and traditional SAN (Fibre Channel/iSCSI) LUNs coexist as Cluster Shared Volumes in the same cluster. WSFC storage design Windows Server 2022 requires one storage model per cluster. This removes that restriction.
Campus Clusters A new stretched-cluster topology, for sites within metro distance. Multi-site/subnet topology Campus Clusters require Windows Server 2025, with the December 2025 cumulative update (KB5072033) installed.
Cluster OS Rolling Upgrade Confirmed still supported from Windows Server 2022 to 2025, with no cluster downtime. Cluster upgrade path This is not a new capability. It confirms that the existing upgrade path still works for this cluster design.
TDS 8.0 with enforced TLS 1.3 SQL Server 2025 can require strict TLS 1.3 encryption on AG replica and listener connections. AG security posture This changes encryption requirements, not port numbers. If the project adopts this, coordinate with the firewall document.
Standard Edition capacity SQL Server 2025 Standard Edition scales to 32 cores and 256 GB RAM, up from 24 cores and 128 GB. Licensing/sizing decisions Basic Availability Groups on Standard Edition become viable for larger workloads. This can change whether a project needs Enterprise Edition.
AG replica limit Unchanged: still 9 total (1 primary, up to 8 secondary, up to 5 synchronous) for Enterprise Edition. AG replica planning If a project moves from SQL Server 2022 to 2025, this document needs no update.
Operational improvements Faster failover, better cluster failover diagnostics, a persistent health model, configurable AG Commit Time (previously hardcoded at 10ms), backups on secondary replicas, and removing a listener IP without deleting the listener. DBA operations These are operational/DBA-facing capabilities, not provisioning requirements. They do not change any item the Windows or Storage team needs to supply.

Comments

Post a Comment

Popular posts from this blog

Windows Server Failover Clustering/SQL Server Firewall Access Rules

This document defines the firewall rules required for a Multi-Subnet Windows Server Failover Cluster (WSFC) environment. It covers WSFC core communication, the WSFC dependency on Active Directory (AD), and SQL Server Availability Group (AG) communication. Use these rules when you request network access for a Multi-Subnet Cluster. This version corrects several rows to agree with current Microsoft documentation. See Verification Notes at the end of this document for the list of changes and sources. Table of Contents Best Practice: IP Specificity Critical Action: Review Ephemeral Port Ranges Section 1: Core WSFC and Active Directory Ports Section 2: SQL Server Database Engine and Availability Group Ports Section 3: SQL Server BI Stack Ports (Conditional) Section 4: Multi-Subnet / Cloud Deployment Ports (Conditional) Section 5: Official References Verification Notes Windows Server 2025 / SQL Server 2025 Notes Best Practice: IP Specificity Specify exact IP ad...
Read more

Windows Server Recommendations For AWS

Windows Server Recommendations for AWS.md Windows Server Recommendations for AWS Overview This document gives a high-level recommendation for building a Windows Server EC2 host in AWS. It also covers support for Windows Server Failover Clustering (WSFC). Table of Contents Table of Contents Overview EC2 Host Recommendations EC2 Network Recommendations EC2 Security Recommendations Security Group 1: Internal Region Traffic Security Group 2: Inbound Windows and SQL Server Access Security Group 3: Outbound Access Security Group 4: Cross-Region WSFC and AG Traffic EC2 O/S Recommendations Operating System Image Network Configuration Drive Configuration Patching and Drivers Backups EC2 Host Recommendations This section gives recommendations for an EC2 host in AWS. It covers instance type and storage. Use the r6idn or i3en instance type for a SQL Server host in AWS. Both instance types come with NVMe SSD storage. Use the NVMe SSD storage for te...
Read more