So What About That Book, Allan?

This is a post I’ve been meaning to write for some time and one of the hardest. I’m not sure where to begin, so I’ll start somewhere.

Some of you may know I’ve been working on the book Mission Critical SQL Server for a long time … way too long. Sometimes the most well intentioned plans take a left turn at Albuquerque. Let me explain.

Change, Change, Change

I wanted to write the spiritual successor to my SQL Server 2005 book – one big honkin’ volume that would be the reference for all things business continuity. In theory it was an awesome idea.

At the time I started writing there was no Linux, virtualization was still on the rise, and not many were using any cloud provider. We all know the landscape today; so much has changed. That has had its own negative as well as positive impact. There are completed parts that I’ve probably written over again or revised four or five times but have never seen the light of day because every time I thought I was there, something else needed to be added. Multiply that over multiple operating systems, SQL Server versions, and environments (physical, virtual, and cloud) … it became paralyzing.

I can’t tell you how many hours I’ve stared at a screen with Word up and nothing was coming out. Perfection is the enemy of done.

Being Honest

At some point in the process, the book became an albatross and quite frankly, I felt a lot of shame around it not being done. The more time went on, the bigger the shame. I felt like a failure. The book became a mythical, larger than life “thing” overshadowing so many other things in my life. I started to think it would never live up to expectations – real or perceived – which made me shut down. Disappointing people is a terrible feeling and on top of it all, I also gave my detractors plenty of ammo for it not being done. Any time someone would ask me about I had a sense of dread. All of this felt like a 10,000 pound weight on my shoulders.

Last year I decided to do get some help – especially with the shame. For the first time in a long time, I can say I am in a good head space with the book. Recently, I’ve been able to start writing again which has been very freeing. I’ve approached someone to help me with the book, too. One thing as I’ve gotten older is that I’ve realized sometimes you just can’t do it all. This book has been one big life lesson in how not to go about things. If I could screw it up, boy have I with this one.

I’m also learning to be kinder to myself. I have impossibly high standards that can be unrealistic from time to time. It’s ok to be human, not superhuman.

The New Timeline

I know many of you are wondering: is the book ever going to be finished? Yes.

The realistic and, most importantly, achievable goal is to have it done as soon as possible after the release of SQL Server 2022. Since there is no set release date, I can’t say when that is but realistically if it hits the shelves later this year, I’m looking at sometime early in 2023. It won’t just be about SQL Server 2022 but ensuring the latest release is covered makes a lot of sense and keeps it relevant for quite some time.

One thing that I am still debating: instead of one big honkin’ volume, release it in smaller, digestible chunks. Is this something you would want or prefer? It may also allow me to get sections out earlier. Let me know below.

That’s it. I have felt awful about not saying anything or answering questions but understand it was not because I didn’t want to; the shame prevented me from doing so. This blog is me finally putting the shame behind me to move forward and remove the weight from my shoulders.

May 26, 2022

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.

July 13, 2021

Time to Stop Deploying SQL Server 2016 and Windows Server 2016

Today is July 13, 2021. Besides the anniversary of Live Aid in 1985 (had to sneak a music reference in somewhere!), today marks an important date: the end of mainstream support for SQL Server 2016. As far as I know there are no plans for a Service Pack 3. The last Cumulative Update was CU17 released on March 29, 2021.

Windows Server 2016 is on a similar trajectory. It is out of mainstream support on January 11, 2022 – less than six months from now.

What does this mean for you?

Getting Support

A key aspect of being mission critical is having platforms that are supported. In other words, if your business critical systems have an issue, you want to be able to pick up the phone and call Microsoft or whoever to get help. All help for the next five years will be paid. Keep in mind as time goes on, people who know this version of the product well will become smaller.

Getting Updates

