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 4, 2020

Designing and Implementing Robust Solutions

No matter where you live, you have most likely heard about the Iowa Caucus fiasco here in the USA this week. This is not going to be a political post, but what happened is something I have seen too many times in all my years working both on the dev and IT sides.

There are numerous topics that could be discussed in greater detail – the failure of redundancy (the availability guy in me is screaming to talk about this), the lack of testing (my QA background is also yelling loudly), but all of this points to a larger culprit: designing the solution properly to have the performance, availability, reliability, scalability, and security required. Do not forget the solution has to be usable by end users and easily maintained not to mentioned tested. The word “robust” is a good single word to sum it all up. According to this 9to5Mac post that has a good, concise summary of what is known to date (and a link to a bigger New York Times article), most of that didn’t happen or just flat out failed. Then there is accounting for data inconsistencies, which according to the New York Times, is a coding problem in the app. ArsTechnica also did a good writeup.

The Iowa Democratic Party issued a statement. It highlights ALL of what I said above, but especially around testing. The fact the data collected was right but the reporting was incorrect is damning. As a data person that makes me angry. Did the reports actually meet requirements and did they test with real (or at least simulated real) data? So many things …

You may ascertain this is not my first rodeo. Whether you buy a packaged application, roll your own, or customize an off-the-shelf package, what worked then may not work now. There’s a difference in scale with few users or a very different workload. How you handle 100 rows is very different than millions or billions. That is not necessarily a failure of design because at the time many applications were designed with what was known. Sometimes things grow organically. Other times some aspects are overlooked with the “we’ll deal with that later” mentality. It is always better to get it right (or as close to it …) as possible the first time around. Retrofitting, or worse, replacing existing solutions is expensive in every aspect of the word and may cause outages to do so. Hire smart people (FTEs or consultants) to help you. It is not a sign of weakness to admit you may not know what you don’t know and you need assistance. What does right look like? It would include, but is not limited to:

Proper application design
Proper schema design
Accounting for RTOs and RPOs for all components
Testing using production (or close to production-like) data and masked if necessary – functional, at scale (works on my machine with no load is not testing at scale …), and full business continuity
Securing everything but not hampering functionality and scalability while doing so (if possible)
A monitoring and overall management strategy that works including backup and restore
Take any lessons learned and drive them into improvements

All of this is valid whether you are fully on premises (physical or virtual), in a public cloud, or using a hybrid which bridges both. Choice of technology or platform does not matter. All of the fundamental principles are the same. Crap in, crap out. The cloud will not fix your poorly designed application nor will throwing more resources often resolve all – if any – issues. At some point you need to take a holistic approach; touching one thing has tentacles elsewhere. It is rare that is not the case.

The right design is always an end-to-end approach, no matter if it is an existing implementation or something new. The application matters just as much as the backend. Over the years, we’ve helped customers achieve their performance, availability, and security goals by helping them implement solutions that work. SQLHA has worked with some of the largest systems on the planet. If you are not a large company, bank, or government, that does not mean your solution needs any less robustness or aspects of mission criticality. We do this with companies of ALL sizes.

If you want to avoid scenarios like the Iowa Caucus, contact us today. We’ve been there, done that and will roll up our sleeves to get you where you need to go.

November 7, 2019

Two Big SQL Server Availability Announcements from Microsoft

Hello everyone. I have not had much time to blog, but there are a few things that you should be aware of and that I have an opinion about. Both were recently announced – one before Ignite and PASS Summit and the other this week somewhat quietly in a session at Ignite.

Big Deal #1 – Azure Shared Disks for FCIs (and One More Thing …)

If you have ever heard me present, you will know I am not the biggest fan of deploying Always On Failover Cluster Instances (FCIs) in non-physical environments. That includes in any of the public clouds. Why? There are quite a few reasons which I will not go into here, but I will discuss the biggest one: shared storage. Configuring shared storage is awful in any of the public clouds. Sure, if you use something like VMware’s solutions in any of the public clouds you can take advantage of vSAN, but that will not work for most folks. I’ve been VERY vocal how much the Storage Spaces Direct solution is currently a fairly terrible experience at least in Azure. SIOS DataKeeper is a good solution, but for some, they do not want to purchase anything else; they want a workable, native solution. Sure, you can use iSCSI but why would you?

