While being an awesome feature introduced in SQL Server 2012, Availability Groups were always missing the ability to synchronize server-level objects between replicas. This finally changed 10 years later, in SQL Server 2022, with the introduction of “Contained Availability Groups”.
What Contained Availability Group Does
A contained availability group is an AlwaysOn availability group that supports:
managing metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level.
specialized contained system databases within the availability group.
These "specialized contained system databases" are "master" and "msdb" databases dedicated to the specific availability group. These "master" and "msdb" databases become part of the availability group and so they are automatically replicated and failed over together with the rest of the group.
Additionally, when you connect to the contained availability group (via the listener, or by specifying one of the AG databases as the initial catalog), you will be connected into a "virtual context" of sorts where the operational "master" and "msdb" databases will actually be the ones attached to the availability group, and not the actual "master" and "msdb" databases of the SQL Server instance.
So if, for example, you have a SQL Server instance with a contained AG, this is what it would look like when you connect "normally" to the instance:
Notice the databases: AG_master, AG_msdb, AG2_master, AG2_msdb.
These are the contained system databases, with the availability group's name in their prefix (AG and AG2 respectively).
Once we connect to the "AG" availability group listener, we would see a slightly different picture:
Notice that all of the highlighted databases and server objects belong to the contained availability group, and all other databases and objects are not visible anymore. This is because our "master" and "msdb" databases are now the contained system databases which are separate from the actual instance system databases.
For more details about contained availability groups, such as interoperability support with other SQL Server features and more, check out the official Microsoft documentation at:
What Contained Availability Group Does Not
All this is very nice and cool, and it's a feature that's been sorely missing ever since availability groups were first introduced in SQL Server 2012.
But with that being said, it's still not quite perfect and doesn't answer all possible use cases. So, let's see what contained availability groups doesn't support.
Unsupported interoperability with SQL Server features
Currently, the following SQL Server features are not supported with a contained availability group:
SQL Server Replication of any type (transactional, merge, snapshot, etc.).
Distributed availability groups.
Log shipping where the target database is in the contained availability group. Log shipping with the source database in the contained availability group is supported.
A contained availability group also does NOT do the following
Automatically synchronize server-level objects between the contained system databases and the actual system databases.
So, if for example, you create a new login that needs to exist in both the contained and non-contained system databases, you'll have to create it twice on your own, once in each.
This is relevant to all server-level objects which are created after the contained availability group already exists: linked servers, certificates and credentials, jobs, alerts, operators, and everything else.
A job that was created within the contained availability group cannot be executed on secondary replicas.
Upon creating the contained AG, it will only copy the sysadmin logins from the instance "master" database to the contained "master" database. Everything else will need to be created/copied manually.
Alternatives
To supplement these shortcomings, here are a couple of useful alternatives that can help you achieve what a contained availability group does not:
Control which jobs run on which replicas (this is an open-source solution developed by Madeira Data Solutions): https://bit.ly/HADRMyJobs
Synchronize server-level objects (Powershell cmdlets from DbaTools): https://dbatools.io
Conclusion
Contained availability groups is an excellent step in the right direction. Hopefully, its capabilities will be expanded even further with future versions and updates.
For more details about contained availability groups, you should check out my online webinar:
It was delivered in Hebrew (there's a recording on YouTube), and it will be delivered in English at the upcoming Future Data Driven Summit of 2022 (be sure to register!).