Both SQL Server 2016 and Windows Server 2016 fall under the Fixed Lifecycle Policy. When things transition from mainstream to extended support, that means no more updates outside of security for the next five years. While getting security updates is a good thing, that means if you encounter a bug in SQL Server 2016, unless you’ve paid Microsoft bigger money for the right type of support contract, you are out of luck. The core, mission critical platforms in your company should be in mainstream support if possible and receiving regular updates – not just security.

Applications

Applications, whether homegrown or purchased, need to be checked. What version of SQL Server are they running now and what do they support? Will you need a new application version to support a later version of SQL Server? Do you need to do extensive testing or fix things if homegrown? What else is involved to get you from here to there to ensure the application can work with a later version of SQL Server and/or Windows Server?

Plan an Upgrade/Migration Strategy

First and foremost, if you are still deploying either of the 2016 versions for brand new deployments, you should stop or have a plan to wind that down. For SQL Server, you should strongly consider SQL Server 2019 as SQL Server 2017 going out of mainstream support is not far behind SQL Server 2016. SQL Server 2019 can run on Windows Server 2016, but you should really be deploying it on Windows Server 2019 these days.

If you are currently using SQL Server 2016 (or WIndows Server 2016), do you have a timeline, strategy, and plan to get off of them? If yes, great. If not, what are you waiting for? I am not saying you need to upgrade tomorrow but do you want to become a victim of technical debt more than you probably already are?

Next Steps

SQLHA can help you evaluate where you are, help you plan to get off of older versions, and get you safely and efficiently to newer versions with minimal downtime and risk. We get there are more than technical factors at play when it comes to any upgrade/migration scenario and that it takes time to plan and get it right. Stay ahead of the curve. Reach out to us today and start your journey whether it is on premises (physical or virtual) or up in a public cloud.

July 12, 2021

Remembering Brian Moran

Friend. Mentor. Colleague. Husband. Father. Parrothead. Lover of Hawaiian shirts, Crocs, and boats.

While not in any specific order, those are just some of the words that could describe Brian Moran. Brian passed away suddenly this past weekend. He touched many people in the SQL Server community over the years and this loss is a tremendous one. I do not know what happened nor does it matter; my condolences to his family and anyone whose life he touched.

My history with Brian goes back at least 20 years, if not more. I’m pretty sure I met him at one of the early PASS Summit events as I was becoming active in the community. He was just always around at that time. We got to know each other.

Over the years his involvement with PASS waned but his enthusiasm for SQL Server did not. He still participated in the community, albeit more locally to where he lived. I went down to his user group in I believe Reston, VA, to speak a few years back.

Brian played an instrumental part in me becoming the consultant and business owner I am today. He is also a reason I give back and mentor when I can. For that I cannot be more grateful. One example: when I was transitioning consulting jobs in the early 00s, Brian helped me out with some small engagements to tide me over. We worked together on and off.

Most importantly, he would always lend an ear or support if you needed it. He would make the time. Brian was there for me in a few situations over the years and I was happy to provide the same for him. He was never judgemental and even if you disagreed, Brian was the type of guy where you agreed to disagree and remained friends.

Brian was one of the friendliest people you could ever meet. He always cared about others and would help when and if he could which is one of the reasons this is so shocking; one of the good ones is gone.

While he and I lost touch the past couple of years, that did not make him any less of a friend. In the “should have” category, I was thinking of checking in on him recently to see what he was up to. He was on my mind.

Our time on this spinning blue ball is short. Make it count. Don’t leave important things unsaid. Let some stuff go and forgive if you can (not saying it’s always possible). Hug your loved ones a bit tighter. Be a mensch – Brian certainly was.

Rest in peace, Brian.

If you knew Brian, you have your own memories. Feel free to share down below.

March 4, 2021

Ignite Announcements – What’s New for Windows Server 2022 and SQL Server

At Ignite this week, Microsoft officially announced Windows Server 2022. This version follows the three year pattern for the long term servicing channel (LTSC) branch of Windows Server. What does this mean? Some point later this year or early next year you will be able to deploy SQL Server using Windows Server 2022 on premises (physical or virtual) as well as using Infrastructure as a Service (IaaS) in your cloud provider of choice.

