Prerequisites, restrictions, and recommendations for Always On availability groups

Applies to: SQL Server

This article describes considerations for deploying Always On availability groups, including prerequisites, restrictions, and recommendations for host computers, Windows Server failover clusters (WSFC), server instances, and availability groups. For each of these components security considerations and required permissions, if any, are indicated.

Important

Before you deploy Always On availability groups, we strongly recommend that you read every section of this topic.

.NET hotfixes that support availability groups

Depending on the SQL Server components and features you'll use with Always On availability groups, you might need to install additional .NET hotfixes identified in the following table. The hotfixes can be installed in any order.

Dependent Feature	Hotfix	Link
Reporting Services	Hotfix for .NET 3.5 SP1 adds support to SQL Client for Always On features of Read-intent, readonly, and multisubnetfailover. The hotfix needs to be installed on each Reporting Services report server.	KB 2654347: Hotfix for .NET 3.5 SP1 to add support for Always On features

Checklist: Requirements (Windows system)

To support the Always On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets the following fundamental requirements:

Requirement	Link
Ensure that the system isn't a domain controller.	Availability groups aren't supported on domain controllers.
Ensure that each computer is running on a supported Windows Server version	Hardware and software requirements for: - SQL Server 2022 - SQL Server 2019 - SQL Server 2017 - SQL Server 2016
Ensure that each computer is a node in a WSFC.	Windows Server Failover Clustering with SQL Server
Ensure that the WSFC contains sufficient nodes to support your availability group configurations.	A cluster node can host one replica for an availability group. The same node can't host two replicas from the same availability group. The cluster node can participate in multiple availability groups, with one replica from each group. Ask your database administrators how many cluster nodes are required for to support the availability replicas of the planned availability groups. What is an Always On availability group?.

Important

Also ensure that your environment is correctly configured for connecting to an availability group. For more information, see Driver and client connectivity support for availability groups.

Recommendations for computers that host availability replicas (Windows system)

• Comparable systems: For a given availability group, all the availability replicas should run on comparable systems that can handle identical workloads.

• Dedicated network adapters: For best performance, use a dedicated network adapter (network interface card) for Always On availability groups.

• Sufficient disk space: Every computer on which a server instance hosts an availability replica must possess sufficient disk space for all the databases in the availability group. Keep in mind that as primary databases grow, their corresponding secondary databases grow the same amount.

• Identical disk layout: Every computer on which a server instance hosts an availability replica should have an identical disk layout (with exact disk drive letters and sizes) to ensure file paths for database files (mdf, ldf) are mirrored, preventing complications during seeding and synchronization. Review Restrictions (availability databases) for disk layouts that differ.

Permissions (Windows system)

To administer a WSFC, the user must be a system administrator on every cluster node.

For more information about the account for administering the cluster, see Appendix A: Failover Cluster Requirements.

Related tasks (Windows system)

Task	Link
Set the HostRecordTTL value.	Change the HostRecordTTL (Using Windows PowerShell)

Change the HostRecordTTL (using PowerShell)

1. Open PowerShell window via Run as Administrator.

2. Import the FailoverClusters module.

3. Use the Get-ClusterResource cmdlet to find the Network Name resource, then use Set-ClusterParameter cmdlet to set the HostRecordTTL value, as follows:

   Get-ClusterResource "<NetworkResourceName>" | Set-ClusterParameter HostRecordTTL <TimeInSeconds>

   The following PowerShell example sets the HostRecordTTL to 300 seconds for a Network Name resource named SQL Network Name (SQL35).

   Import-Module FailoverClusters
   
   $nameResource = "SQL Network Name (SQL35)"
   Get-ClusterResource $nameResource | Set-ClusterParameter HostRecordTTL 300
   

   Tip

   Every time you open a new PowerShell window, you need to import the FailoverClusters module.

Related content (PowerShell)

• Clustering and High-Availability (Failover Clustering and Network Load Balancing Team Blog)

• Getting Started with Windows PowerShell on a Failover Cluster

• Cluster resource commands and equivalent Windows PowerShell cmdlets

Related content (Windows system)

• Configure DNS settings in a Multi-Site Failover Cluster

• DNS Registration with Network Name Resource

SQL Server instance prerequisites and restrictions

Each availability group requires a set of failover partners, known as availability replicas, which are hosted by instances of SQL Server. A given server instance can be a stand-alone instance or a SQL Server failover cluster instance (FCI).

In this section:

