What is not contained in contained Availability Groups

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:

https://docs.microsoft.com/sql/database-engine/availability-groups/windows/contained-availability-groups-overview?view=sql-server-ver16

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