Contained Availability Groups in SQL Server 2022

Microsoft announced the public preview of SQL Server 2022 the other day. Along with it comes SSMS 19 Preview 2 which you will need as well. There is no set release date for SQL Server 2022 but with the public preview, we are definitely closer.

One of the features in that announcement is why I am writing this blog post: contained availability groups, yet another AG variant.

For those among you who will call them CAGs, it is not the official abbreviation – it is contained AG. I get it is easier to say or type CAG. Don’t do it unless you want the Allan stink eye (I can already see the memes …).

A Bit of History

The idea of containment is not new to SQL Server. Anyone remember contained databases (or as I like to call them, partially contained databases)? These have been around since SQL Server 2012 but very few have implemented them in the wild. It was a nice concept that basically died on the vine.

Just under two years ago, I got an e-mail from Kevin Farlee (Twitter) asking if I wanted to participate in a private preview for contained AGs. I could not reply fast enough. I played with it and provided some feedback. As is the way these things go, things were silent for a long time. Fast forward to a few months ago when I learned that the contained AG feature was alive and well. It has been very hard for me to not say anything. Kudos to Kevin for pushing this forward and finally getting it into the product.

It shouldn’t have to be said, but don’t bug Kevin. I am fortunate enough to have this type of NDA relationship as well as the trust of both the SQL Server and Windows dev teams. I do what I can behind the scenes to hopefully assist Microsoft to develop better products and experiences for those of you reading this before you even see these features. Some of the features I evaulate or provide feedback on never see the light of day and I will take them to the grave.

Why Do We Need a Contained Availability Group?

All SQL Server availability features except Always On Failover Cluster Instances (FCIs) have a “problem”: when a secondary replica/warm standby/mirror (the term is different for each feature …) takes over as the new boss, some items such as SQL Server Agent jobs, instance level logins, etc., are not there. Going back to the early days of SQL Server when log shipping was not even in the product, this was always a manual process. There are multiple ways to approach this challenge and I am not going to detail them. This “problem” is a longstanding pain point with those who are responsible for managing SQL Server.

Contained AGs solve this issue by having their own master and msdb databases synchronized as part of the AG mechanism.

A Look at Contained Availability Groups

DISCLAIMER All screen shots here are using SSMS 19 Preview 2. Things may change so if what you are seeing in the future is different, that is to be expected.

Contained AGs is not an Enterprise Edition only feature; it will work in Standard Edition (nee Basic AGs) as well.

Right now you can create a contained AG with SSMS or Transact-SQL. In the Availability Group Wizard, Figure 1 shows you two new options.

Figure 1. Contained database options in SSMS

For a new AG, you would only have to select Contained. More on the second option in #4 of the gotchas section.

After the contained AG is created, you get two additional databases per AG

AGNAME_master
AGNAME_msdb

Figure 2 shows you what the contained databases look like in SSMS.

Figure 2. Contained databases

Figure 3 shows you what a contained database looks like on the AG properties dialog. Note that Contained is greyed out and not selectable. See #1 of the gotchas section for more details.

Figure 3. Contained AG properties

You can also see that the AG is contained using the DMV sys.availability_groups as shown in Figure 4 with the new column is_contained.

Figure 4. New is_contained column

Create Contained Objects

The contained system databases start out empty except for master which has the administation accounts. That means if you have a lot of logins, jobs, linked servers, etc., they do not automatically move into the contained system databases. You need to create them. How?

Connect to the contained AG as an administrator.
Switch to the contained master or msdb.
Create the object.

The example in Figure 5 creates a login in the contained master database for the MRROBOTO AG and then its corresponding user in the database ContainedAGDB1.

Figure 5. Creating a contained login and the corresponding user in the database

Connect to a Contained Availability Group

Using a contained AG is all about context. If you connect to the instance directly, you only see and can use what is in the instance’s master and msdb databases. Heed gotcha #7 in the next section. Figure 6 shows a connection to a default instance named DENNIS using sqlcmd.

