{"id":17276,"date":"2022-06-09T00:21:49","date_gmt":"2022-06-08T22:21:49","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=17276"},"modified":"2025-10-01T12:03:31","modified_gmt":"2025-10-01T10:03:31","slug":"first-look-at-sql-server-2022-contained-availability-groups","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/","title":{"rendered":"First look at SQL Server 2022 Contained Availability Groups"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\">Introduction<\/h4>\n\n\n\n<p>SQL Server 2022 introduces the new concept of <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/contained-availability-groups-overview?view=sql-server-ver16\">Contained Availability Groups<\/a>. This is something that DBAs have been waiting for since Availability Groups were introduced 10 years ago.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In this blog post, using <a href=\"https:\/\/info.microsoft.com\/ww-landing-sql-server-2022.html\">SQL Server 2022 CTP2.0<\/a> we will have a first look at the upcoming Contained Availability Groups.<\/p>\n\n\n\n<p><span style=\"text-decoration: underline\">Table of contents<\/span><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#containedag\">Contained Availability Groups<\/a><\/li>\n\n\n\n<li><a href=\"#create\">Creating a Contained Availability Group<\/a><\/li>\n\n\n\n<li><a href=\"#ssms\">Contained Availability Group in SSMS<\/a><\/li>\n\n\n\n<li><a href=\"#dmv\">DMV change<\/a><\/li>\n\n\n\n<li><a href=\"#listener\">Connecting through the Listener<\/a><\/li>\n\n\n\n<li><a href=\"#job\">Creating an Agent Job<\/a><\/li>\n\n\n\n<li><a href=\"#login\">Creating a Login<\/a><\/li>\n\n\n\n<li><a href=\"#failover\">Performing a Failover<\/a><\/li>\n\n\n\n<li><a href=\"#delete\">Deleting a Contained Availability Group<\/a><\/li>\n\n\n\n<li><a href=\"#reuse\">Reuse old Contained Availability Group system databases<\/a><\/li>\n\n\n\n<li><a href=\"#final\">Final words<\/a><\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"containedag\">Contained Availability Groups<\/h4>\n\n\n\n<p>Since the introduction of Availability Groups with SQL Server 2012, synchronization between multiple replicas only concerns user databases.<br>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).<br>This type of object must be manually replicated by a DBA, or scripted, for example with <a href=\"https:\/\/dbatools.io\/\">dbatools<\/a>.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"create\">Creating a Contained Availability Group<\/h4>\n\n\n\n<p>The SSMS Wizard contains a new Checkbox that is not checked by default.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"815\" height=\"462\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png\" alt=\"\" class=\"wp-image-17317\" style=\"width:714px;height:405px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png 815w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13-300x170.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13-768x435.png 768w\" sizes=\"auto, (max-width: 815px) 100vw, 815px\" \/><\/figure>\n<\/div>\n\n\n<p>There&#8217;s a new T-SQL keyword for the CREATE AVAILABILITY GROUP command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE AVAILABILITY GROUP &#091;ContainedAG02]\nWITH (\n\tAUTOMATED_BACKUP_PREFERENCE = PRIMARY,\n\tREQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0,\n\t<mark style=\"background-color:#fcb900\" class=\"has-inline-color\">CONTAINED\n<\/mark>)\nFOR DATABASE &#091;appdb02] \nREPLICA ON N'SQL19VM1\\SQL2022A' &#091;...]<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"ssms\">Contained Availability Group in SSMS<\/h4>\n\n\n\n<p>The master and msdb databases are visible both under the Availability Databases folder in SSMS and also in the main &#8220;User&#8221; Databases list.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"545\" height=\"570\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-16.png\" alt=\"\" class=\"wp-image-17398\" style=\"width:409px;height:428px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-16.png 545w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-16-287x300.png 287w\" sizes=\"auto, (max-width: 545px) 100vw, 545px\" \/><\/figure>\n<\/div>\n\n\n<p>Adding another Contained AG brings of course more of these databases.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"506\" height=\"517\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-18.png\" alt=\"\" class=\"wp-image-17401\" style=\"width:374px;height:382px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-18.png 506w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-18-294x300.png 294w\" sizes=\"auto, (max-width: 506px) 100vw, 506px\" \/><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"dmv\">DMV change<\/h4>\n\n\n\n<p>Notice there&#8217;s a new column to the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-catalog-views\/sys-availability-groups-transact-sql?view=sql-server-ver16\">sys.availability_groups<\/a> DMV called is_contained.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"318\" height=\"130\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-27.png\" alt=\"\" class=\"wp-image-17418\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-27.png 318w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-27-300x123.png 300w\" sizes=\"auto, (max-width: 318px) 100vw, 318px\" \/><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"listener\">Connecting through the Listener<\/h4>\n\n\n\n<p>What&#8217;s interesting too is that when connecting through the listener you only see the databases related to the Contained AG related to that listener. <\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"479\" height=\"366\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03_SSMS_ContainedAG_ListenerConnection.jpg\" alt=\"\" class=\"wp-image-17403\" style=\"width:311px;height:237px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03_SSMS_ContainedAG_ListenerConnection.jpg 479w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03_SSMS_ContainedAG_ListenerConnection-300x229.jpg 300w\" sizes=\"auto, (max-width: 479px) 100vw, 479px\" \/><\/figure>\n<\/div>\n\n\n<p>This is not the behavior with a not Contained &#8220;Normal&#8221; Availability Group.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"577\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03bis_SSMS_NormalAG.jpg\" alt=\"\" class=\"wp-image-17404\" style=\"width:312px;height:400px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03bis_SSMS_NormalAG.jpg 450w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/Blog32_03bis_SSMS_NormalAG-234x300.jpg 234w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"job\">Creating an Agent Job<\/h4>\n\n\n\n<p>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 &#8220;New Job&#8221; wizard to set the Job to be a Contained AG or an instance level related Job.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Connect to primary replica on default database\nUSE msdb\nGO\nDECLARE @jobId BINARY(16)\nEXEC  msdb.dbo.sp_add_job @job_name=N'test_ContainedAG', \n\t\t@enabled=1, \n\t\t@notify_level_eventlog=0, \n\t\t@notify_level_email=2, \n\t\t@notify_level_page=2, \n\t\t@delete_level=0, \n\t\t@category_name=N'&#091;Uncategorized (Local)]', \n\t\t@owner_login_name=N'sa', @job_id = @jobId\nGO\n-- Connect to ContainedAG01 on default database\nUSE msdb\nGO\nDECLARE @jobId BINARY(16)\nEXEC  msdb.dbo.sp_add_job @job_name=N'test_ContainedAG', \n\t\t@enabled=1, \n\t\t@notify_level_eventlog=0, \n\t\t@notify_level_email=2, \n\t\t@notify_level_page=2, \n\t\t@delete_level=0, \n\t\t@category_name=N'&#091;Uncategorized (Local)]', \n\t\t@owner_login_name=N'sa', @job_id = @jobId\nGO<\/code><\/pre>\n\n\n\n<p>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&#8217;t see the ContainedAG Jobs but only the instance scoped one. <\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"544\" height=\"1003\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-19.png\" alt=\"\" class=\"wp-image-17407\" style=\"width:370px;height:682px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-19.png 544w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-19-163x300.png 163w\" sizes=\"auto, (max-width: 544px) 100vw, 544px\" \/><\/figure>\n<\/div>\n\n\n<p>I can see this causing a lot of confusion when administrating instances with multiple ContainedAG and dozens of Jobs.<\/p>\n\n\n\n<p>Same thing with SQL.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select name\nfrom msdb..sysjobs\ngo\n\nselect name\nfrom ContainedAG01_msdb..sysjobs<\/code><\/pre>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"279\" height=\"300\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-20.png\" alt=\"\" class=\"wp-image-17408\" style=\"width:224px;height:241px\" \/><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"login\">Creating a Login<\/h4>\n\n\n\n<p>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.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"921\" height=\"551\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-24.png\" alt=\"\" class=\"wp-image-17413\" style=\"width:682px;height:407px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-24.png 921w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-24-300x179.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-24-768x459.png 768w\" sizes=\"auto, (max-width: 921px) 100vw, 921px\" \/><figcaption class=\"wp-element-caption\">Logins when connected to the listener<\/figcaption><\/figure>\n<\/div>\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"918\" height=\"549\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-23.png\" alt=\"\" class=\"wp-image-17412\" style=\"width:679px;height:406px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-23.png 918w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-23-300x179.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-23-768x459.png 768w\" sizes=\"auto, (max-width: 918px) 100vw, 918px\" \/><figcaption class=\"wp-element-caption\">Logins when connected to the instance<\/figcaption><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"failover\">Performing a Failover<\/h4>\n\n\n\n<p>The Failover wizard is showing the master and msdb databases as affected by the Failover operation.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"944\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-25-1024x944.png\" alt=\"\" class=\"wp-image-17414\" style=\"width:635px;height:585px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-25-1024x944.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-25-300x277.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-25-768x708.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-25.png 1102w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p>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 &#8220;Manual&#8221;\/&#8221;DBA&#8221; object synchronization.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT @@SERVERNAME AS ServerName\n\t, ag.name AS AgName\n\t, dc.&#091;database_name]\n\t, rs.is_primary_replica\n\t, rs.synchronization_state_desc \nFROM sys.dm_hadr_database_replica_states AS rs\n\tINNER JOIN sys.availability_databases_cluster AS dc\n\t\tON rs.group_id = dc.group_id \n\t\tAND rs.group_database_id = dc.group_database_id\n\tINNER JOIN sys.availability_groups AS ag\n\t\tON ag.group_id = rs.group_id\nWHERE is_primary_replica = 1\n\nselect name, sysadmin\nfrom master.sys.syslogins\nwhere name not like '##%' and name not like 'NT%'\n\nselect name\nfrom msdb..sysjobs<\/code><\/pre>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"955\" height=\"371\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-26.png\" alt=\"\" class=\"wp-image-17416\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-26.png 955w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-26-300x117.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-26-768x298.png 768w\" sizes=\"auto, (max-width: 955px) 100vw, 955px\" \/><figcaption class=\"wp-element-caption\">Contained AG objects after a Failover<\/figcaption><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"delete\">Deleting a Contained Availability Group<\/h4>\n\n\n\n<p>Deleting the Contained Availability Group will not drop the master and msdb databases.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"544\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-36.png\" alt=\"\" class=\"wp-image-17433\" style=\"width:384px;height:398px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-36.png 525w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-36-290x300.png 290w\" sizes=\"auto, (max-width: 525px) 100vw, 525px\" \/><\/figure>\n<\/div>\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"476\" height=\"324\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-37.png\" alt=\"\" class=\"wp-image-17434\" style=\"width:385px;height:262px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-37.png 476w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-37-300x204.png 300w\" sizes=\"auto, (max-width: 476px) 100vw, 476px\" \/><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"reuse\">Reuse old Contained Availability Group system databases<\/h4>\n\n\n\n<p>I can recreate a new Availability Group now by reusing the old msdb and master databases.<br>This is when the &#8220;Reuse System Database&#8221; can be checked.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"584\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-30-1024x584.png\" alt=\"\" class=\"wp-image-17425\" style=\"width:638px;height:364px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-30-1024x584.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-30-300x171.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-30-768x438.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-30.png 1085w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"564\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-31-1024x564.png\" alt=\"\" class=\"wp-image-17426\" style=\"width:642px;height:353px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-31-1024x564.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-31-300x165.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-31-768x423.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-31.png 1038w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p>As you can see just above I tried to use a new Contained Availability Group name with the suffix &#8220;_bis&#8221;.<br>It didn&#8217;t go as planned. The master and msdb databases were not detected as such but were considered as simple user databases.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"393\" height=\"247\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-38.png\" alt=\"\" class=\"wp-image-17435\" style=\"width:289px;height:182px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-38.png 393w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-38-300x189.png 300w\" sizes=\"auto, (max-width: 393px) 100vw, 393px\" \/><\/figure>\n<\/div>\n\n\n<p>The second attempt with the original name did not work any better.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"908\" height=\"399\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-41.png\" alt=\"\" class=\"wp-image-17438\" style=\"width:647px;height:284px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-41.png 908w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-41-300x132.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-41-768x337.png 768w\" sizes=\"auto, (max-width: 908px) 100vw, 908px\" \/><\/figure>\n<\/div>\n\n\n<p>Actually, the master and msdb need to be unselected on this Wizard panel. Only User databases have to be selected. <br>The msdb and master databases will be reused based on their name matching with the Contained AG Name.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-style-default\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"538\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-42-1024x538.png\" alt=\"\" class=\"wp-image-17440\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-42-1024x538.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-42-300x158.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-42-768x404.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-42.png 1082w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>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 &#8220;Reuse System database&#8221; option.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"final\">Final words<\/h4>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>Written by <a href=\"https:\/\/www.linkedin.com\/in\/steven-naudet-aa540158\/\">Steven Naudet<\/a><\/p>\n\n\n\n<p><br><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,99],"tags":[2574,2573,992,2550,2553],"type_dbi":[],"class_list":["post-17276","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-sql-server","tag-contained-ag","tag-contained-availability-groups","tag-preview","tag-sql-server-2","tag-sql-server-2022"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>First look at SQL Server 2022 Contained Availability Groups - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"First look at SQL Server 2022 Contained Availability Groups\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-06-08T22:21:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-01T10:03:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png\" \/>\n\t<meta property=\"og:image:width\" content=\"815\" \/>\n\t<meta property=\"og:image:height\" content=\"462\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"First look at SQL Server 2022 Contained Availability Groups\",\"datePublished\":\"2022-06-08T22:21:49+00:00\",\"dateModified\":\"2025-10-01T10:03:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/\"},\"wordCount\":806,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/06\\\/image-13.png\",\"keywords\":[\"Contained AG\",\"Contained Availability Groups\",\"Preview\",\"SQL Server\",\"SQL Server 2022\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/\",\"name\":\"First look at SQL Server 2022 Contained Availability Groups - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/06\\\/image-13.png\",\"datePublished\":\"2022-06-08T22:21:49+00:00\",\"dateModified\":\"2025-10-01T10:03:31+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/06\\\/image-13.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/06\\\/image-13.png\",\"width\":815,\"height\":462},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/first-look-at-sql-server-2022-contained-availability-groups\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"First look at SQL Server 2022 Contained Availability Groups\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"First look at SQL Server 2022 Contained Availability Groups - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/","og_locale":"en_US","og_type":"article","og_title":"First look at SQL Server 2022 Contained Availability Groups","og_description":"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 [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/","og_site_name":"dbi Blog","article_published_time":"2022-06-08T22:21:49+00:00","article_modified_time":"2025-10-01T10:03:31+00:00","og_image":[{"width":815,"height":462,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png","type":"image\/png"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"First look at SQL Server 2022 Contained Availability Groups","datePublished":"2022-06-08T22:21:49+00:00","dateModified":"2025-10-01T10:03:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/"},"wordCount":806,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png","keywords":["Contained AG","Contained Availability Groups","Preview","SQL Server","SQL Server 2022"],"articleSection":["Database Administration &amp; Monitoring","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/","url":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/","name":"First look at SQL Server 2022 Contained Availability Groups - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png","datePublished":"2022-06-08T22:21:49+00:00","dateModified":"2025-10-01T10:03:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-13.png","width":815,"height":462},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/first-look-at-sql-server-2022-contained-availability-groups\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"First look at SQL Server 2022 Contained Availability Groups"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17276","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17276"}],"version-history":[{"count":24,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17276\/revisions"}],"predecessor-version":[{"id":40621,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17276\/revisions\/40621"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17276"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17276"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17276"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17276"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}