Contained Availability Groups (CAGs) in SQL Server 2022 allow you to create an availability group that is independent of the instance, separating logins, jobs, and database mail from the main SQL Server instance. This is particularly useful when you want to create an availability group for a specific application while keeping its authentication and configurations isolated.
With Contained Availability Groups, a copy of the master and msdb databases is maintained within the group, ensuring that logins, jobs, and database mail settings are unique to the availability group rather than shared with the instance.
Key Features of Contained Availability Groups:
- Multi-Tenant Support – Ideal for database-as-a-service (DBaaS) environments, allowing customers to have isolated authentication and jobs.
- Automated Login Synchronization – No need for manual login synchronization, as the master database within the group is automatically replicated.
- Dedicated Always On Environments – Enables separate high-availability environments for different applications on the same SQL Server instance.
Contained Availability Groups are exclusively available in SQL Server 2022.
you also need ssms 19 and above
Configured Contained Availability Group
on this lab we have already setup normal Availability Group that setup for database StackOverflow2010 , we will create separate contained Availability Group for database AdventureWorks2008R2
to start right click on Availability Groups and select New Availability Group Wizard
on next window give friendly unique name to availability group and check mark
remaining steps are the same to regular availability group setup which consist of adding replica , configure listener with dns name , select way you want to sync database with replica
once setup is completed you notice that Availability Groups icon is changed
additionally if you expand Availability Databases you notice that master database and msdb copy is also added
with both copy of master and msdb you will get separate separate database for login and job for this AG that is separated from instance .
contained AG limitation
- jobs can only run from primary nodes of the AG
- contained AG doesn’t support replication
- Backups of contained databases are portable, but restoring them outside the AG requires ensuring containment settings and users are preserved. This can lead to mismatches if not carefully managed.