• Checklist: Prerequisites
• Thread Usage by availability groups
• Permissions
• Related Tasks
• Related Content

Checklist: Prerequisites (server instance)

Prerequisite	Links
The host computer must be a WSFC node. The instances of SQL Server that host availability replicas for a given availability group reside on separate nodes of the cluster. An availability group can temporarily straddle two clusters while being migrated to different cluster. SQL Server 2016 (13.x) introduced distributed availability groups. In a distributed availability group, two availability groups reside on different clusters.	Windows Server Failover Clustering with SQL Server Failover Clustering and Always On Availability Groups (SQL Server) Distributed availability groups
If you want an availability group to work with Kerberos: All server instances that host an availability replica for the availability group must use the same SQL Server service account. The domain administrator needs to manually register a Service Principal Name (SPN) with Active Directory on the SQL Server service account for the virtual network name (VNN) of the availability group listener. If the SPN is registered on an account other than the SQL Server service account, authentication fails. To use Kerberos authentication for the communication between availability group (AG) endpoints, manually register SPNs for the database mirroring endpoints used by the AG. Important: If you change the SQL Server service account, the domain administrator needs to manually re-register the SPN.	Register a Service Principal Name for Kerberos connections Note: Kerberos and SPNs enforce mutual authentication. The SPN maps to the Windows account that starts the SQL Server services. If the SPN isn't registered correctly or if it fails, the Windows security layer can't determine the account associated with the SPN, and Kerberos authentication can't be used. Note: NTLM doesn't have this requirement.
If you plan to use a SQL Server failover cluster instance (FCI) to host an availability replica, ensure that you understand the FCI restrictions and that the FCI requirements are met.	Prerequisites and Requirements on Using a SQL Server Failover Cluster Instance (FCI) to Host an Availability Replica (later in this article)
Each server instance must be running the same version of SQL Server to participate in an availability group.	For more information, see the list of editions and supported features at the end of this section.
All the server instances that host availability replicas for an availability group must use the same SQL Server collation.	Set or change the server collation
Enable the Always On availability groups feature on each server instance that will host an availability replica for any availability group. On a given computer, you can enable as many server instances for Always On availability groups as your SQL Server installation supports.	Enable or disable Always On availability group feature Important: If you destroy and re-create a WSFC, you must disable and re-enable the Always On availability groups feature on each server instance that was enabled for Always On availability groups on the original cluster.
Each server instance requires a database mirroring endpoint. This endpoint is shared by all the availability replicas and database mirroring partners and witnesses on the server instance. If a server instance that you select to host an availability replica is running under a domain user account and doesn't yet have a database mirroring endpoint, the Use the Availability Group Wizard (SQL Server Management Studio) (or Add a replica to your Always On Availability group using the Availability Group Wizard in SQL Server Management) can create the endpoint and grant CONNECT permission to the server instance service account. However, if the SQL Server service is running as a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication, and the wizard is unable to create a database mirroring endpoint on the server instance. In this case, we recommend that you create the database mirroring endpoints manually before you launch the wizard. Security note: Transport security for Always On availability groups is the same as for database mirroring.	The Database Mirroring Endpoint (SQL Server) Transport Security - Database Mirroring - Always On Availability
If any databases that use FILESTREAM are added to an availability group, ensure that FILESTREAM is enabled on every server instance that will host an availability replica for the availability group.	Enable and configure FILESTREAM
If any contained databases are added to an availability group, ensure that the contained database authentication (server configuration option) is set to 1 on every server instance that hosts an availability replica for the availability group.	contained database authentication Server Configuration Option Server configuration options (SQL Server)

For a list of features supported by the editions of SQL Server on Windows, see:

• Editions and supported features of SQL Server 2022
• Editions and supported features of SQL Server 2019
• Editions and supported features of SQL Server 2017
• Editions and supported features of SQL Server 2016

Thread usage by availability groups

Always On availability groups has the following requirements for worker threads:

• On an idle instance of SQL Server, Always On availability groups uses 0 threads.

• The maximum number of threads used by availability groups is the configured setting for the maximum number of server threads ('max worker threads') minus 40.

• The availability replicas hosted on a given server instance share a single thread pool in SQL Server 2019 (15.x) and previous versions.

  Threads are shared on an on-demand basis, as follows:

  • Typically, there are 3-10 shared threads, but this number can increase depending on the primary replica workload.

  • If a given thread is idle for a while, it's released back into the general SQL Server thread pool. Normally, an inactive thread is released after ~15 seconds of inactivity. However, depending on the last activity, an idle thread might be retained longer.

  • A SQL Server instance uses up to 100 threads for parallel redo for secondary replicas. Each database uses up to one-half of the total number of CPU cores, but not more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a single redo thread for every remaining database. Serial redo threads are released after approximately 15 seconds of inactivity.

