SQL Server Availability Group Limitations

This post lists the various SQL Server Availability Group limitations that must be considered before deployments.

SQL Server’s Always On Availability Groups is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. However, like any technology, it comes with its own set of prerequisites, restrictions, and recommendations.

Table of Contents

SQL Server Availability Group Limitations: Table

Here’s a detailed table summarizing the restrictions associated with SQL Server Availability Groups:

Category	Restriction	Description
General Restrictions	Backup on Secondary Replicas	Secondary replica backup priority must be set to 50 or higher.
	Cross-database Transactions	Not supported for databases in availability groups.
	Database Mirroring	Cannot be combined with Always On availability groups.
	Database Ownership	SQL Server instance service account must have necessary permissions.
	Distributed Transactions	Not supported for databases in availability groups.
	Encrypted Databases	Server certificate must be the same on every server instance for TDE.
	Filestream	Supported on readable secondary replicas with limitations.
	gMSA	Supported with some restrictions.
	Linked Servers	Must start MSDTC service on all replicas.
	SQL Server Agent Jobs	Jobs might need to be scripted and created on every server hosting a replica.
	SQL Server FCIs	Supported with some restrictions.
	Unsupported SQL Server Features	Includes stretch databases, system-versioned temporal tables, etc.
Target Replica Restrictions	Backup	System databases cannot be backed up on secondary replicas.
	Database Snapshots	Not supported on secondary replicas.
	DBCC	Some DBCC commands are not supported on secondary replicas.
	Direct Updates	Direct updates to system tables not supported on secondary replicas.
Client Connection Restrictions	Read-Only Access	Clients must use ApplicationIntent=ReadOnly for read-only access to a secondary replica.
Availability Database Restrictions	Adding/Removing Database Files	Must take log backups and restore them on every secondary database.
	Changing Database Options	Some changes to database options are not supported.
	Changing the Recovery Model	Changing recovery model of a database in an availability group is not supported.
	Database Collation	Must be the same on every server instance hosting a replica.
	Database Compatibility Level	Must be set to 110 or higher.
	Database Filegroups and Files	All filegroups must be online.
	Database Size	~~Maximum size is 10TB.~~
	Full-Text Indexes	Must be unique across replicas.
Availability Group Restrictions	Availability Group with No Databases	Not supported.
	Availability Group Listener	Can have multiple IP addresses but only one DNS name.
	Maximum Number of Replicas	Supports one primary replica and up to eight secondary replicas.
	Network Name	Must be unique in the domain and in NetBIOS.

SQL Server Availability Group Limitations

This blog post will delve into the restrictions associated with SQL Server Availability Groups, as sourced from Microsoft’s official documentation.

1. General Restrictions

Backup on Secondary Replicas: If you’re using a secondary replica to create backups, the backup priority of the secondary replica must be set to 50 or higher.
Cross-database Transactions: Cross-database transactions are not supported for databases that are part of an availability group. If a database is part of an availability group, it cannot be a publishing database for transactional replication.
Database Mirroring: You cannot combine database mirroring with Always On availability groups.
Database Ownership: The SQL Server instance service account must have the necessary permissions. If the service account lacks the required permissions, you might encounter issues.
Distributed Transactions: Distributed transactions are not supported for databases in availability groups.
Encrypted Databases: If you’re using Transparent Data Encryption (TDE), the server certificate must be the same on every server instance that hosts an availability replica of the database.
Filestream: FILESTREAM filegroup, FILETABLE, and the Semantic Search Full-Text feature are supported on readable secondary replicas, but there are some limitations.
Group Managed Service Accounts (gMSA): gMSA is supported but with some restrictions.
Linked Servers: If you’re using linked servers, you must start the distributed transaction coordinator (MSDTC) service on all replicas.
SQL Server Agent Jobs: SQL Server Agent jobs might need to be scripted and created on every server that hosts an availability replica.
SQL Server Failover Cluster Instances (FCIs): FCIs are supported but have some restrictions.
SQL Server Features Not Supported by Always On Availability Groups: Some SQL Server features are not supported, including stretch databases, system-versioned temporal tables, and more.

2. Restrictions on the Target Replica

Backup: You cannot back up system databases on secondary replicas.
Database Snapshots: Database snapshots are not supported on secondary replicas.
DBCC: Some DBCC commands are not supported on secondary replicas.
Direct Updates: Direct updates to the system tables are not supported on secondary replicas.

3. Restrictions for Client Connections

Read-Only Access: For read-only access to a secondary replica, clients must use either the ApplicationIntent=ReadOnly connection string keyword or the corresponding application intent connection property of SqlConnection.

4. Restrictions on Availability Databases

Adding or Removing Database Files: If you add or remove files from a primary database, you must take backups of the log and restore them on every secondary database.
Changing Database Options: Some changes to database options are not supported.
Changing the Recovery Model: Changing the recovery model of a database in an availability group is not supported.
Database Collation: The collation of a database in an availability group must be the same on every server instance that hosts an availability replica for the database.
Database Compatibility Level: The compatibility level of a database in an availability group must be set to 110 or higher.
Database Filegroups and Files: All filegroups must be online.
Database Size: The maximum size for databases in an availability group is 10TB.
Full-Text Indexes: Full-text indexes must be unique across replicas.

5. Restrictions on Availability Groups

Availability Group with No Availability Databases: An availability group without availability databases is not supported.
Availability Group Listener: An availability group can have multiple IP addresses but only one DNS name.
Maximum Number of Availability Replicas: An availability group supports one primary replica and up to eight secondary replicas.
Network Name: An availability group listener’s network name (virtual network name) must be unique in the domain and NetBIOS.

Conclusion

Always On Availability Groups in SQL Server offers a robust solution for high availability and disaster recovery. However, it’s essential to be aware of its restrictions to ensure smooth operation and avoid potential issues. Always refer to the official Microsoft documentation for the most up-to-date and comprehensive information.

Note: Always refer to the official documentation for a detailed understanding and the latest updates.

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.