SQL Server 2022 introduced a new feature called Contained Availability Groups. It allows the Database Administrators to effectively manage the Server Level objects, such as Logins, SQL Agent jobs, etc. in an HA environment. In today's article, we will learn about this new feature of SQL Server.

The Challenge of Managing Server Objects in Availability Groups

Always On Availability Groups were first introduced way back in 2012. It was designed to provide a highly available and disaster recovery solution. The problem with normal availability group is that DBA's must manually replicate the server-level objects, such as logins, sql agent jobs or operators on all the replicas whenever they are created.  A Contained AG fixes this problem. In addition to user databases, the contained AG also includes portions of the master and msdb databases. Thus along with user databases, system databases get replicated across all the replicas. This eliminates the need for  DBAs to manually create or update the objects across all the replicas.

Configuring Contained Availability Groups

We will now see how to Create an Always On Contained Availability Groups on a 2-node Windows Cluster. For this example, we will use 2 nodes, ACS-POC-DB01 and ACS-POC-DB02. Both the nodes have a named SQL Server Instance(SQL2022) installed on them. You may refer this article to learn how to install standalone SQL Servers on a Windows Server.

Enable the Always On Feature

Connect to both the Primary and Secondary Instances and open SQL Server 2022 Configuration Manager. Then select the SQL Server(SQL2022) Service properties and Check 'Enable Always On Availability Groups'.

Create a Test Database

Next, connect to the Primary Instance ACS-POC-DB01\SQL2022 and create a test database named 'TestAG'. Take a full backup of the database.

Create a Contained AG

After taking the database backup, right click on Always On Availability Groups option and select 'New Availability Group Wizard'

Provide a name 'SQLAG-2022' for the availability Group and enable the 'Contained' and 'Reuse System Databases' option.

Note: If you enable the 'Reuse System Databases' option, SQL Server replicates the existing master and msdb databases across all nodes. If you don't enable this option, SQL Server creates new master and msdb databases within the availability group, and it does not replicate the existing server-level objects. In this case, only the server-level objects that you create after the availability group is created will be replicated.

Next, add TestAG database to the availability group.

Then, add the secondary replica ACS-POC-DB01\SQL2022 to the availability group from the Add Replica option.

Note: You can leave the default settings for Endpoints, Backup Preferences, and Read-Only Routing, as this article does not cover these features in detail.

Then, create a Listener 'SQL2022Listener' for the availability group and assign an IP address to it.

Finally, perform a validation test, and if all the validations are passed, verify the configuration settings.

Click on Finish to create the Contained Availability Group.

After you create the Availability Group, SQL Server automatically adds two additional system databases—SQLAG-2022_master and SQLAG-2022_msdb—alongside TestAG. You will see these databases on both instances.

Managing Logins in a Contained Availability Group

Let's understand how to create a login and observe it's behavior in a Contained Availability Group. First, create a login in the Primary Instance.

:connect ACS-POC-DB01\SQL2022
USE [master]
GO
CREATE LOGIN [user1] WITH PASSWORD=N'qwerty123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

After running the above commands, you can verify the login by querying the sys.server_principals system view. user1 will appear in the result. However if you connect via Availability Group Listener and run the same query, you won't see user1. This is because contained availability groups maintains it own copy of master database separate from the instance one.

To make the login available in the context of Availability Group (thus replicating it to secondary replica), connect via listener name and create the login.

:connect SQL2022Listener,1434
USE [master]
GO
CREATE LOGIN [user1] WITH PASSWORD=N'qwerty123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Now if you query sys.server_principals, you will see user1.

If you connect to the Secondary Instance, still you won't see user1. This is because user1 exists only within the context of Availability Group and not at Instance-level. To make it available at instance level also, create the login like you created it in the primary instance.

Managing SQL Agent Jobs in Contained Availability Group

Managing jobs in Contained Availability Groups are similar to managing logins. To understand this, first create a SQL Agent job TestAGJob in the Primary Instance. Once the job is created, you can verify it by querying the msdb.dbo.sysjobs system table. You will see a record of it. However if you connect via Availability Group Listener and run the same query, you won't see the job. Again, this is because contained availability groups maintains it own copy of msdb database as well, separate from the instance one.

To make it available in the context of Availability Group, connect via the listener name and create the job.

Conclusion

Contained Availability Groups help DBAs manage server-level objects across replicas in a high availability and disaster recovery (HADR) setup. Previously, DBAs had to manually create logins, SQL Agent jobs, and other objects on each replica. Now, by connecting to the listener and creating these objects, they are automatically available on all replicas. This not only saves time but also ensures consistency across the environment