SQL Server 2022 – Contained AGs – Overview

You’ve Asked, It’s Now Here!

The largest complaint, by far, for Availability Groups (AG) has been that system databases could not be part of the AG. This is how mirroring worked, and AGs were an evolution of mirroring, solving many of the problems that mirroring had (although mirroring solved many of the problems log shipping had). Since there are no systems databases, this meant that server levels items which are typically changed quite often such as logins and jobs would not “automagically” flow over to any of the secondary replicas. This is both a good and a bad thing, though many DBAs see it as a bad thing because that means more administrative overhead and a larger chance for these changes to be missed, leading to configuration drift and a nasty 3 am surprise phone call.

SQL server 2022 (SQL Dallas) has added what is called “Contained Availability Groups”, coming as a nice surprise to many DBAs or other admins out there (incidental DBAs, small shops, etc.) which *could* severely remove a large chunk of administrative overhead and challenges. There’s quite a lot to like about them, but like all things in life there are also some drawbacks that one needs to be aware exist, while these are a giant and amazing step forward for AGs, much like any other tool it has its place and time.

What Exactly Are Contained AGs?

Contained AGs are Availability Groups that have a _copy_ of the master and msdb system databases in them, which allow for their own logins and jobs to be automagically created on all other replicas involved in the contained AG. There are specific rules about what is and isn’t copied from these system databases, which means not all logins will be available to all contained AGs. This isn’t by any means a security wall, merely a starting point for a new contained AG. This also means that the logins required are going to need added after creation, which can be more administrative overhead as now one would need to do logins not by a _replica_ level, but by a contained AG level – whether there could easily be more contained AGs than replicas. This will undoubtedly result in more scripting and automation and so as I stated previously, it’s possible this isn’t the silver bullet of lower administration for availability groups.

High Level Wins

These are things that will drive you to use contained AGs:

1. Logins and Jobs are synced automagically for you
2. Can choose to have logins on a per AG basis
3. Allows for easier self-healing and scale out of AGs
4. Logins and Jobs that only the contained AG can use and see

High Level Losses

These are things that you’re just going to either hate or lose the ability to do:

1. Application changes might be required for those *already poorly written* ones, most won’t need it
2. Potential for more administrative overhead for logins and jobs (adding to all contained AGs, for example)
3. Administrator/Application confusion over physical master and logical master when dealing with connections
4. DMVs aren’t security containers and thus may show other data outside the contained AG
5. Getting an overall server view of jobs and outcomes

My Personal Thoughts

I’m excited about this feature and happy that it’s finally here. It’s going to solve many problems for smaller shops or those places that have tons of logins and jobs. It’s going to make some aspects of AGs harder, but that’s why you get paid the big bucks.