Overview
One of the more intriguing features introduced in SQL Server 2022 is Contained Availability Groups (AGs). At first glance, they look very similar to traditional AGs — but there are some key differences that can trip you up if you’re not prepared.
In this post, we’ll walk through:
-
What a normal AG looks like (and its limitations)
-
How Contained AGs change the game
-
The pitfalls we’ve seen in real-world use
-
Practical solutions if you run into these issues
A Quick Refresher: Always On Availability Groups
Availability Groups, introduced back in SQL Server 2012, are Microsoft’s built-in solution for high availability and disaster recovery. They let you replicate databases across multiple instances, managed by a Windows Server Failover Cluster (WSFC).
Key benefits:
-
Seamless failover between primary and secondary nodes
-
Read-only replicas to offload workloads
-
The ability to perform backups and DBCC checks on secondaries, easing the load on your primary
But AGs have always had one big limitation: only user databases are replicated. System databases like msdb and master stay local to each node.
Why does that matter? Think about Agent jobs:
-
You have to manually install jobs on every node (and keep them in sync).
-
Jobs need to be AG-aware — otherwise they might try to run against a read-only secondary.
-
Job history lives only on the current primary, which makes tracking a little messy.
For years, DBAs have worked around these issues, but it’s always been a pain point.
*Enter Contained Availability Groups*
Contained AGs look and act like traditional AGs, but with one important twist: they include their own contained system databases.
For example, if you create a Contained AG called MyAg, you’ll see:
-
MyAg_master
-
MyAg_msdb
These are replicated to all nodes in the AG, just like user databases.
Why this matters
-
Agent jobs now live inside the contained msdb. No more duplicating jobs on every node.
-
Job history is replicated. After failover, you don’t lose tracking.
-
System tables travel with the AG. Everything stays consistent across nodes.
That’s a big win for manageability and one of the headline features of SQL Server 2022.
Gotchas Seen in the Wild
As with most “new shiny” features, Contained AGs aren’t a silver bullet. There’s a few things to watch out for:
-
No more job offloading to secondaries. Jobs inside the contained msdb only run on the primary. If you used secondaries to handle CHECKDB or backups, you’ll need to rethink.
-
Replication is not supported. If transactional replication is part of your design, contained AGs may not be an option.
-
Seeding is clunky. The GUI doesn’t support manual seeding yet. You’ll need to fall back to T-SQL.
-
No distributed AG support. If you rely on DAGs for hybrid or geo-distributed HA, contained AGs won’t help.
-
Confusion with system databases. When you connect to the listener, the msdb and master you see belong to the contained AG — not the local system copies.
Workarounds & Solutions
For Agent Jobs
If you truly need jobs to run on a secondary node, you still can — but you’ll have to install them in the local msdb outside the contained AG. That means:
-
Jobs must be AG-aware again
-
You’ll have to maintain them separately
It’s not elegant, but it gets the job done.
For System Databases
This is case-by-case. If your code doesn’t touch master or msdb, you’re fine. But if you do, you’ll need to carefully evaluate whether those objects belong in the contained copies or the traditional system DBs.
Remember: you can query both contained and local msdbs — just make sure your scripts account for both.
Final Word
Contained Availability Groups solve one of the long-standing headaches of AGs — managing Agent jobs across nodes. But they come with trade-offs that every DBA needs to be aware of.
Test carefully. Know where your jobs live. And make sure your disaster recovery plan accounts for these differences.
If you want to see them in action, check out our webinar on SQL Server 2022 features, where we demo Contained AGs in detail.
