Configure Contained Availability Groups for High Availability in SQL Server 2022

Introducing Contained Availability Groups

SQL Server 2022 introduced the concept of Contained Availability Groups. You can think of this as a special type of Availability Group that takes care of server-level objects that databases in an Availability Group need in order to stay consistent across all replicas. That could be a login or a SQL Server Agent job. Using Contained Availability Groups eliminates the hassle of recreating (and removing) server-level objects on all of your secondary replicas.

Since logins and SQL Server Agent jobs are created in the master and msdb databases, respectively, Contained Availability Groups will have their own copies of master and msdb. The server-level objects that exist within the Contained Availability Groups are limited within its boundaries. What that means is only the logins and SQL Server Agent jobs created within the context of the Contained Availability Groups will be created in those system databases.

Configuring Contained Availability Groups

The prerequisites for configuring Contained Availability Groups are the same as with traditional Availability Groups configured for high availability. For this tip, the Windows Server Failover Cluster has already been created and the Always On High Availability feature enabled on the instances that will be used as replicas. Configuring Contained Availability Groups for high availability is also similar to configuring a traditional Availability Group as described in this tip. However, you need SQL Server Management Studio v19.0 and higher if you want to take advantage of the graphical user interface. If you're using an older version of SQL Server Management Studio, the only option is to use T-SQL.

The environment used in this tip is as follows:

• A 2-node WSFC joined to an Active Directory domain
• Uses a file share as a witness type
• SQL Server 2022 RTM (16.0.1000.6)
• SQL Server uses Active Directory domain accounts as service accounts
• SQL Server Management Studio v19.0 Preview 3

Launch the New Availability Group Wizard to create the Contained Availability Group:

1. From within Object Explorer, expand the Always On High Availability node and the Availability Groups node.
2. Right-click the Availability Group node and select the New Availability Group Wizard option. This opens the New Availability Group Wizard dialog box.

 

3. In the Specify Availability Group Options dialog box, type the name of the Availability Group in the Availability group name: textbox.

Notice the new Contained checkbox. This tells SQL Server that you will be creating a Contained Availability Group. Make sure you check this box before moving forward. Otherwise, it will the same as a traditional Availability Group.

The Reuse System Databases checkbox will only matter if you want to reuse existing system databases for a previous Contained Availability Group of the same name. Because you can create multiple Availability Groups within a SQL Server instance, each one will have its own system databases.

Click Next.

4. In the Select Databases dialog box, select the database that you want to include in your Contained Availability Group. Click Next.

5. In the Specify Replicas dialog box,

a.                   In the Replicas tab, click on the Add Replica button to add the SQL Server instance that you want to configure as a replica. Configure the following options:

§  Automatic Failover (Up to 5) : Checked

§  Availability Mode: Synchronous Commit

§  Readable Secondary: (only if you want the replicas as a readable secondary)

2. In the Endpoints tab, verify that the Port Number value is 5022.

3. In the Listener tab, select the Create an availability group listener option. Proceed to create the Listener name.
• Select the Create an availability group listener option
• Type the Listener DNS name and Port number
• Select Static IP in the Network Mode: drop-down list
• Provide the virtual IP address by clicking the Add … button

Click Next.

NOTE: In addition to providing a seamless failover experience for client applications, the listener name now becomes the entry point for creating server-level objects within the scope of the Contained Availability Group. If you don't connect to the Contained Availability Group using the listener name, the server-level objects will only be created on the SQL Server instance and not get replicated. I will cover this in more detail in future tips.

6. In the Select Initial Data Synchronization page, select the Automatic seeding option if you have relatively small databases. Click Next.

7. In the Validation page, verify that all validation checks return successful results. Click Next.

8. In the Summary page, verify all configuration settings and click Finish. This will create and configure the Contained Availability Group and join the databases.

9. In the Results page, verify that all tasks have been completed successfully.

Exploring the Contained Availability Group

After the Contained Availability Group is created, you should see two additional databases. These get created in every Contained Availability Group. The databases are named <AGName_master> and <AGName_msdb>.

It can become confusing when you start to have multiple Contained Availability Groups within a single SQL Server instance. Make sure you document the configuration so you know which databases are joined to their corresponding Contained Availability Group. This is a recommended best practice when you have multiple Availability Groups, regardless of the type.

When you open the properties dialog box of the Contained Availability Group, you will notice a few things. First, the Availability Group name includes (contained). This is to differentiate it from traditional Availability Groups. Next is the Contained checkbox. Notice that it is disabled. This means that you will not be able to change an Availability Group from contained to the traditional one and vice versa. The only way to do this is to delete the Availability Group and recreate it which would require downtime even if the databases will not be taken offline. Proper planning should be done if you decide to configure Contained Availability Groups.

