Problem
In a previous tip on Orphaned User Detection for SQL Server High Availability Databases, we have seen how to identify when there are orphaned database users in an Availability Group database. Is there a way where we no longer need to worry about missing database users or different SID values between primary and secondary databases in an Availability Group?
Solution
Almost all SQL Server database availability features – database mirroring, log shipping, Availability Groups – require that you recreate server-level objects like logins and SQL Server Agent jobs on the standby server. That’s because only the databases with their corresponding objects and data get replicated to a standby server for availability. Logins and SQL Server Agent jobs are server-level objects. You have to manually recreate them on the corresponding standby servers to make sure that it’s business as usual when a failover happens. If you don’t, applications will not be able to connect to the standby server.
There’s also a proper way of recreating the server-level objects on the standby server. You cannot just go ahead and recreate the same login with the same password and permissions. You need to make sure that the SID values remain the same. Otherwise, you’ll end up with orphaned database users. That’s what the tip on Orphaned User Detection for SQL Server High Availability Databases was trying to address. And we haven’t even considered the SQL Server Agent-related objects like jobs, alerts, and operators.
The way this was dealt with in the past was to have a proper change management process when configuring database availability for SQL Server. Whatever gets created on the primary database server needs to be replicated to the secondary database server. And this goes beyond creating server-level objects. The same approach needs to be done for removing them. That’s because they are potential security risks. Imagine deleting a login on the primary database server but not on the secondary database server. Anyone who has access to the login credentials can access the secondary database server.
What if we no longer have to worry about server-level objects when configuring high-availability solutions for SQL Server databases?
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:
- From within Object Explorer, expand the Always On High Availability node and the Availability Groups node.
- Right-click the Availability Group node and select the New Availability Group Wizard option. This opens the New Availability Group Wizard dialog box.
- 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.
- In the Select Databases dialog box, select the database that you want to include in your Contained Availability Group. Click Next.
- In the Specify Replicas dialog box,
- 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)
- 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:
- In the Endpoints tab, verify that the Port Number value is 5022.
- 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.
- In the Select Initial Data Synchronization page, select the Automatic seeding option if you have relatively small databases. Click Next.
- In the Validation page, verify that all validation checks return successful results. Click Next.
- In the Summary page, verify all configuration settings and click Finish. This will create and configure the Contained Availability Group and join the databases.
- 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.
Next Steps
- Evaluate Contained Availability Groups in SQL Server 2022
- Read about SQL Server 2022 Contained Availability Groups from the Microsoft Documentation page