In this post I’ll be highlighting the things I think are important from a SQL Server perspective that were announced this week at Ignite.

Windows Server 2022

VMware and Cloud Provider Support

Outside of Azure where you can spin up a preview IaaS VM, I have not seen anyone announce even preliminary support for Windows Server 2022. This will be crucial for adoption. I would not expect that VMware would certify going back to even vSphere/ESXi 6.5. For example, the VMware Compatibility Guide only lists through Windows Server 2019.

Windows Server 2022 is not RTM yet. Be aware supportability must be on your radar map especially in non-physical architectures.

SQL Server Support

It is unknown which versions of SQL Server will be certified for use with Windows Server 2022. Based on prior releases, assume the following:

Any new version (if there is one) released in Windows Server 2022’s mainstream lifecycle (i.e. first five years) will be supported
You will get one, possibly two versions of SQL Server that are currently in support at the time of Windows Server 2022 RTM. SQL Server 2017 and 2019 come to mind as those versions.
I would not expect SQL Server 2016 and earlier to be supported since SQL Server 2016 will be out of mainstream support this summer (July 13, 2021 to be specific).

I am not Microsoft and those types of announcements will most likely be made closer to release.

More Memory and CPU

With Windows Server 2022, you can have bigger deployments of SQL Server. Windows Server 2022 supports up to 48TB of memory as well as 64 sockets and 2048 logical processors. That is a lot of compute power. Of course licensing a server that big will be fun. If you are using virtualization or IaaS, check your hypervisor or cloud provider to see what the maximum VM size is.

For ESXi 7.0, you can see the maximum of what a VM can support with the VMware Configuration Maximums web-based tool. With vSphere 7.0, you can currently (as of the writing of this post) use 24TB of memory and up to 768 vCPUs. Both require virtual hardware version 18. As it stands today, even when VMware supports Windows Server 2022, you would not be able to use all of what this new version supports.

Improved Security

One of the pillars of Windows Server 2022 is security. There are a few things that will potentially impact SQL Server.

Group Managed Service Accounts v2

Group managed service accounts (gMSAs) are also improved in Windows Server 2022. Not much detail is out there at the moment. gMSA v2 does allow Active Directory-dependent apps like SQL Server to work on non-domain joined hosts. The scenario I saw has to do with Windows Server-based containers (not really a SQL Server scenario at the moment), so again, this is a “stay tuned” thing for me.

If you want to understand more about gMSAs, read Steve Syfuh’s (Twitter | Blog) blog post “How Managed Service Accounts in Active Directory Work“. In general I recommend you follow Steve if you care about Windows Server and security.

Secure Computing

If you are using Intel’s Ice Lake (and obviously later) processors, Windows Server 2022 supports confidential computing with Intel Secure Guard Extension (SGX). This combination means applications can be isolated.

For SQL Server, this is supported via the Always Encrypted Enclaves in SQL Server 2019 or later. It works in VMs in Azure (on premises under, say, VMware is unknown) but I’m unsure how it is enabled.

Secure DNS

Windows Server 2022 will feature encrypted DNS name resolution. While not SQL Server specific, this is just a good general improvement.

Server Message Block

Windows Server 2022 supports SMB with AES-256 encryption. The most immediate impact will be for both Always On features, but most likely Always On Failover Cluster Instances since they can use SMB for storage.

Any east-west (i.e. intra-node) Windows Server Failover Cluster (WSFC) communications will now be able to be more secure.

I have not received confirmation if a file share witness in Windows Server 2022 will use the new SMB encryption.

TLS 1.3

By default, Windows Server 2022 will use TLS 1.3. While in theory this should not affect SQL Server, past history tells us it will – here’s some proof with older versions. The impact to SQL Server – if any – is unknown at this time. Stay tuned.

