First look at SQL Server 2022 Contained Availability Groups

Introduction

SQL Server 2022 introduces the new concept of Contained Availability Groups. This is something that DBAs have been waiting for since Availability Groups were introduced 10 years ago.

Contained Availability Groups enhance the Availability groups by providing the ability to replicate system objects (like SQL Agent jobs, Logins and Linked Servers) between your database replicas.

In this blog post, using SQL Server 2022 CTP2.0 we will have a first look at the upcoming Contained Availability Groups.

Table of contents

• Contained Availability Groups
• Creating a Contained Availability Group
• Contained Availability Group in SSMS
• DMV change
• Connecting through the Listener
• Creating an Agent Job
• Creating a Login
• Performing a Failover
• Deleting a Contained Availability Group
• Reuse old Contained Availability Group system databases
• Final words

Contained Availability Groups

Since the introduction of Availability Groups with SQL Server 2012, synchronization between multiple replicas only concerns user databases.
There are challenges when applications also rely on objects such as users, logins, permissions, agent jobs, etc., which are stored in the system databases (master or msdb).
This type of object must be manually replicated by a DBA, or scripted, for example with dbatools.

Contained Availability Groups solve this problem by automatically creating a master database and an msdb database for each Availability Group which automatically replicates objects created in its context.

Creating a Contained Availability Group

The SSMS Wizard contains a new Checkbox that is not checked by default.

There’s a new T-SQL keyword for the CREATE AVAILABILITY GROUP command:

CREATE AVAILABILITY GROUP [ContainedAG02]
WITH (
	AUTOMATED_BACKUP_PREFERENCE = PRIMARY,
	REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0,
	CONTAINED
)
FOR DATABASE [appdb02] 
REPLICA ON N'SQL19VM1\SQL2022A' [...]

Contained Availability Group in SSMS

The master and msdb databases are visible both under the Availability Databases folder in SSMS and also in the main “User” Databases list.

Adding another Contained AG brings of course more of these databases.

DMV change

Notice there’s a new column to the sys.availability_groups DMV called is_contained.

Connecting through the Listener

What’s interesting too is that when connecting through the listener you only see the databases related to the Contained AG related to that listener.

This is not the behavior with a not Contained “Normal” Availability Group.

Creating an Agent Job

I have created 2 Agent Jobs with different scopes. The scope is defined at the connection time as there are no changes for now in the “New Job” wizard to set the Job to be a Contained AG or an instance level related Job.

-- Connect to primary replica on default database
USE msdb
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'test_ContainedAG', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId
GO
-- Connect to ContainedAG01 on default database
USE msdb
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'test_ContainedAG', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=2, 
		@notify_level_page=2, 
		@delete_level=0, 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId
GO

The result, as shown in SSMS is that the Contained AG shows only its related Jobs. What I find disturbing is that as a sysadmin connected to my instance I don’t see the ContainedAG Jobs but only the instance scoped one.

I can see this causing a lot of confusion when administrating instances with multiple ContainedAG and dozens of Jobs.

Same thing with SQL.

select name
from msdb..sysjobs
go

select name
from ContainedAG01_msdb..sysjobs

Creating a Login

The same thing goes for Logins and Users; no change to the UI. They have to be created in the correct scope in T-SQL.

Performing a Failover

The Failover wizard is showing the master and msdb databases as affected by the Failover operation.

All objects related to the Contained AG (only Logins and Jobs here) are also available after a failover on the secondary replica without the need for a “Manual”/”DBA” object synchronization.

SELECT @@SERVERNAME AS ServerName
	, ag.name AS AgName
	, dc.[database_name]
	, rs.is_primary_replica
	, rs.synchronization_state_desc 
FROM sys.dm_hadr_database_replica_states AS rs
	INNER JOIN sys.availability_databases_cluster AS dc
		ON rs.group_id = dc.group_id 
		AND rs.group_database_id = dc.group_database_id
	INNER JOIN sys.availability_groups AS ag
		ON ag.group_id = rs.group_id
WHERE is_primary_replica = 1

select name, sysadmin
from master.sys.syslogins
where name not like '##%' and name not like 'NT%'

select name
from msdb..sysjobs

Deleting a Contained Availability Group

Deleting the Contained Availability Group will not drop the master and msdb databases.

Reuse old Contained Availability Group system databases

I can recreate a new Availability Group now by reusing the old msdb and master databases.
This is when the “Reuse System Database” can be checked.

As you can see just above I tried to use a new Contained Availability Group name with the suffix “_bis”.
It didn’t go as planned. The master and msdb databases were not detected as such but were considered as simple user databases.

The second attempt with the original name did not work any better.

Actually, the master and msdb need to be unselected on this Wizard panel. Only User databases have to be selected.
The msdb and master databases will be reused based on their name matching with the Contained AG Name.

This is something that could also be improved in the Wizard to make it clear these databases are of a special kind and maybe should be added as User databases in a Contained AG when selecting the “Reuse System database” option.

Final words

This blog post was just a basic introduction to Contained Availability Group playing with SQL Server 2022 CTP2.0 and SSMS 19 preview. The feature Contained AG feature seems to be working as expected but might need some important improvements to both SSMS and DMVs to make the scope (Contained or instance) of objects (Logins, Agent Jobs, etc..) more clear, and easier to manage.