Introduction to Contained Availability Group in SQL Server 2022

In the ever-demanding world of data management, ensuring application uptime and availability is paramount. For SQL Server users, AlwaysOn Availability Groups (AGs) have long been a trusted solution for disaster recovery. But what if you could achieve even greater control and simplification in your disaster recovery strategy? Enter Contained Availability Groups, a powerful new feature introduced in SQL Server 2022.

Contained Availability Groups elevate disaster recovery to a new level by addressing a key limitation of traditional AGs. Let's learn about Contained Availability Groups and how they revolutionize data protection for your SQL Server databases.

Contained Availability Groups are a specialized type of Availability Group that tackles two key challenges:

1. Managing Metadata Objects: Traditional Availability Groups focus on replicating user databases. However, applications rely on server-level objects like logins, permissions, and SQL Agent jobs. Contained Availability Groups address this by managing these objects at the AG level, ensuring consistency across all replicas.

2. System Databases: These groups utilize specialized contained system databases within the AG. These databases, typically named <AGName_master> and <AGName_msdb>, mirror the traditional master and msdb databases and are replicated like any other user database.

Understanding Traditional Availability Groups:

• A standard Availability Group typically consists of one or more user databases that function as a unit.

• In the event of a failure, the entire group gets seamlessly moved (failed over) to another replica node within the group, ensuring high availability for your applications.

The Challenge: Beyond Databases

While traditional Availability Groups excel at database replication, they don't inherently manage server-level objects. These objects typically reside in system databases like master and msdb. When applications rely on these objects, maintaining consistency across replicas becomes complex.

What is a Contained Availability Group?

A Contained Availability Group is a feature of SQL Server Always On Availability Groups (AG) introduced in SQL Server 2022. It extends the concept of the group of databases being replicated to include relevant portions of the master and msdb databases.

Here are some key characteristics of a Contained Availability Group:

• It includes specialized contained system databases within the AG.

• It generally consists of one or more user databases intended to operate as a coordinated group, which are replicated on some number of nodes in a cluster.

• When there’s a failure in the node or the health of the SQL Server on the node that hosts the primary copy, the databases group is moved as a unit to another replica node in the AG.

• All user databases are kept in sync across all replicas of the AG, either in synchronous or asynchronous mode.

• It extends the concept of the group of databases being replicated to include relevant portions of the master and msdb databases.

• In a contained AG, you can create users, logins, permissions, and so on, at the AG level, and they are automatically consistent across replicas in the AG and consistent across databases within that contained AG.
  

Contained Availability Groups (AGs) in SQL Server extend the concept of the group of databases being replicated uniquely:

1. Extension of Database Replication: In a traditional AG, only user databases are included in the group and replicated across replicas. However, Contained AGs extend this concept to include relevant portions of the master and msdb system databases. This means that server-level objects such as logins, permissions, SQL Server Agent jobs, and others relevant to the databases in the AG are also included and replicated. This is a significant enhancement over the traditional AG model, as it ensures that all necessary database and server-level objects are consistently replicated across all replicas, thereby maintaining application functionality even in the event of a failover.

2. Contained AG Environment: The contained AG environment includes settings that would affect the application relying on them. For example, if an application relies on a specific login or SQL Server Agent job, these are included in the AG and replicated across all replicas. This ensures that the application can function correctly even if a failover occurs. This is a significant advantage over traditional AGs, where server-level objects are manually recreated on each replica.

Comparison between contained databases and contained AGs

Contained Databases	Contained Availability Groups
Introduced in SQL Server 2012	Introduced in SQL Server 2022
It is a database isolated from other databases and the instance of the SQL server hosting it.	It is always on the availability group that supports managing metadata objects (users, logins, permissions, SQL Agent jobs, etc) at the AG level.
Only replicates logins and users, and the scope of the replicated login or user is limited to that single database (and its replicas).	Extends the concept of the group of databases being replicated to include relevant portions of the master and msdb databases.
Increases database portability and reduces dependencies on the SQL Server instance.	Increases database portability, reduces dependencies on the SQL Server instance and ensures consistency across all replicas in the AG.
All settings and metadata of the database are stored within the database itself. This includes user accounts, stored inside the database instead of leveraging the instance logins.	You can create users, logins, permissions, and so on, at the AG level, and they are automatically consistent across replicas in the AG, and consistent across databases within that contained AG.

Benefits of Contained AGs

1. Administrative Efficiency: Contained AGs save administrators from making manual changes themselves. This is because the relevant portions of the master and msdb databases, which include server-level objects like logins, permissions, and SQL Server Agent jobs, are included in the AG. This means any changes to these objects are automatically replicated across all replicas in the AG.

2. Consistency Across Replicas: Contained AGs eliminate the hassle of recreating (and removing) server-level objects on all secondary replicas. This is a significant advantage in scenarios where consistency across replicas is crucial. For example, if an application relies on a specific login or SQL Server Agent job, the administrator doesn’t have to create these objects on each secondary replica. Instead, these objects are automatically included in the AG and replicated across all replicas.

Use Cases for Contained Availability Groups

Contained Availability Groups (AGs) in SQL Server have several use cases, particularly in high availability and consistency across replicas is crucial. Here are some key use cases:

1. High Availability: Contained AGs are designed to provide high availability for SQL Server databases. They ensure that all user databases, along with relevant portions of the master and msdb databases, are kept in sync across all replicas of the AG.

2. Consistency Across Replicas: Contained AGs maintain consistency across all replicas in the AG and across databases within that contained AG. This includes server-level objects like logins, permissions, SQL Server Agent jobs, and more.

3. Simplifying Database Management: Contained AGs simplify the management of metadata objects at the AG level. They reduce administrative overhead by eliminating manually recreated server-level objects on all secondary replicas.

4. Security: Contained AGs help maintain security by ensuring that changes to server-level objects, such as the creation or deletion of logins, are replicated across all replicas. This prevents potential security risks associated with discrepancies between primary and secondary servers.

5. Disaster Recovery: In the event of a node failure or an issue with SQL Server on the primary node, Contained AGs allow the group of databases to be moved as a unit to another replica node in the AG.

6. Migration to New Hardware or Configurations: Contained AGs can be useful when migrating to new hardware or changing the underlying operating systems.

Limitations of Contained Availability Groups

Contained Availability Groups (AGs) in SQL Server offer many benefits, but they also have some limitations:

1. Limited Scope: The server-level objects within the Contained Availability Groups are limited within its boundaries. This means only the logins and SQL Server Agent jobs created within the context of the Contained Availability Groups will be created in those system databases.

2. Access to Databases Outside AG: No boundary will keep a connection to a contained AG from accessing databases outside the AG. This could potentially lead to security concerns or inconsistencies if not managed properly.

3. Initial Empty Templates: The system databases in a newly created contained AG aren’t copies from the instance where the CREATE AVAILABILITY GROUP command is run. They are initially empty templates without any data. This means that any existing data or configurations in the system databases of the primary server are not automatically included in the contained AG.

Conclusion

Contained Availability Groups represent a significant leap forward in SQL Server disaster recovery. By managing user databases and server-level objects within a single unit, they eliminate the complexities associated with traditional AG setups. This translates to faster failovers, simplified management, and a more robust disaster recovery posture for your critical SQL Server applications.