Hotpatch Windows Server

Before you get too excited, understand this is what everyone has been asking for but many may not be able to use. Yes, it means that Windows Server can be patched with no downtime. However, it is currently only for Azure-based IaaS VMs. It is in preview now and here is the documentation. I had a Twitter conversation with Carmen Crincolli who confirmed this initial rollout is for Windows Server 2019-based VMs.

What does this mean for you? Simple. If you’re not in Azure and not running the right version of Windows Server you’re not getting the ability to hotpatch.

Don’t misunderstand me – I love this feature but I hate its scope is currently very limited.

Windows Server Azure Edition

I thought this was some sort of typo or mistake dropped in the hotpatch documentation. Nope! It’s apparently a confirmed new variant of Windows Server per this Twitter thread from Ned Pyle. More on this as I find out. Here is what you see in the Azure Portal. Of course what is in the hotpatch documentation (Windows Server Azure Edition) does not match what is in the GUI (Windows Server 2019 Datacenter: Azure Edition). That stuff drives me bonkers but at the same time, it’s so Microsoft to do things like that.

<rant>USE YOUR MADE UP WORDS FOR THINGS CONSISTENTLY AND CORRECTLY. YOU CONFUSE PEOPLE OTHERWISE.</rant>

Azure portal showing the new edition of Windows Server

Windows Admin Center

I did an unscientific poll recently on Twitter. 94% of the people responding do not use Windows Admin Center (WAC). I have a blog post coming on why I find WAC poor for SQL Server-based deployments.

Event Viewer functionality was added to the newest version(s) WAC. They’re slowly trying to sunset existing admin tools in the OS.

Here’s the reality: Microsoft is not really investing much in improving existing tools if they are not WAC-ready. Failover Cluster Manager may not be updated or just get minor things while WAC will get the majority of attention for any GUI-based administration.

Windows Admin Center is also in preview up in the Azure portal.

PowerShell

Unfortunately, PowerShell 7/Core versions are not fully integrated in Windows Server 2022 nor is the WSFC module ported to them. I believe there are dependencies such as WMI that make some of that challenging right now. I’m a bit disappointed but also understand. Maybe someday that module will be modernized.

Azure SQL

High Availability for Azure SQL Managed Instance

Microsoft also announced that Azure SQL managed instance can now be deployed with additional replicas using Azure Arc. Underneath the covers it’s using Always On availability groups. This is obviously a good thing but there’s one major difference from on premises: it includes the system databases. Maybe we’ll finally get that ability on premises (i.e. “in the box”) sooner rather than later …

Long Term Backup Retention for Azure SQL Managed Instance

Sometimes you need to keep backups of databases around for various purposes, not the least of which is regulatory. Yesterday, Microsoft announced that if you are using Azure SQL managed instance, you now have a way to retain your backups up to ten (10) years.

Maintenance Windows for Azure SQL Managed Instance and Azure SQL Database

A common problem many customers face is restricting maintenance to a certain period of time. Now in preview, Microsoft has added the ability to have some control over when maintenance can happen if you are using Azure SQL managed instance or Azure SQL Database. For more information, read the blog post published a few days ago.

The Bottom Line

There were quite a few things announced that flew under the radar. Some of it is very cool.

Windows Server 2022 is more evolution than revolution for SQL Server. I expect once released, it will be at least 2024 (the year) before any kind of widespread adoption. We’re just starting to see Windows Server 2019 gain meaningful traction and adoption with customers. As things are revealed in more depth over the coming months, I may address specific features of Windows Server 2022 that will have significant impact on SQL Server.

To get started with Windows Server 2022 today, you must be a Windows Insider to get access to the builds for use on premises or try it as a VM up in Azure (see picture above).

Let me know down below – are you even looking to the future? Struggling to get to Windows Server 2019 or do not plan on upgrading at all? Are you willing to use Azure just to get hotpatching?

12 3 4 Next ›Last »