You get the idea. It kinda sucks right now (forget the fact you probably should not be doing it to begin with). I’ve had conversations with both the SQL Server and Windows Server dev teams about this. They know my displeasure.

Maybe they finally heard me. At Ignite during BRK3253 – Windows Server on Azure overview: Lift-and-shift migrations for enterprise workloads (man, I hate how MS does not capitalize titles ….), Elden Christensen and Rob Hindman from the Windows Server dev team (both of whom I know very well) announced the private preview of Azure Shared Disks (it’s apparently being renamed, and isn’t Shared Azure Disks) and show a non-SQL Server-based demo. If you want to skip ahead, they talk about it at 22:34. This will not change my opinion on using FCIs up in Azure, but it will sure as heck make it easier to deploy. As soon as I can test, I’ll report back. There is another session BRK3283 Optimize price-performance and lower TCO for your workloads with the next-gen Azure Disks (not live as of this posting) which will cover more of the Azure disk stuff.

One more recent change which I will be most likely showing and talking about for the first time in a few weeks at my SQL Server LIVE! precon is the ability to use Azure Files for FCIs.

I would recommend you also have a look at the Ignite sessions from John Marlin to see and hear about anything new coming in WSFCs in Windows Server vNext LTSC. The biggest announcement I’m aware of that could affect SQL Server (and specifically FCIs) is the ability to stretch a WSFC with Storage Spaces Direct (S2D). THR2155 I can’t currently watch (network error on the Ignite site), so I haven’t been able to ascertain what else could impact SQL Server. If there’s something else, I’ll either update this post or do a new one if it’s really major.

Big Deal #2 – Changes to Licensing for Availability Scenarios

On October 30th, Microsoft let the cat out of the bag that licensing rules for the availability scenarios were changing as of November 1, 2019 if you have Software Assurance (SA). We always tell our customers that SA is often worth it. You can see the full new rules in the SQL Server 2019 licensing guide. The rules apply to all supported versions of SQL Server.

Let that sink in: supported versions of SQL Server. What does that mean? For example, if you are using SQL Server 2016 with Service Pack 1 whose support ended on July 9, 2019, you would need to go to Service Pack 2 or later. Microsoft is not going to come in and crash your party, but if they audit you or you are going through a true-up for licensing, it could bite you. This means you need to stay current to take advantage of these benefits.

I’m going to discuss what I feel are the biggest game changers. I knew licensing was changing as I had conversations with Microsoft around this months ago. I was not sure what the final result was going to be, but I’m fairly pleased. Is it perfect? No, but it’s much better than it was.

It’s interesting to note that for licensing purposes, Microsoft defines HA as a synchronous replica with automatic failover and D/R as an asynchronous replica with manual failover.

Change #1 – Backups and DBCC on a Secondary Replica Is Now Free

I still hate the use of the words active and passive, but that’s a whole other blog post. In talking with Microsoft, there are other reasons some of these terms are used, but again, irrelevant at the moment. Taking terminology out of the picture, with SA, you can now generate full, copy only backups, transaction log backups, and run DBCC on a secondary replica of an AG without incurring a license. That of course assumes no other databases are live on that instance. That is a huge change because through SQL Server 2017, those were considered “use” and you had to license. This is one thing that should have been there since day one, but I’m just glad they finally corrected their stance.

Change #2 – Allowance for Disaster Recovery Testing

This one is buried in the licensing document, but very big. Here’s what it says:

Customer may also run primary and the corresponding disaster recovery replicas simultaneously for brief periods of disaster recovery testing every 90 days.

I can’t emphasize how huge this is. For example, there was always a question if you were using something like VMware’s Site Recovery Manager (SRM) as your primary D/R method. Now that is covered in pretty clear language. Thank you, Microsoft.

