SQL Server 2022 is the latest version of SQL Server and the first version to support Contained Availability Groups. This can be used to sync up agent jobs, logins, and permissions automatically between primary and secondary replicas of SQL Server Always On Availability Groups.
Always On Availability Group contains one or more user databases that can failover to secondary replica in case of failure at the primary node. The primary and secondary database sync occurs depending on the availability mode – Synchronous or Asynchronous.
One problem with SQL Server Always On is that it does not replicate users, logins, permissions, or SQL agent jobs. It is due to the reason that always-on does not replicate system database – Master or MSDB information. The DBA’s responsibility is to make sure both primary and secondary objects, permissions, and jobs are in sync. If you forget to keep secondary in sync with primary replica for users, logins, permissions, and agent jobs, your application might impact your application. Similarly, if you add a new SQL Instance into the availability group as a secondary replica, you need to configure it again with system database objects.
SQL Server 2022 introduced the Contained Availability Group for this purpose. The Contained availability group includes the relevant portion of the master and msdb database for the application to work without any issues after failover. It allows you to create users, permissions, and jobs at the availability group level. Therefore, these objects are consistent across replicas in the availability group.
Contained System Databases in SQL Server 2022 Always On availability group
The contained availability group contains its own master and msdb databases. The name of these databases is in the format of <availabilitygroup>_master and <availabilitygroup>_msdb. If you add a new AG replica, these system databases get automatically seeded to a new replica.
Please note that the <availabilitygroup>_master and <availabilitygroup>_msdb databases are different from the Master and MSDB database at the SQL instance.
Initially, when configuring an availability group, SQL Server creates empty system databases from the pre-defined templates. The DB administrator can login to the contained availability group and configure users, logins, agent jobs, etc.
If you connected to a regular SQL instance ( not the contained availability group) and configure the objects (login, users, jobs, permissions), you will not see them in the contained availability group. Therefore, SQL Server 2022 contained availability group requires you to set up objects under the security context of the contained availability group. The only exception is the sysadmin role. SQL Server copies all logins with sysadmin role in the parent instance to the new AG-specific master database.
To effectively use a contained availability group, you should create a SQL listener and connect to the listener for object creation. You can only access the new system database objects while connecting to the container availability group listener or SQL Database participating in the contained availability group.
For example, let’s assume the following environment:
Contained Availability group Name: TestContainedAG
Primary SQL Instance: SQLINST1\MSSQLSERVER
Database in the contained availability group: DBBlogger
Listener: SQLAG_Listener
Therefore, It creates additional TestContainedAG_Master and TestContainedAG_MSDB databases for storing users, permissions, and jobs.
Connect using listener SQLAG_Listener:
Users will see databases part of the contained AG, plus tempdb.
The container AG system databases names are master and msdb. However, at the instance level, These databases will be named TestContainedAG_master and TestContainedAG.
The container AG master database ID is one from inside contained AG but different (something else) when connected to the instance.
The users in the Contained AG connection can access a database not part of the availability group using the four-part name or use command.
The SP_CONFIGURE server can be set only at the instance level.
The sysadmin can perform instance-level operations, such as shutting down SQL Server from contained AG connections.
Many DB level, endpoint, or AG level operations can only be performed from instance connections, not contained AG connections.
Contained Availability group and SQL Server 2022 features
The Contained Availability group database does not support Replication, Distributed availability groups, and Log shipping with a target DB in the contained availability group.
It supports Log shipping with the source database in the contained availability group is supported.
You can implement Change Data Capture after connecting to the contained availability group listener.
You can implement transparent data encryption (TDE) with databases in a contained availability group. To use it, manually install the Database Master Key (DMK) to the new contained master database within the contained availability group.
You can query SYS.AVAILABILITY_GROUPS to determine whether an availability group is contained or not using column IS_CONTAINED.
Stay tuned for a further article on Contained Availability Group in SQL Server 2022 Always On