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.
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 | ||
| 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.