Change #3 – Disaster Recovery Is Now Covered (To a Point …)

Prior to these changes, you only got one “free” replica. If you had an AG with three replicas, two local for HA and one remote for D/R, you paid for two of those. With this licensing change, both the HA and D/R replicas would be covered if you have SA. This is huge.

Change #4 – Azure Disaster Recovery Replicas Are Covered via the Azure Hybrid Benefit

This is related to #3. I’m sure this one will irk some, and if so, please comment below. I know Microsoft is listening 🙂 If Azure is your cloud provider and you want to stick a replica for your AG up in the cloud that is just for disaster recovery, you are now covered with SA. This was not the case before. This new benefit does not apply if you are using Amazon Web Service or Google Cloud.

With this benefit, you can technically have up to three passive replicas (one on premises for HA, one one premises for D/R, and one up in Azure for D/R). Anything else would still seemingly need to be paid for. So if you have two D/R replicas in different data centers (for example, your main data center is in Boston, and you have D/R replicas in London and Chicago), you are going to pay for one of those. That is still better than paying for two.

Things That Could Use Clarification

Due to the “standardization” of terminology, FCIs, AGs, and log shipping are basically treated the same in this document. That’s confusing. Microsoft says this:

Each of these implementations uses different terminology. The examples that follow use ‘Active’ as the read-write database or instance (also referred to as Primary Replica in an Always On Availability Group) and ‘Passive’ for the write only database or instance (marked to not-read, referred to as Secondary Replica in an Always On Availability Group).

But that’s not the terms we use for FCIs in technical documentation. For example, FCIs do not have “write only instances”. You install the binaries on other nodes of a Pacemaker cluster or WSFC and then the FCI can fail over to it. It’s not a “live” instance. I know this is probably not changing because documents like this go through legal reviews and has to match other similar documents/licensing terms for things like Windows, but it irks me to no end.

Also, I am wondering how all of this affects distributed availability groups and how the forwarder is considered. Is it passive or active from a licensing perspective? I’m hoping they clarify that.

April 16, 2019

Notre Dame – Disaster Recovery in Action

Yesterday, a fire broke out at Notre Dame in Paris which caused major damage to the well-known landmark and religious icon. While it is certainly a tragedy, it could have been much worse. Why? There was a protocol for dealing with it. Let me explain and give a little background.

In the world of IT, we talk about the concepts of high availablity (HA) and disaster recovery (DR). Both may use similar techniques, but are very different exercises. High availablity is about surviving a smaller, more local failure with minimal to no downtime and impact. Disaster recovery is what you do when a catastrophic even occurs. Both of these fall under the auspices of business continuity. Business continuity relies on people, process, and technology, but should never be driven by a specific feature. It should be requirements-based. Without people, processes do not work – especially in the case of DR where human intervention is usually required. These concepts are universal and when they are implemented properly, work well.

In Paris, all of that all came together, According to the Twitter thread I linked above, there has been a protocol (read: process) in place since the French Revolution if fire broke out at Notre Dame: save the people first, then save the art, altar, and furniture after, finally landing on saving the structure. People cannot be replaced. Original objects cannot, and to a degree, buildings cannot even though they can be rebuilt. That protocol is about survival. Just this morning, the Guardian published an article on how the structure was being assessed for stability. Clearly the building needs to be safe for people to not only rebuild, but to ultimately use it when it is ready. The article also mentions that objects such as the Crown of Throrns have been secured and others which incurred smoke damage are being looked at by the Louvre. A massive undertaking is underway – and the work has barely even begun!

Now think about where you work and what goes on every day – solutions and their underlying applications, systems, and data power your business. Without them, things would most likely come to a grinding halt, and in a worst case scenario, make the business close their doors if they were down for a significant period of time. We live in a data-driven world. Without data, there literally is nothing. Could you process payroll without the HRIS data needed? How about processing a payment from a customer? What would happen if a record of all of your sales for the last month disappeared? I’ve seen all these scenarios (and more!) fail at companies over the years. In our data-driven world, less data is a bad thing to most companies. Data loss may happen in extreme scenarios, but the goal is to minimize or eliminate. Losing a month’s worth of data can be dealt with but you need to understand not only the technical side of the house to not lose that data, but also the non-technical aspects to devise the right solution.

