Why you should use SQL Server contained availability groups to save time – and why consultants may not tell you about them
Admittedly, the title is a bit provocative—but considering how underappreciated contained availability groups are, a little boldness feels justified. 🙂
So, why exactly do we at Sarpedon Quality Lab recommend their use?
Our customers—whether in banking, healthcare, or other critical industries—demand 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.
The concept of contained availability groups
The fundamental idea behind contained 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 – without requiring additional steps by the DBAs to replicate server-level objects to all available hosts in advance.
That’s it. In theory, all you need to do is set up the availability group as contained, add the databases, and then start using it.
No need to copy logins, jobs, schedules, and other server-level objects to all the involved nodes via some routines.
And 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’t connect, jobs behave differently, and the search for the culprit starts.
This is precisely where CAGs save you time and therefore money: you do not need to do any of that.
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 dedicated master and msdb databases which is then part of the ongoing synchronization.
This per-CAG system database concept means that server-objects in CAG1 will not be visible in CAG2 and not even on the host – unless you look into the specially created contained master and msdb databases, which carry the name of the AG as a prefix like CAG1_master and CAG1_msdb.
One of the most-asked questions for SQL Server over 2 decades has been around limiting visibility of individual databases. – Contained Availability Groups solve that!
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)
How is that?
It’s 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 “their” CAG will only see the databases that belong to the CAG and no others.
With contained availability groups, you can combine SQL Server instances on a shared server and still make each database application feel like it’s running alone on the instance.
The following image shows which databases are visible when a login connects to either the host or a CAG.
The same concept applies to all other server-level objects.
Note: There is no security guarantee around the limitation to visible databases. Members of the sysadmin server role can always connect to any database.
Deployment-considerations for contained AGs
When you use contained AGs, you need to connect to it using the listener name (a listener is a requirement for a CAG).
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—regardless of which replica you connect to.
There is one important exception: The databases.
Databases can only be created on the hosting instance, including restores. – 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)
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.
Please vote for this functionality to be included here: Feature request: Allow creation and restore of databases in contained availability group
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
- The application databases must be created on the host first.
- Then in a second step, you need to move the databases to the contained AG which you create.
- 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.
Tip: Use a DNS alias for all your SQL Server connections—it can save you countless hours and headaches over the lifetime of your environment.
A special callout for security Auditing
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, you need to roll out your Auditing in every CAG, not just on the host!
Administering contained AGs
Contained AGs save you a lot of time since you do not need to develop or maintain additional routines for synchronizing server-level objects.
As with any new technology, it’s 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’s 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
Tip: Use the Central Management Servers functionality in Management Studio and make sure to add a connection to every contained AG Listener in addition to the SQL Server Instances themselves.
Further reading: Administer multiple servers using Central Management Servers
List of issues and bugs around contained AGs
As much as I appreciate this feature, my colleagues at Sarpedon Quality Lab 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:
- Contained Availability Group writes transaction marks to host instead of contained msdb
- Bug: contained master database has page_verify option set to NONE plus it cannot be changed
- Bug: contained msdb has Trustworthy bit set OFF (in Contained Availability Group)
- Jobs cannot log to table when job part of Contained Availability Group
- 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)
- Feature request: Allow creation and restore of databases in contained availability group
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: Books Online: What is a contained availability group?
And fellow MCM, Edwin Sarmiento, has published a blog with some nice screenshots where you can see the configuration here: SQL Server Contained Availability Groups Configuration
Talk to us if you want to learn more ways to make your environment more efficient.
Andreas
Hi Andreas,
thanks again.
Probably it is not possible to use the CAG-Listener with Read-scale AGs (or better “clusterless AGs)
I think in 2 months i can install an SQL 2025-server.
With it i can test this combination and i will send you than the result.
Regards
Jeff
Hi Jeff,
The setup without Listener is probably not possible. Although I wonder if it could work by specifying the name of the Database in the connection string – which is alternative way to connect to the CAG (without using the listener). But the combination with read-intent is not 100% clear from the docs.
So only a test can tell for sure, indeed.
I’d be curious to know, too.
Thanks in advance
Andreas
Hi Andreas,
you wrote: “One of the most-asked questions for SQL Server over 2 decades has been around limiting visibility of individual databases.”
but i read this in the documentation:
”
Contained AGs are a mechanism for keeping execution environment configurations consistent across the replicas of an availability group. They don’t represent a security boundary.
”
so it is “only” a visibility thing but not for security (or at max security by obscurity…) ? 🙂
Hi Jeff.
Good observation. Both is correct.
Yes, it is technically possible to reach out from one database within a CAG to another not within the (same) CAG – given the account has the permissions.
And yet, the common ask is really about visibility. Mainly in SSMS or when querying sys.databases with a tool. If a Login is created within a CAG, it does not see nor has access to any other database.
So that fulfills that ask IMO.
To get the full list of databases, one would need to find a way to query the hosts master. That would not be a default-thing any more but require running some code.
The security to block that is weak, so I am sure on can find a way. Hence, no promise.
But for ay regular work, giving an app access to just a CAG fulfills this wish for most people as far as I understand it.
hope that makes sense
Andreas
Thanks for your fast answer.
You wrote “Yes, it is technically possible to reach out from one database within a CAG to another not within the (same) CAG – given the account has the permissions.”
Hmmm, how can a login have permissions outside the CAG if the login is created in the CAG_master? Or the engine checks simple if the SID matches and than it let it go into the non-CAG-DB?
An other question:
At the moment i have no SQL 2022 or SQL 2025 – Servers to do tests.
So i try to ask you:
I have read that the visibility limitation in CAGs works only if is used the CAG-listener in the connection string.
But we use read-scale-AGs (aka clusterless AGs). This only for the purpose to have DR in a second Datacenter…
But with read-scale-AGs there is no AG-listener.
So this means, that the “visibility limitation”-feature with CAGs is not usable, if the CAG is a readscale AG?
Or the visibility limitation/filtration is based on in which master-DB the login is created?
Kind regards
Jeff
Hi Jeff
well, I don’t want to give instructions on how to circumvent the Contained Availability Group behavior, at least not for now.
But for example, consider this scenario: you have 2 databases, DB1 and DB2 and a Login U1 that has access to both with a Database User.
Now you create the Contained AG and only put DB2 in it.
You then Drop the Login from the Server – but not the User within both databases.
Then you re-create the Login U1 but within the CAG.
U1 can now log on to the CAG-Listener connection – but not to the host. However, it is possible to draft a query that crosses from DB2 into DB1 even without seeing it in object explorer. And with the user U1 still in DB1 he could access it.
Regarding the read-intent and cluster-less setup.
From the documentation (I have not tested it), Contained AGs do work with that setup – but it requires the (CAG)-listener to be used and make sure the read-intent is specified in the connection.
Here is the respective doc-entry: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16#read-only-routing-and-contained-availability-groups
Hopefully that is not a hard stopper for you.
Regard
Andreas
“One of the most-asked questions for SQL Server over 2 decades has been around limiting visibility of individual databases. ”
Can’t this be solved by simply removing view any database on public role?
That may work for a few. But most people I talk to expect the following behavior:
only show the databases which the logged on User has access to.
If you remove the VIEW ANY DATABASE from public, most users see no database at all. Only the owner of a database will still see the database he owns.
That is not a great compromise for most.