Why you should use SQL Server contained availability groups to save time – and why consultants may not tell you about them

Admittedly, the title is a bit provocative—but considering how underappreciated contained availability groups are, a little boldness feels justified. 🙂

So, why exactly do we at Sarpedon Quality Lab recommend their use?
Our customers—whether in banking, healthcare, or other critical industries—demand the highest standards of availability and security, and rightfully so. Meeting those standards requires our full attention. Using available ways to simplify operations or eliminate security and reliability risks is a no-brainer. Contained availability groups check all these boxes and save significant time, both in the short term and over the long run, ultimately reducing costs.

The concept of contained availability groups

The fundamental idea behind contained availability groups (I will abbreviate it to contained AGs or CAGs in his article), as opposed to the non-contained type, is that a CAG fails over with all the required objects that are necessary for the applications to continue to work on the new host – without requiring additional steps by the DBAs to replicate server-level objects to all available hosts in advance.

That’s it. In theory, all you need to do is set up the availability group as contained, add the databases, and then start using it.
No need to copy logins, jobs, schedules, and other server-level objects to all the involved nodes via some routines.
And this is by far the most common issue when using the traditional availability groups: at some point, someone will forget that Logins cannot just be created at any given host without following a process for proper deployment everywhere. Same for new jobs, altering job definitions, schedules, and so on. Over time, unless the environment is tightly controlled, disparities will occur. And when the failover happens, things stop working, users can’t connect, jobs behave differently, and the search for the culprit starts.

This is precisely where CAGs save you time and therefore money: you do not need to do any of that.

The reason is that SQL Server also replicates the master database and the msdb database for the CAG. To be more precise, each CAG comes with its own dedicated master and msdb databases which is then part of the ongoing synchronization.

This per-CAG system database concept means that server-objects in CAG1 will not be visible in CAG2 and not even on the host – unless you look into the specially created contained master and msdb databases, which carry the name of the AG as a prefix like CAG1_master and CAG1_msdb.

One of the most-asked questions for SQL Server over 2 decades has been around limiting visibility of individual databases. – Contained Availability Groups solve that!

Solution: Logins that reside in a contained availability group must connect to the CAG and can only see the databases that are part of the CAG. (unless they have sysadmin-level privileges)

How is that?

It’s quite simple: if you design your contained availability groups to contain only the databases needed for respective applications, then the logins that have permission to connect to “their” CAG will only see the databases that belong to the CAG and no others.

With contained availability groups, you can combine SQL Server instances on a shared server and still make each database application feel like it’s running alone on the instance.

The following image shows which databases are visible when a login connects to either the host or a CAG.

ContainedAvailabilityGroup_Databases

The same concept applies to all other server-level objects.

Note: There is no security guarantee around the limitation to visible databases. Members of the sysadmin server role can always connect to any database.

Deployment-considerations for contained AGs

When you use contained AGs, you need to connect to it using the listener name (a listener is a requirement for a CAG).

Once connected to the Contained Availability Group (CAG), you can create logins, jobs, and other objects directly through that connection. These will become part of the availability group, ensuring that after a failover, everything remains accessible—regardless of which replica you connect to.

There is one important exception: The databases.

Databases can only be created on the hosting instance, including restores. – You can bckup databases from a CAG-connection, but restore can only happen at the host-level. (and restoring a replicated database requires it to be removed from an AG anyway)

Feature request: While it may seem logical, this is the one feature I truly hope the product team will add: the ability to create databases directly through a contained availability group or listener connection.
Please vote for this functionality to be included here: Feature request: Allow creation and restore of databases in contained availability group

Until that is supported, it is important to consider this process for new database-application deployments, which is very different from using non-contained AGs

The application databases must be created on the host first.
Then in a second step, you need to move the databases to the contained AG which you create.
And then you need to inform the application that it needs to use a different DNS-name, this time to connect to the CAG-listener instead of the host.

Tip: Use a DNS alias for all your SQL Server connections—it can save you countless hours and headaches over the lifetime of your environment.

A special callout for security Auditing

Continuous Auditing should be implemented at every SQL Server installation, and when using CAGs, remember that Server Audits, just as Logins are stored in the master database. Therefore, you need to roll out your Auditing in every CAG, not just on the host!

Administering contained AGs

Contained AGs save you a lot of time since you do not need to develop or maintain additional routines for synchronizing server-level objects.
As with any new technology, it’s important that your DBA team understands the distinctions between contained and non-contained availability groups. The key difference is that to get the full picture of your environment, you need to connect to each contained AG in addition to the hosting instance. It’s somewhat like a virtual instance running on top of the main SQL Server instance, where each CAG maintains its own set of logins, jobs, and other configurations

Tip: Use the Central Management Servers functionality in Management Studio and make sure to add a connection to every contained AG Listener in addition to the SQL Server Instances themselves.
Further reading: Administer multiple servers using Central Management Servers

List of issues and bugs around contained AGs

As much as I appreciate this feature, my colleagues at Sarpedon Quality Lab came across a few bugs that are worth mentioning. None of them prevented me from using the feature, but you need to be aware to make sure it does not affect you. Please upvote them so Microsoft prioritizes fixing them:

I hope I have made you curious about contained AGs. Make sure to understand the documentation, which is just a single page currently, available here: Books Online: What is a contained availability group?

And fellow MCM, Edwin Sarmiento, has published a blog with some nice screenshots where you can see the configuration here: SQL Server Contained Availability Groups Configuration

Talk to us if you want to learn more ways to make your environment more efficient.

Andreas

0 replies

Want to join the discussion?
Feel free to contribute!