• In addition, availability groups use unshared threads, as follows:

  • Each primary replica uses 1 Log Capture thread for each primary database. In addition, it uses 1 Log Send thread for each secondary database. Log send threads are released after ~15 seconds of inactivity.

  • A backup on a secondary replica holds a thread on the primary replica for the duration of the backup operation.

• SQL Server 2022 (16.x) introduced the parallel redo thread pool, which is an instance-level thread pool shared with all databases having redo work. With this pool, the same set of threads can process the log records for different databases at the same time (in parallel). In SQL Server 2019 (15.x) and previous versions, the number of available threads for redo is limited to 100.

• SQL Server 2019 (15.x) introduced parallel redo for memory optimized availability group databases. In SQL Server 2016 (13.x) and SQL Server 2017 (14.x), disk-based tables don't use parallel redo if a database in an availability group is also memory optimized.

For more information, see Always On - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases (a CSS SQL Server Engineers Blog).

Permissions (server instance)

Task	Required Permissions
Creating the database mirroring endpoint	Requires CREATE ENDPOINT permission, or membership in the sysadmin fixed server role. Also requires CONTROL ON ENDPOINT permission. For more information, see GRANT Endpoint Permissions (Transact-SQL).
Enabling Always On availability groups	Requires membership in the Administrator group on the local computer and full control on the WSFC.

Related tasks (server instance)