Exploring SQL Server Logins

Not all SQL Server logins will be copied to the <AGName_master>database. The only logins that get copied are the sysadmin accounts. This is to allow admins to continue with other configurations needed for both the Contained Availability Group and other replicas. If there are existing SQL Server logins that need to access the databases in the Contained Availability Groups, they have to be recreated while connected to the listener name. Refer to the screenshot below for the following logins:

• The domain account TESTDOMAIN\esarmientoDBA is a member of the sysadmin role and the one who created the Contained Availability Group. This login is automatically copied to the <AGName_master> database.
• The domain account TESTDOMAIN\gorwellDBA is a member of the public role. But since the login was created in the context of the SQL Server instance TDPRD011, it is only visible in that instance and not replicated to other replicas.
• The domain account TESTDOMAIN\tlasso is also a member of the public role. But since the login was created in the context of the Contained Availability Group and connected via the listener name TDPRDSQLAGLN16, it is only created in the <AGName_master> database, not the instance's master database. As a result, it gets replicated to other secondary replicas.

NOTE: Adding individual domain accounts is only used for demonstration purposes. The best practice is to create a domain security group in Active Directory and add domain accounts to the group. This domain security group is what is added to SQL Server as a login.

Exploring SQL Server Agent Jobs

Unlike logins that are members of the sysadmin role, no SQL Server Agent job is copied to the <AGName_msdb> database. If you have existing SQL Server Agent jobs that you want to replicate across all the replicas, you have to recreate them in the context of the Contained Availability Group, logging in to the SQL Server instance using the listener name. In the screenshot below, the SQL Server Agent job named Backup Databases is only created in the <AGName_msdb> database, not the instance's msdb database. This job gets replicated on all replicas. In comparison, there are more jobs in the TDPRD011 instance. This is because they were created in the context of that instance. And because they were not added to the <AGName_msdb> database, they will not get replicated on all replicas.

Summary

We are just scratching the surface of what Contained Availability Groups are capable of. As you can see, this greatly reduces the effort of replicating SQL Server logins and jobs on all replicas. In future tips, we will explore managing Contained Availability Groups.

Contained Availability group and SQL Server 2022 features

• The Contained Availability group database does not support Replication, Distributed availability groups, and Log shipping with a target DB in the contained availability group.
• It supports Log shipping with the source database in the contained availability group is supported.
• You can implement Change Data Capture after connecting to the contained availability group listener.
• You can implement transparent data encryption (TDE) with databases in a contained availability group. To use it, manually install the Database Master Key (DMK) to the new contained master database within the contained availability group.
• You can query SYS.AVAILABILITY_GROUPS to determine whether an availability group is contained or not using column IS_CONTAINED.

Currently Known Contained Availability Group

§  An AG can be configured as either contained or not contained (i.e. “regular”). You cannot change an AG’s configuration from not contained to contained or contained to not contained after it is created. To do so, destroy the AG and recreate it the other way. I do not see this ever being changed.

§  Contained AGs are not currently supported with distributed AGs. I can think of a few reasons why it would break contained AGs. That means if you rely on distributed AGs today, contained AGs may not be in your future if you want to continue that strategy. My hope is that both contained AGs and distributed AGs work together. If I had to guess, I think it would happen after SQL Server 2022 is released and possibly another major version. I plan on doing some testing to see what does/does not work and if there are workarounds.

§  Contained AGs also do not currently work with replication. If you need replication, you may not be able to use a contained AG.

§  Contained AGs assume automatic seeding for the AG’s databases including the contained system ones. If you want to create databases via manually seeding (i.e. backup/copy/restore WITH NORECOVERY), use Transact-SQL. The creation process for a contained AG with manual seeding is not documented yet.

§  This feature  is SQL Server 2022 only. I do not see this being backported to SQL Server 2019. See #1 for implications in an upgrade.

§  If you delete the contained AG, it does not delete the contained system databases. If you want to create another AG with the same name, you can choose to reuse those contained system databases already created. That is the second check mark in Figure 1. Otherwise, you will need to manually delete them from all replicas.

§  A contained AG is not a security boundary. You can still potentially see everything else in the instance (including the real master and msdb) if you have the access. 

§  If you do not have a baseline knowledge (not expert) to be able to crate objects not using SSMS, right now contained AGs would be a challenge for you to implement. I am hoping that changes at some point.