{"id":6864,"date":"2025-04-29T20:41:22","date_gmt":"2025-04-30T01:41:22","guid":{"rendered":"https:\/\/andreas-wolter.com\/?p=6864"},"modified":"2026-04-16T18:58:57","modified_gmt":"2026-04-16T23:58:57","slug":"2504_sqlserver_contained_availability_groups","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/2504_sqlserver_contained_availability_groups\/","title":{"rendered":"Why you should use SQL Server contained availability groups to save time \u2013 and why consultants may not tell you about them"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-m0cxh8ps-98c753e6e6dbc7a2a22eaa580ab40412\">\n#top .av-special-heading.av-m0cxh8ps-98c753e6e6dbc7a2a22eaa580ab40412{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-m0cxh8ps-98c753e6e6dbc7a2a22eaa580ab40412 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-m0cxh8ps-98c753e6e6dbc7a2a22eaa580ab40412 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-m0cxh8ps-98c753e6e6dbc7a2a22eaa580ab40412 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >Why you should use SQL Server contained availability groups to save time \u2013 and why consultants may not tell you about them<\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-m0cxgkjy-c935304b4106b45214698f40e83a9894 '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Admittedly, the title is a bit provocative\u2014but considering how underappreciated contained availability groups are, a little boldness feels justified. \ud83d\ude42<\/p>\n<p>So, why exactly do we at <strong><em><a href=\"https:\/\/sarpedonqualitylab.us\/\" target=\"_blank\" rel=\"noopener\">Sarpedon Quality Lab<\/a><\/em><\/strong> recommend their use?<br \/>\nOur customers\u2014whether in banking, healthcare, or other critical industries\u2014demand the highest standards of availability and security, and rightfully so. Meeting those standards requires our full attention. Using available ways to simplify operations or eliminate security and reliability risks is a no-brainer. Contained availability groups check all these boxes and save significant time, both in the short term and over the long run, ultimately reducing costs.<\/p>\n<h2>The concept of contained availability groups<\/h2>\n<p>The fundamental idea behind <u>contained<\/u> availability groups (I will abbreviate it to contained AGs or CAGs in his article), as opposed to the non-contained type, is that a CAG fails over with all the required objects that are necessary for the applications to continue to work on the new host \u2013 without requiring additional steps by the DBAs to replicate server-level objects to all available hosts in advance.<\/p>\n<p>That\u2019s it. In theory, all you need to do is set up the availability group as contained, add the databases, and then start using it.<br \/>\nNo need to copy logins, jobs, schedules, and other server-level objects to all the involved nodes via some routines.<br \/>\nAnd this is by far the most common issue when using the traditional availability groups: at some point, someone will forget that Logins cannot just be created at any given host without following a process for proper deployment everywhere. Same for new jobs, altering job definitions, schedules, and so on. Over time, unless the environment is tightly controlled, disparities will occur. And when the failover happens, things stop working, users can\u2019t connect, jobs behave differently, and the search for the culprit starts.<\/p>\n<p>This is precisely where CAGs save you time and therefore money: you do not need to do any of that.<\/p>\n<p>The reason is that SQL Server also replicates the master database and the msdb database for the CAG. To be more precise, each CAG comes with its own <u>dedicated<\/u> master and msdb databases which is then part of the ongoing synchronization.<\/p>\n<p>This per-CAG system database concept means that server-objects in CAG1 will not be visible in CAG2 and not even on the host \u2013 unless you look into the specially created contained master and msdb databases, which carry the name of the AG as a prefix like <em>CAG1_master<\/em> and <em>CAG1_msdb<\/em>.<\/p>\n<p><strong>One of the most-asked questions for SQL Server over 2 decades has been around limiting visibility of individual databases. &#8211; Contained Availability Groups solve that!<\/strong><\/p>\n<blockquote><p><em>Solution: Logins that reside in a contained availability group must connect to the CAG and can only see the databases that are part of the CAG. (unless they have sysadmin-level privileges)<\/em><strong style=\"font-size: 16px;\">\u00a0<\/strong><\/p><\/blockquote>\n<p>How is that?<\/p>\n<p>It\u2019s quite simple: if you design your contained availability groups to contain only the databases needed for respective applications, then the logins that have permission to connect to \u201ctheir\u201d CAG will only see the databases that belong to the CAG and no others.<\/p>\n<blockquote><p><em>With contained availability groups, you can combine SQL Server instances on a shared server and still make each database application feel like it\u2019s running alone on the instance.<\/em><\/p><\/blockquote>\n<p>The following image shows which databases are visible when a login connects to either the host or a CAG.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6878\" src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/04\/2504_ContainedAvailabilityGroup_Databases-1.jpg\" alt=\"ContainedAvailabilityGroup_Databases\" width=\"871\" height=\"300\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/04\/2504_ContainedAvailabilityGroup_Databases-1.jpg 871w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/04\/2504_ContainedAvailabilityGroup_Databases-1-300x103.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/04\/2504_ContainedAvailabilityGroup_Databases-1-768x265.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2025\/04\/2504_ContainedAvailabilityGroup_Databases-1-705x243.jpg 705w\" sizes=\"auto, (max-width: 871px) 100vw, 871px\" \/><\/p>\n<p>The same concept applies to all other server-level objects.<\/p>\n<blockquote><p><em>Note: There is no security guarantee around the limitation to visible databases. Members of the sysadmin server role can always connect to any database.<\/em><\/p><\/blockquote>\n<h2><\/h2>\n<h2><br class=\"avia-permanent-lb\" \/>Deployment-considerations for contained AGs<\/h2>\n<p>When you use contained AGs, you need to connect to it using the listener name (a listener is a requirement for a CAG).<\/p>\n<p>Once connected to the Contained Availability Group (CAG), you can create logins, jobs, and other objects directly through that connection. These will become part of the availability group, ensuring that after a failover, everything remains accessible\u2014regardless of which replica you connect to.<\/p>\n<p>There is one important exception: The databases.<\/p>\n<p>Databases can only be created on the hosting instance, including restores. \u2013 You can bckup databases from a CAG-connection, but restore can only happen at the host-level. (and restoring a replicated database requires it to be removed from an AG anyway)<\/p>\n<blockquote><p><em>Feature request: While it may seem logical, this is the one feature I truly hope the product team will add: the ability to create databases directly through a contained availability group or listener connection.<br \/>\nPlease vote for this functionality to be included here: <a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/07f0807c-6825-f011-9d47-7c1e52d4bdd3\" target=\"_blank\" rel=\"noopener\">Feature request: Allow creation and restore of databases in contained availability group<\/a><br \/>\n<\/em><\/p><\/blockquote>\n<p>Until that is supported, it is important to consider this process for new database-application deployments, which is very different from using non-contained AGs<\/p>\n<ul>\n<li>The application databases must be created on the host first.<\/li>\n<li>Then in a second step, you need to move the databases to the contained AG which you create.<\/li>\n<li>And then you need to inform the application that it needs to use a different DNS-name, this time to connect to the CAG-listener instead of the host.<em>\u00a0<\/em><\/li>\n<\/ul>\n<blockquote><p><em>Tip: Use a DNS alias for all your SQL Server connections\u2014it can save you countless hours and headaches over the lifetime of your environment.<\/em><\/p><\/blockquote>\n<h2><\/h2>\n<h2><br class=\"avia-permanent-lb\" \/>A special callout for security Auditing<\/h2>\n<p>Continuous Auditing should be implemented at every SQL Server installation, and when using CAGs, remember that Server Audits, just as Logins are stored in the master database. Therefore, <strong>you need to roll out your Auditing in every CAG, not just on the host!<\/strong><\/p>\n<h2>Administering contained AGs<\/h2>\n<p>Contained AGs save you a lot of time since you do not need to develop or maintain additional routines for synchronizing server-level objects.<br \/>\nAs with any new technology, it\u2019s important that your DBA team understands the distinctions between contained and non-contained availability groups. The key difference is that to get the full picture of your environment, you need to connect to each contained AG in addition to the hosting instance. It\u2019s somewhat like a virtual instance running on top of the main SQL Server instance, where each CAG maintains its own set of logins, jobs, and other configurations<\/p>\n<blockquote><p><em>Tip: Use the Central Management Servers functionality in Management Studio and make sure to <strong>add a connection to every contained AG Listener in addition to the SQL Server Instances<\/strong> themselves.<br \/>\nFurther reading: <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/administer-multiple-servers-using-central-management-servers?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">Administer multiple servers using Central Management Servers<\/a><\/em><\/p><\/blockquote>\n<h2><\/h2>\n<h2><br class=\"avia-permanent-lb\" \/>List of issues and bugs around contained AGs<\/h2>\n<p>As much as I appreciate this feature, my colleagues at <em>Sarpedon Quality Lab<\/em> came across a few bugs that are worth mentioning. None of them prevented me from using the feature, but you need to be aware to make sure it does not affect you. Please upvote them so Microsoft prioritizes fixing them:<\/p>\n<ol>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/4629b2c1-2325-f011-9d47-7c1e52d4bdd3\" target=\"_blank\" rel=\"noopener\">Contained Availability Group writes transaction marks to host instead of contained msdb<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/cf69002b-53ea-ef11-a4de-0022484ba63a\" target=\"_blank\" rel=\"noopener\">Bug: contained master database has page_verify option set to NONE plus it cannot be changed<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/3d88aa5c-70f8-ef11-a4dd-0022484ba63a\" target=\"_blank\" rel=\"noopener\">Bug: contained msdb has Trustworthy bit set OFF (in Contained Availability Group)<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/03e8e8d0-2896-ef11-95f6-000d3ae2b698\" target=\"_blank\" rel=\"noopener\">Jobs cannot log to table when job part of Contained Availability Group<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/1358762b-64e3-ef11-b542-00224854717c\" target=\"_blank\" rel=\"noopener\">Having the same Audit name on server and inside contained AG will lead to error when trying to start it (but no when creating it)<\/a><\/li>\n<li><a href=\"https:\/\/feedback.azure.com\/d365community\/idea\/07f0807c-6825-f011-9d47-7c1e52d4bdd3\" target=\"_blank\" rel=\"noopener\">Feature request: Allow creation and restore of databases in contained availability group<\/a><\/li>\n<\/ol>\n<p>I hope I have made you curious about contained AGs. Make sure to understand the documentation, which is just a single page currently, available here: <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/availability-groups\/windows\/contained-availability-groups-overview?view=sql-server-ver16\" target=\"_blank\" rel=\"noopener\">Books Online: What is a contained availability group?<\/a><\/p>\n<p>And fellow MCM, Edwin Sarmiento, has published a blog with some nice screenshots where you can see the configuration here: <a href=\"https:\/\/www.mssqltips.com\/sqlservertip\/7523\/sql-server-contained-availability-groups-configuration\/\" target=\"_blank\" rel=\"noopener\">SQL Server Contained Availability Groups Configuration<\/a><\/p>\n<p>Talk to us if you want to learn more ways to make your environment more efficient.<\/p>\n<p>Andreas<\/p>\n<\/div><\/section>\r\n\r\n<div  class='flex_column av-av_one_full-048119fdb636ea3d70edc0b975509c6c av_one_full  avia-builder-el-2  el_after_av_textblock  el_before_av_social_share  first flex_column_div  column-top-margin'     ><div  class='hr av-av_hr-23b3846cdd0fbd0e234720a594f6db24 hr-default  avia-builder-el-3  el_before_av_textblock  avia-builder-el-first '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\n<section  class='av_textblock_section av-av_textblock-564067357eb74c20cdd60b282c9b50bd '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Planning a migration or designing a highly available SQL Server architecture? Ensure your deployment is bulletproof. The experts at Sarpedon Quality Lab specialize in complex, cost-efficient database architectures.<\/p>\n<\/div><\/section>\n<div  class='avia-button-wrap av-av_button-45c2c4f5331b7088976bf5ab7a97dc38-wrap avia-button-center  avia-builder-el-5  el_after_av_textblock  el_before_av_hr '>\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_button-45c2c4f5331b7088976bf5ab7a97dc38\">\n#top #wrap_all .avia-button.av-av_button-45c2c4f5331b7088976bf5ab7a97dc38{\nfont-size:14px;\nbackground-color:#75a823;\nborder-color:#75a823;\ncolor:#ffffff;\nbox-shadow: 0 0 5px 5px ;\ntransition:all 0.4s ease-in-out;\n}\n<\/style>\n<a href=\"https:\/\/sarpedonqualitylab.us\/services\" class=\"avia-button av-av_button-45c2c4f5331b7088976bf5ab7a97dc38 avia-icon_select-yes-left-icon avia-size-medium avia-position-center\" target=\"_blank\" rel=\"noopener\"><span class='avia_button_icon avia_button_icon_left' aria-hidden='true' data-av_icon='\ue832' data-av_iconfont='entypo-fontello'><\/span><span class='avia_iconbox_title' >Explore Architectural Services<\/span><\/a><\/div>\n<div  class='hr av-av_hr-23b3846cdd0fbd0e234720a594f6db24 hr-default  avia-builder-el-6  el_after_av_button  avia-builder-el-last '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div><\/div>\r\n\r\n<div  class='av-social-sharing-box av-5n5vpa-78ffdd9d224b4a246af65bdc00dce900 av-social-sharing-box-default  avia-builder-el-7  el_after_av_one_full  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share article<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/2504_sqlserver_contained_availability_groups\/&#038;t=Why%20you%20should%20use%20SQL%20Server%20contained%20availability%20groups%20to%20save%20time%20%E2%80%93%20and%20why%20consultants%20may%20not%20tell%20you%20about%20them\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=Why%20you%20should%20use%20SQL%20Server%20contained%20availability%20groups%20to%20save%20time%20%E2%80%93%20and%20why%20consultants%20may%20not%20tell%20you%20about%20them&#038;url=https:\/\/andreas-wolter.com\/en\/?p=6864\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=Why%20you%20should%20use%20SQL%20Server%20contained%20availability%20groups%20to%20save%20time%20%E2%80%93%20and%20why%20consultants%20may%20not%20tell%20you%20about%20them&#038;url=https:\/\/andreas-wolter.com\/en\/2504_sqlserver_contained_availability_groups\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-4ofg9q-c2108540b480aba02923089240a3a176\">\n#top .hr.hr-invisible.av-4ofg9q-c2108540b480aba02923089240a3a176{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-4ofg9q-c2108540b480aba02923089240a3a176 hr-invisible  avia-builder-el-8  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-284ftq-f5a1564cd6b8ffad6ce835e2d40de4b7  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":4,"featured_media":6878,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[98,57],"tags":[378,206],"class_list":["post-6864","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hadr-alwayson-en","category-security-en","tag-availability-groups","tag-sql-security"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6864","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=6864"}],"version-history":[{"count":12,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6864\/revisions"}],"predecessor-version":[{"id":7443,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/6864\/revisions\/7443"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/6878"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=6864"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=6864"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=6864"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}