Task	Article
Determining whether database mirroring endpoint exists	sys.database_mirroring_endpoints (Transact-SQL)
Creating the database mirroring endpoint (if it doesn't yet exist)	Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL) Use Certificates for a Database Mirroring Endpoint (Transact-SQL) Create a database mirroring endpoint for an availability group using PowerShell
Enabling availability groups	Enable or disable Always On availability group feature

Related content (server instance)

• Always On - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases

Network connectivity recommendations

We strongly recommend that you use the same network links for communications between WSFC nodes and communications between availability replicas. Using separate network links can cause unexpected behaviors if some of links fail (even intermittently).

For example, for an availability group to support automatic failover, the secondary replica that is the automatic-failover partner must be in the SYNCHRONIZED state. If the network link to this secondary replica fails (even intermittently), the replica enters the UNSYNCHRONIZED state and can't begin to resynchronize until the link is restored. If the WSFC requests an automatic failover while the secondary replica is unsynchronized, automatic failover doesn't occur.

Client connectivity support

For information about Always On availability groups support for client connectivity, see Driver and client connectivity support for availability groups.

Prerequisites and restrictions for using a SQL Server failover cluster instance (FCI) to host an availability replica

In this section:

• Restrictions
• Checklist: Prerequisites
• Related Tasks
• Related Content

Restrictions (FCIs)

Note

Failover cluster instances (FCIs) support clustered shared volumes (CSV). For more information on CSV, see Understanding Cluster Shared Volumes in a Failover Cluster.

• The cluster nodes of an FCI can host only one replica for a given availability group: If you add an availability replica on an FCI, the WSFC nodes that are possible FCI owners can't host another replica for the same availability group. To avoid possible conflicts, it's recommended to configure possible owners for the failover cluster instance. This prevents potentially causing a single WSFC from attempting to host two availability replicas for the same availability group.

  Furthermore, every other replica must be hosted by an instance of SQL Server that resides on a different cluster node in the same Windows Server failover cluster. The only exception is that while being migrated to another cluster, an availability group can temporarily straddle two clusters.

  Warning

  Using the Failover Cluster Manager to move an FCI hosting an availability group to a node that is already hosting a replica of the same availability group might result in the loss of the availability group replica, preventing it from being brought online on the target node. A single node of a failover cluster can't host more than one replica for the same availability group. For more information on how this occurs, and how to recover, see the blog Replica unexpectedly dropped in availability group.

• FCIs don't support automatic failover by availability groups: FCIs don't support automatic failover by availability groups, so any availability replica that is hosted by an FCI can be configured for manual failover only.

• Changing FCI network name: If you need to change the network name of an FCI that hosts an availability replica, you'll need to remove the replica from its availability group and then add the replica back into the availability group. You can't remove the primary replica, so if you're renaming an FCI that is hosting the primary replica, you should fail over to a secondary replica and then remove the former primary replica and add it back. Renaming an FCI might alter the URL of its database mirroring endpoint. When you add the replica ensure that you specify the current endpoint URL.

Checklist: Prerequisites (FCIs)

Prerequisite	Link
Ensure that each SQL Server failover cluster instance (FCI) possesses the required shared storage as per standard SQL Server failover cluster instance installation.

Related tasks (FCIs)

Task	Article
Installing a SQL Server FCI	Create a New Always On Failover Cluster Instance (Setup)
In-place upgrade of your existing SQL Server FCI	Upgrade a failover cluster instance
Maintaining your existing SQL Server FCI	Add or remove nodes in a failover cluster instance (Setup)

Related content (FCIs)

• Failover Clustering and Always On Availability Groups (SQL Server)

• Always On Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and availability groups

Availability group prerequisites and restrictions

In this section:

• Restrictions
• Requirements
• Security
• Related Tasks

Restrictions (availability groups)

• Availability replicas must be hosted by different nodes of one WSFC: For a given availability group, availability replicas must be hosted by server instances running on different nodes of the same WSFC. The only exception is that while being migrated to another cluster, an availability group can temporarily straddle two clusters.

  Note

  Virtual machines on the same physical computer can each host an availability replica for the same availability group because each virtual machine acts as a separate computer.

• Unique availability group name: Each availability group name must be unique on the WSFC. The maximum length for an availability group name is 128 characters.

• Availability replicas: Each availability group supports one primary replica and up to eight secondary replicas. All of the replicas can run under asynchronous-commit mode, or up to five of them can run under synchronous-commit mode (one primary replica with two synchronous secondary replicas). Each replica must have a unique server name in both Windows and SQL Server. The server names between Windows and SQL Server must match.

• Maximum number of availability groups and availability databases per computer: The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there's no enforced limit. Microsoft has tested up to 10 AGs and 100 DBs per physical machine, however this isn't a binding limit. Depending on the hardware specification on the server and the workload, you can put a higher number of databases and availability groups on an instance of SQL Server. Signs of overloaded systems can include, but aren't limited to, worker thread exhaustion, slow response times for availability group system views and DMVs, and/or stalled dispatcher system dumps. Please make sure to thoroughly test your environment with a production-like workload to ensure it can handle peak workload capacity within your application SLAs. When considering SLAs be sure to consider load under failure conditions as well as expected response times.

• Don't use the Failover Cluster Manager to manipulate availability groups. The state of a SQL Server FCI is shared between SQL Server and the Windows Server Failover Cluster (WSFC), with SQL Server keeping more detailed state information about the instances than the cluster cares about. The management model is that SQL Server must drive the transactions, and is responsible for keeping the cluster's view of the state in sync with SQL Server's view of state. If the state of the cluster is changed outside of SQL Server it's possible for the state to get out of sync between WSFC and SQL Server, which might lead to unpredictable behavior.

  For example:

  • Don't change any availability group properties, such as the possible owners.

  • Don't use the Failover Cluster Manager to fail over availability groups. You must use Transact-SQL or SQL Server Management Studio.

• Don't add resources or alter dependencies associated to availability group role. We don't recommend placing any additional resources (including user or third-party) into the availability group role or altering the role dependencies as these changes can negatively impact failover performance.

Prerequisites (availability groups)

When creating or reconfiguring an availability group configuration, ensure that you adhere to the following requirements.

Prerequisite	Description
If you plan to use a SQL Server failover cluster instance (FCI) to host an availability replica, ensure that you understand the FCI restrictions and that the FCI requirements are met.	Prerequisites and restrictions for using a SQL Server failover cluster instance (FCI) to host an availability replica (earlier in this article)

Security (availability groups)

• Security is inherited from the WSFC. Windows Server failover clustering provides two levels of user security at the granularity of entire cluster:

  • Read-only access

  • Full control

    Always On availability groups need full control, and enabling Always On availability groups on an instance of SQL Server gives it full control of the cluster (through Service SID).

    You can't directly add or remove security for a server instance in Cluster Manager. To manage cluster security sessions, use the SQL Server Configuration Manager or the WMI equivalent from SQL Server.

• Each instance of SQL Server must have permissions to access the registry, cluster, and so forth.

• We recommend that you use encryption for connections between server instances that host Always On availability groups availability replicas.

Permissions (availability groups)

Task	Required Permissions
Creating an availability group	Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
Altering an availability group	Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission. In addition, joining a database to an availability group requires membership in the db_owner fixed database role.
Dropping/deleting an availability group	Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission. To drop an availability group that isn't hosted on the local replica location you need CONTROL SERVER permission or CONTROL permission on that availability group.

Related tasks (availability groups)

Task	Article
Creating an availability group	Use the Availability Group Wizard (SQL Server Management Studio) Create an Always On availability group using Transact-SQL (T-SQL) Create an Always On availability group using PowerShell Specify Endpoint URL - Adding or Modifying Availability Replica
Modifying the number of availability replicas	Add a secondary replica to an Always On Availability Group Join a secondary replica to an Always On availability group Remove a Secondary Replica from an Availability Group (SQL Server)
Creating an availability group listener	Configure a listener for an Always On availability group
Dropping an availability group	Remove an availability group (SQL Server)

Availability database prerequisites and restrictions

To be eligible to be added to an availability group, a database must meet the following prerequisites and restrictions.

In this section:

• Requirements
• Restrictions
• Recommendations for computers that host availability replicas (Windows system
• Permissions
• Related Tasks

Checklist: Requirements (availability databases)

To be eligible to be added to an availability group, a database must:

Requirements	Link
Be a user database. System databases can't belong to an availability group.
Reside on the instance of SQL Server where you create the availability group and be accessible to the server instance.
Be a read-write database. Read-only databases can't be added to an availability group.	sys.databases (is_read_only = 0)
Be a multi-user database.	sys.databases (user_access = 0)
Not use AUTO_CLOSE.	sys.databases (is_auto_close_on = 0)
Use the full recovery model.	sys.databases (recovery_model = 1)
Possess at least one full database backup. Note: After setting a database to full recovery model, a full backup is required to initiate the full-recovery log chain.	Create a Full Database Backup
Not belong to any existing availability group.	sys.databases (group_database_id = NULL)
Not be configured for database mirroring.	sys.database_mirroring (If the database doesn't participate in mirroring, all columns prefixed with "mirroring_" are NULL.)
Before adding a database that uses FILESTREAM to an availability group, ensure that FILESTREAM is enabled on every server instance that hosts or will host an availability replica for the availability group.	Enable and configure FILESTREAM
Before adding a contained database to an availability group, ensure that the contained database authentication server option is set to 1 on every server instance that hosts or will host an availability replica for the availability group.	contained database authentication Server Configuration Option Server configuration options (SQL Server)

Note

Always On availability groups works with any supported database compatibility level.

Restrictions (availability databases)

• If the file path (including the drive letter) of a secondary database differs from the path of the corresponding primary database, the following restrictions apply:

  • New Availability Group Wizard/Add Database to Availability Group Wizard: The Full option isn't supported (on the Select Initial Data Synchronization Page (Always On Availability Group Wizards) page),

  • RESTORE WITH MOVE: To create the secondary databases, the database files must be RESTORED WITH MOVE on each instance of SQL Server that hosts a secondary replica.

  • Impact on add-file operations: A later add-file operation on the primary replica might fail on the secondary databases. This failure could cause the secondary databases to be suspended. This, in turn, causes the secondary replicas to enter the NOT SYNCHRONIZING state.

    Note

    For information about responding to a failed ad-file operation, see Troubleshoot a Failed Add-File Operation (Always On Availability Groups).

• You can't drop a database that currently belongs to an availability group.

Follow up for TDE protected databases

If you use transparent data encryption (TDE), the certificate or asymmetric key for creating and decrypting other keys must be the same on every server instance that hosts an availability replica for the availability group. For more information, see Move a TDE Protected Database to Another SQL Server.

Permissions (availability databases)

Requires ALTER permission on the database.

Related tasks (availability databases)

Task	Article
Preparing a secondary database (manually)	Prepare a secondary database for an Always On availability group
Joining a secondary database to availability group (manually)	Join a secondary database to an Always On availability group
Modifying the number of availability databases	Add a Database to an Always On availability group Remove a Secondary Database from an Availability Group (SQL Server) Remove a primary database from an Always On availability group

Related content

• Microsoft SQL Server Always On Solutions Guide for High Availability and Disaster Recovery
• SQL Server Always On Team Blog: The official SQL Server Always On Team Blog
• Always On - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases
• What is an Always On availability group?
• Failover Clustering and Always On Availability Groups (SQL Server)
• Driver and client connectivity support for availability groups