I am not forgetting about people. By all means, the human aspect is important here. Make sure people are safe and sound before thinking about recovering systems. Systems also need care and feeding. They’re not going to be putting precious objects back into Notre Dame until it’s structurally sound and rebuilt, why would you bring SQL Server up if you do not have, for example, no storage or networking?

Things can change in an instant. You need to be prepared. Paris was. Whether it is an old iconic world known building and its objects, or your own systems and data, you need to account for business continuity to pick up the pieces after the proverbial dust settles. If Paris can get it right with Notre Dame, you can do it in your own environments. Contact SQLHA today to help ensure the survival of your business in the event of downtime events big or small.

September 13, 2018

Don’t Let a Natural Disaster Be One For Your Systems

With Hurricane Florence heading towards the Carolinas here in the US and dominating a lot of the news, it puts many things into the limelight – not the least of which is this question: is your business ready if such an event happened in your area?

Every part of the world has challenges with Mother Nature in some way. Here in the Northeastern US, we generally only see Nor’easters which can knock out power, the West Coast certainly gets earthquakes and there is a potential tsunami threat in places, and so on. You get the idea. There is only so much you can do in a physical data center to counteract all of this. Man made events, including hacking, also falls under this disaster recovery category, but there are defenses you can generally account for in most (but not all) scenarios. You cannot stop a hurricane coming at 140 miles per hour; you can have redundant links to prevent a network outage if your telco cuts a trunk.

The reality is you can never protect against every single scenario planned or unplanned, but you can do your best to ensure that once the event is gone, people are able to get into work, and life starts to get back to relative normal, you have a business to come back to. The famous shots in newscasts and in pictures of people boarding up houses and businesses is one way to do this; the goal is to minimize physical damage. But do you:

Have a plan to properly shut down your systems and bring them back up?
Have a way to restore or rebuild your physical systems/servers in the event they are destroyed?

If the answer to both of these is not a resounding “Yes!”, that’s a problem. For over 20 years I’ve been in the availability business. I’ve helped customers of all sizes from small shops to large enterprise companies. FCIs, AGs, log shipping, etc. – all are great features. But when floods take out your data center, what do you have? For the most part, dead servers which most likely need to be replaced (and possibly the data center, too). You need to start with backups and/or the software to rebuild those systems.

The good news is that with the rise of the public cloud (Amazon’s various offerings, Microsoft’s Azure, GCP), disaster recovery is not completely impossible. Even just storing backups in “cold” storage up in the cloud makes them available in a way that was not an option not too long ago. Most companies, such as Microsoft, can get you the software you’re licensed to use via websites and no longer are you relying on DVDs. Heck, you can build systems up in the public cloud with IaaS VMs that extend your on premises solutions that you can flip to in the event of your main data center not being online.

In a worst case scenario, assuming your SQL Server databases are not hundreds of terabytes or petabytes, back that stuff up to an external disk and take it with you. Of course, you should protect it properly (i.e. encrypted backups, password protected, etc.) since you do not want sensitive data falling into the wrong hands, but for heaven’s sake DO SOMETHING!

Do all of these things have costs, especially cloud-based solutions? You bet! In no way am I claiming is any of this free, but what is the cost associated with bringing you business back online? What is the cost if it cannot come back online? The cost of “cold” storage in the cloud is much less than never coming back.

If you’re in the path of Florence, I truly hope you are safe and that its effects are not devastating. If you want to ensure your business is resilient, it’s not too late to start thinking about how to protect yourself in a worst case scenario. Contact us today to devise the right disaster recovery strategy for business continuity. We can help you minimize and possibly eliminiate your downtime.

12 3 4 5 6 7