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