Figure 6. Logins in the instance

If you connect to the contained AG as an administrator, Figure 7 connects to a listener named DOMOARIGATO and shows that in that context, you see a very different set of logins from what was shown in Figure 6. Note JChance that was created earlier is listed.

Figure 7. Contained logins

From an application perspective, you can log into the contained AG via the listener with a contained login as shown in Figure 8. JChance gets right in. The syntax used for sqlcmd is:

sqlcmd -Slistenername -Uuserincontaineddatabase -Ppassword

Figure 8. Connect to Listener with Contained User

You can also connect using a combination of a contained user, one of the databases in the contained AG, and the instance (not listener) name. An example is shown in Figure 9 using sqlcmd. The syntax is:

sqlcmd -Sinstancename -Uuserincontaineddatabase -Ppassword -ddatabaseincontainedag

Figure 9. Connecting to a contained database properly

Currently Known Contained Availability Group Gotchas

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.

My Thoughts

I see contained AGs as a compelling reason to consider upgrading to SQL Server 2022 when it is released. In my opinion, the contained AG feature is the most significant improvement to AGs since the introduction of distributed AGs in SQL Server 2016.

Will contained AGs create world peace? No. Does it fix the external objects outside the database problem for features like log shipping? No; that is still a manual process and a pain point. Does this feature solve an issue that should have been solved with the introduction of AGs in SQL Server 2012 especially for scenarios like automatic failover? Absolutely. Can the feature and its tooling use some improvement? Sure.

The limitation around distributed AGs is more of an understandable disappointment than a dealbreaker for me. The value of this feature is too high not to recommend people kicking the tires on it. I also love distributed AGs so it’s a hard call.

What are your thoughts about contained AGs? Do you have questions about this new feature? Let me know below.

February 23, 2021

WARNING: Distributed Network Names and Distributed Availability Groups

Distributed Network Names (DNNs) are a relatively new Windows Server Failover Cluster (WSFC) concept. Up in Azure, DNNs effectively eliminate the need for an internal load balancer (ILB). The ILB allows applications and end users to be able to connect to an AG’s listener or the FCI after failing over to another Iaas VM. You should not really be using DNNs for any on premises deployments.

DNNs are supported as of SQL Server 2019 CU2 and require Windows Server 2016 or later. I wrote more about them in my blog post Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs. Go there if you want to see what they look like and learn more.

Right now, I cannot wholeheartedly recommend the use of DNNs for listeners or FCIs if you are using Enterprise Edition. Why?

DNNs do not work with a distributed AG which is an Enterprise Edition only feature. A distributed AG is something nearly many customers who have Enterprise Edition implement either for disaster recovery andor migration. If you want to use a distributed AG, you will need an ILB for the underlying AGs and/or FCIs. As far as I know, this is not officially documented anywhere on Microsoft’s site. I did confirm this limitation of DNNs with them.

If you use Standard Edition or think you will never use a distributed AG, you can use a DNN for AGs and FCIs in Azure. For D/R across regions (or on premises to Azure), that means deploying a stretched WSFC which is inherently a more complex architecture or using something like log shipping.

If anything changes, I’ll be sure to update this blog post.

In the meantime, if you would like Microsoft to add support for distributed AGs with DNNs, go add your vote over at Uservoice.

July 1, 2020

Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs

Over the past month or two, I’ve seen a lot of people run into a problem with Azure-based IaaS configurations that isn’t well documented and want to address it in this blog post. For those creating Always On Availability Groups (AGs) or Always On Failover Cluster Instances (FCIs) with a Windows Server Failover Cluster (WSFC) using Windows Server 2019 as the underlying OS, things have changed. There are other “problems”, but I’m specifically going to address one pain point: the distributed network name (DNN).

Introduced in Windows Server 2016, a DNN is another kind of name resource in a WSFC. It differs from a standard network name resource because it does not require an IP address. In Windows Server 2019, the name of the WSFC, which is also the Cluster Name Object (CNO) in Active Directory Domain Services (AD DS), can be created with a DNN. A DNN cannot be used to create the name resource for an FCI or an AG’s listener.

