Contained Availability Groups in SQL Server 2022 AlwaysOn
Welcome to a new post on the Aleson ITC blog. As many of us know, in SQL Server 2022 there are many new features that we are loving and one of the least known is the ability to manage contained availability groups.
What are Contained Availability Groups?
- A Contained AG is a special type of Always On availability group that is more than replicating just user databases.
- In addition to user databases, it also includes relevant parts of system databases, such as master and msdb.
- Take note of it as the “execution context” for applications that use this availability group.
Why are so important?
- In traditional availability groups, user databases are replicated, but system objects (such as logins, users, permissions, and SQL Agent jobs) are not automatically replicated.
- That means that administrators must manually ensure that any changes to these objects are duplicated across all replicas in the group.
- With Contained AGs, system objects are also automatically replicated.
How do Contained Availability groups work?
What are the big differences between contained high availability groups and regular high availability groups?
Since 2012, we have lived with the complexity of replicating information on the secondary node as we add it to the primary node, jobs, logins, permissions, etc.
In the case of Aleson ITC, we have automated and managed it with our own tool that we call Sync-Objects and that makes use of the versatil dbatools powershell module.
With the implementation of the contained availability groups, we have the facility of being able to directly implement this information within the system databases that are part of the high availability group itself and, therefore, will only be accessible from within the AG itself.
If we connect to the listener of the contained availability group, we will access the information from the master and msdb databases of this availability group.
If we connect directly to the SQL Server instance, we will connect to the generic master or msdb of the server (the usual ones).
Creating content high availability groups
To create a contained high availability group, it would be done in a very simple way with the SQL Server wizard itself.
To begin, click on “Always On High Availability” and select the “New Availability Group Wizard” option:
Once we select this option in the wizard, we name our group “AG-Aleson” and, in the options, we can assign it the “Contained” option, marked in red in the following image.
If we check the “Reuse System Databases” option we would make a copy of the current system databases in our high availability group and it would look like this:
As we see, the system databases are added with the name “AG-Aleson_master” and “AG-Aleson_msdb” automatically. We have only added the database “TESTDB” to our high availability group.
Conclusion:
Since SQL Server provides us with this option intrinsically, we find it highly recommended and useful to use the system databases contained in the AG for the specific creation of the jobs of each Availability Group (in AG-Aleson_msdb) and the creation of groups permissions, logins, etc. (AG-Aleson_master).
With this we have more knowledge about how to get the most of SQL Server 2022. Don’t miss the following news and updates on SQL Server!
Do you need help managing your data? Contact us!
Azure Data Engineer con más de 7 años de experiencia. Conocimiento de múltiples herramientas y enfocado en el mundo del dato. Experto en tuning de queries y mejora de rendimiento de Base de Datos. Profesional apasionado por la tecnología y los deportes al aire libre. En mi tiempo libre, me encontrarás jugando vóley playa o disfrutando con nuevos videojuegos.