Figure 1 shows what a DNN looks like in Failover Cluster Manager (FCM).

Figure 1. Distributed network name in FCM

The new Windows Server 2019 DNN functionality does have a side effect that does affect Azure-based configurations. When creating a WSFC, Windows Server 2019 detects that the VM is running in Azure and will use a DNN for the WSFC name. This is the default behavior.

FCI Behavior

During Setup, you’ll get to the step cluster_failover_cluster_name_cluster_config_Cpu64 shown in Figure 3 where it’s configuring the network name resource.

Figure 3. Making progress, right?

Unfortunately, you will also get the error message in Figure 4 pop up. This error is fatal – you can’t create the FCI.

Figure 4. Not so fast …

The fix? Recreate the WSFC without a DNN. Before you do that, make sure you uninstall SQL Server cleanly otherwise that could be unpleasant.

Listener Behavior

With both SQL Server 2017 and 2019, creating a listener when the WSFC uses a DNN seems to work. I have no reason to believe it will not work with SQL Server 2016. However, there’s no official support statement from Microsoft on using the DNN-based WSFC with AGs, nor do I believe it was tested with them. Just because I have not encountered issues in my limited testing does not mean it is good or bad – I’m just relating my experience.

The Core of the Problem with Windows Server 2019

If you require a static IP address for a WSFC in Azure, the only way to create it is with PowerShell. This is the typical syntax for an AG. For an FCI, you would want storage, so leave out the -NoStorage parameter.

[code]New-Cluster -Name WSFCName -Node nodelist -StaticAddress IPAddress -NoStorage -AdministrativeAccessPoint DNS[/code]

Figure 5 shows the output of the command

[code]Get-ClusterGroup “Cluster Group” | Get-ClusterResource[/code]

The WSFC creation process creates a WSFC with a DNN despite specifcying a name and IP address. The value for resource type is Distributed Network Name not Distributed Server Name as shown back in Figure 1. Once again, as with group/role, things are different in the UI vs. PowerShell. Get your act together, Windows!

Figure 5. It didn’t create what you thought it would

How Do You Create the WSFC without a DNN?

The answer is in this obscure blog post from Microsoft. That blog post links to a bunch of Youtube videos created by John Marlin who is on the HAS team of Windows Server. What you need is buried in Part 6. It’s a new switch/parameter in the New-Cluster PowerShell cmdlet called ManagementPointTypeNetwork that is not even documented there. I created a pull request to update the documentation yesterday.

ManagementPointTypeNetwork can have one of three values: Automatic (the default value, which detects if you are on premises or using another cloud provider, or you are doing this in Azure), Singleton (use a traditional WSFC name and IP address), and Distributed (use a distributed network name for the WSFC name).

To create a WSFC using a static IP address and a traditional WSFC name and IP address with no shared storage, here’s the syntax:

[code]New-Cluster -Name WSFCName -Node nodelist -StaticAddress IPAddress -NoStorage -AdministrativeAccessPoint DNS -ManagementPointTypeNetwork Singleton[/code]

Figure 6 shows the output which is what we want to see – a traditional WSFC with a name and an IP address.

Figure 6. Much better

You also get a much more familiar display in FCM. I really with Microsoft would not use Server Name (or Distributed Server Name) under Core Cluster Resources since you don’t really access the WSFC directly. I get why it needs a name, but wouldn’t WSFC Name or Distributed WSFC Name be better here?

Figure 7. Traditional WSFC with a name and IP address

Postscript

Before anyone sends nastygrams or leaves comments, I was just showing the WSFC creation. There are more steps. You still need to add a witness resource, which up in Azure should be cloud witness. You may also need to create an ILB (or use an existing one) depending on your configuration. Both of those are outside the scope of what I’m covering in this post.

It’s my hope that the SQL Server dev team officially tests and certifies DNNs for use with AGs and FCIs. FCIs appears to require a fix to Setup. That means FCIs may not work until a CU or possibly vNext (if at all).

Update February 2021

Check out my blog post WARNING: Distributed Network Names and Distributed Availability Groups for some new and relevant information.

The TL;DR

Using a DNN right now may work for you depending on what you are deploying (AG or FCI). No matter what path you take, at least you now know how to deploy things. Happy clustering!

September 5, 2019

Availability Group Support For Containers Not Shipping with SQL Server 2019 RTM

This one is going to be a short post. One of the big capabilities of SQL Server 2019 – the ability to deploy AGs using containers – is not shipping when SQL Server 2019 is released. My guess is it it will be enabled in a CU sometime after its release. From the RC1 release notes:

The Kubernetes operator for Always On Availability Groups is not supported in this release candidate and will not be available at RTM.

I’ll update this blog post when it is lit up in SQL Server 2019.

June 12, 2019

Supporting the Future – Win a Seat to the Availability Groups Boot Camp in August

Giving back is intrinsic to SQLHA’s values. Nearly every time we offer public training, we’ve given away a seat in at least one class. In 2016, it was WIT and we saluted the troops in 2017. The upcoming classes in Chicago in August are no exception. This time, we’re giving away one seat for the Always On Availability Groups Boot Camp from August 7 – 9.

For this giveaway, we’re focusing on the future. If you are between the ages of 18 and 25, a high school senior, currently enrolled in college, or just graduated college within the last six months, you are eligible to be in the running for that free seat. This is a very unique opportunity to get real world skills at a young age, so good luck to all entrants.

The Rules

You have to be able to prove that you are at least 18 years of age but no older than 25, a high school senior, currently enrolled in college, or just graduated college in the last six (6) months.
Send an e-mail to sales at sqlha dot com with the subject Give Me Real World Education and tell us why you think you deserve the seat and how it would impact you. You really have to demonstrate that you truly would benefit from attending.You do not have to send a tome, but one or two lines won’t cut it either. The grammar police won’t hold it against you if your e-mail is not up to snuff; we prefer heartfelt over perfect. Having said that, see #10 of The Fine Print. There is one exception.
Entries must be in by Friday, June 28, at 5PM Eastern. A winner will be chosen and notified by Friday, July 5.
Do not make or send a video, write a Word document, etc.; that will disqualify you. This should be e-mail only.

The Fine Print

One entry per person.
Winners will not be eligible for a free seat in a future class and are ineligible for winning any other free SQLHA LLC giveaway for 12 months after winning the seat in the class (excluding any giveaways in the class). If you cannot attend the class where you are chosen as a winner, you forfeit the prize.
Do not enter if you cannot attend; it is not fair to those who can and a waste of everyone’s time.
You have to get yourself to the class. That means you are responsible for all travel and expenses including, but not limited to: airfare, taxis, food, hotel, and so on. If you cannot meet this obligation for the class, please don’t enter. We’re just providing the class itself.
Entries without the proper subject will be disqualified. Sorry.
While we do not have delicate sensibilities, keep your entries clean.
You are responsible for any taxes you may need to pay as a result of winning this contest.
You must be eligible to win. For example, some who work in certain jobs or roles would be ineligible. Know if you can before you enter.
All entries must be in English.
While we understand that writing is not everyone’s forte, anyone who uses text speak such as ur will be disqualified as well. We have to have some standards, you know.

Not between the ages of 18 and 25 and still in or just out of school?

We have a few seats left in each class, so don’t miss your opportunity. Use the discount code GIVE20 to get 20% off of the Chicago classes. Don’t miss out!

Why SQLHA for your training needs?

Show your boss this. We offer the best in person training which includes labs. Keeping our class sizes smaller, our classes have great interaction. Between the labs and being in the room with one of the world’s recognized experts for availability on SQL Server, it doesn’t get much better.

12 3 4 Next ›Last »