Blog

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

By: on March 4, 2021 in AWS, Azure, GCP, SQL Server, Vmware, Windows Server 2022 | No Comments

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

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?

WARNING: Distributed Network Names and Distributed Availability Groups

By: on February 23, 2021 in Availability Groups, Distributed Network Name, SQL Server 2019, Windows Server 2016, Windows Server 2019, Windows Server Failover Cluster | No Comments

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.

Technical Debt – The (Not So) Silent Crisis

By: on February 16, 2021 in Advice, Business Continuity, Technical Debt | No Comments

Here in the USA, many are experiencing unprecedented winter weather. For example, parts of Texas are without power and heat and experiencing blackouts. A big focus of what I do for customers – business continuity – has to account for things like the power going down. A few years ago California had rolling blackouts in the summertime. Austin tried that in this 2021 storm – and stop me if you’ve heard this before – the plan did not work since what happened isn’t what they expected. From this Austin-American Statesman article:

Austin Energy’s plan was to rotate the outages, meaning more neighborhoods would’ve shared the no-electricity burden for the entire city, for a period not to exceed 40 minutes. But the rotation was not possible, Sargent said, because it would have disrupted service for those critical operations.

There is a reason I strongly recommend testing all continuity plans. For IT folks, you do not just want your servers literally powering off. If that happens, pray you experience no data corruption. How are your backups? Test them recently?

Having said that, the Houston Chronicle wrote a damning article. Here’s the part I want to focus on:

“The ERCOT grid has collapsed in exactly the same manner as the old Soviet Union,” said Hirs. “It limped along on underinvestment and neglect until it finally broke under predictable circumstances.

“For more than a decade, generators have not been able to charge what it costs them to produce electricity,” said Hirs. “If you don’t make a return on your money, how can you keep it up? It’s like not taking care of your car. If you don’t change the oil and tires, you can’t expect your car to be ready to evacuate, let alone get you to work.”

Some of what is happening in Texas is not only due to Mother Nature but also partially because of technical debt. Old, aging infrastructure eventually buckles and sometimes fails. Technical debt is not just an IT problem. Real life examples such as this bring the concept front and center.

Kicking the proverbial can down the road and saying things like “We’ll worry about that later, it’s fine now” is often what makes the end results so painful. Neglect increases risk whether it is intentional or not. Max and I help customers so these big leaps incur less risk and pain including managing external dependencies.

Look at your company if you are a FTE or your customers if a consultant. How many applications and servers have not been even looked at because they “just work” and “it/they is/are fine”? Budget is always a concern, but to paraphrase the old adage, you will pay now or pay later. Paying later is often more expensive. Insert your own scenario or question here but answer this:

What is the actual cost if that system/application/server fails or is unavailable? I bet it is more than if things had been dealt with all along or right from the start. You can sometimes avoid a quadruple bypass.

For those of you affected by this weather, my heart goes out to you. Stay safe and warm. Even though we are in the midst of a pandemic, please check in with neighbors and loved ones.

We must be prepared both in life and in business to handle both the expected and unexpected which also means managing your technical debt. This is what we help our customers do every day at SQLHA, so if you want to ensure your business can not only be resilient but also in a good place to manage handle technical debt, contact us today.

Making Lemonade Out of Lemons

By: on February 1, 2021 in Advice | No Comments

The last time I did any appreciable travel pre-COVID-19 was late January 2020. I went down to NYC for a customer meeting. My next trip was supposed to be SQLBits in the UK in March which never happened for obvious reasons. For the better part of the past 20 years, I’ve flown 50 – 100,000 miles per year. Some people I know fly more than that. There are two ways I could have handled the sudden and enforced change in my life: look at it as a problem and be miserable or lean into it and make something of it.

At first, I must be honest and say I was bummed and missed being out there. What I came to realize is that this past year has become a blessing in disguise. Let me explain.

Nearly 20 years on the road does things to your life – both good and bad. There were stretches I was home and sometimes travel came in spurts, but I didn’t earn lifetime status with American Airlines and Marriott because I am a homebody.

What I realized quickly in my forced “staycation” is that I was weary. All those years on the road which came to a sudden halt was like getting off a treadmill still running at full speed. I needed a reset both mentally and physically. Just as COVID was kicking in, I was dealing with a hand issue that was not only hampering my ability to type (affecting work) but also made it hard to play bass. I also realized I had a backlog of projects and other things personally and professionally that were long neglected for one reason or another.

For example, I’ve never worked in an office when at home. I not only set one up, but I figured out a lot of stuff which will influence some exciting things I will announce in the upcoming months. I just never had dedicated time to deal with setting one up when I was home a few days here, maybe a week or two there. Even when I was home, I was working all the time or trying to squeeze one more rehearsal in because I may not be able to play for weeks on end. I didn’t allow myself to slow down. I had to cram something into every moment. That’s normal until it isn’t.

A big silver lining is that 2020 was a busy year for SQLHA. I basically didn’t have downtime all year and just this past week was the first one I took a breath. I am VERY thankful for the work as I know some were not. We have great customers (why aren’t you one? contact us today ;)). I never take it for granted.

Even with being busy, I made myself a priority. I not only feel the best physically I have in years but also rejuvenated. Part of that rejuvenation is being inspired. For example, before these two recent blog posts, I realized I hadn’t written one in nearly six months. I just wasn’t feeling it.

Did 2020 suck in many ways? You bet. It was not all sunshine and roses. I’ll just say this: I certainly have more salt than pepper in my hair after 2020.

Realistically I probably won’t be hitting the road in any meaningful way until later in 2021 or early 2022 since vaccines are just rolling out. Most countries are closed for visitors. Even travel here in the US is iffy to me at the moment. I’m going to continue making lemonade out of lemons by checking items off the aforementioned list of backlogged projects. I know I can’t tackle them all at once, but it feels good to be making forward progress. I am also making sure I give myself some time, too. Self-care matters. You’re no good to anyone if you’re exhausted.

When the time comes, I’ll be grateful to be out and about again and am looking forward to seeing all of you outside of a screen. I know one thing for sure: I will appreciate it that much more. Until then, stay safe and healthy.

Closing in on 30 Years in the Database World

By: on January 28, 2021 in Advice | 1 Comment

It was the fall of 1991. I applied for and landed a quality assurance internship at SQL Solutions in Burlington, MA which was located at 8 New England Executive Park right next to the Burlington Mall. SQL Solutions was purchased by Sybase not long after. I kept that internship through the end of graduation in 1994 and there’s no question it literally set up the rest of my professional career.

I always find it interesting how one thing, one event, one interaction can sometimes change the course of your life – good or bad. I’ve been on both sides of that.

I always went home in the summertime, but when back in MA for college, I was at Sybase twice a week (Tuesday and Thursday if memory serves me correctly). Two of the people I worked closely with and were my direct supervisors were women I still have the utmost respect for today. I’ve never seen gender, color of someone’s skin, religion, etc., as a problem then or now. No one should. I learned a lot in those two years, and I am forever thankful to everyone I worked with at Sybase. In fact, one of the people I met I wound up playing years later in various jazz ensembles (he’s a guitar player). Small world.

Part of me thought I’d land a permanent job at Sybase, but that wasn’t meant to be, and I believe things worked out how they should have. The skills I acquired in those two years gave me the foundation to land my first job post-college doing QA using FoxPro (then Visual FoxPro) for a company that did medical software. In reality, it set me up for life since I’m still tinkering with databases today.

In a harbinger of things to come, I once got in trouble in that job because I automated part of what I was supposed to do using Visual Basic and Excel macros. The results were predictable, so why would I eyeball that? Let a process tell me if it was good or bad. Was I DevOps before DevOps was cool? (kidding here, folks) That little “stunt” landed me on a PIP which I survived but it taught me a valuable lesson.

I can see why automating rubbed some the wrong way – here’s this young upstart at the tender age of maybe 23 trying to tell us how to do our job. I was just trying to free my time up to be more efficient and save my time for the stuff that needed my attention. It’s funny how automation and other things are commonplace now but were not then.

The events of the past year when we’ve all been stuck at home has given us time to reflect and see how we want or need to move forward and improve. I have always looked forward, but that doesn’t mean you can’t look to the past for inspiration. I look fondly on the early years of my career. In some ways I’ve come so far, but in others, I haven’t gone very far at all. I think I found my calling early if I wasn’t going to be a full-time musician but I marched to the beat of my own drummer and I’d like to think I still do to a degree. I’m certainly older. Wiser and smarter? I hope so. Better filter? Those who know me will have varying opinions here.

I’d like to think my success to date has outweighed my failures and shortcomings. I’m a work in progress. We all are. This is why even 30 years in, I try my best to learn and do new things knowing in some cases it will be difficult or I may be ahead of the curve.

A recent interview with Tomo Fujita by Mary Spender struck a huge chord with me. Paraphrasing what Tomo said at one point is this: teachers are not better than students and can learn from them even though they are still helping the student. This one quote stuck with me: “(A) teacher is just a great student.” Fast forward to about 8:37 if you want to see that interaction. It’s such a good interview where even if you are not a musician, you can apply a lot of what Tomo says to your life beyond that little bit.

I’m no longer that early 20-something but I learn something from all of my customers and students (including anyone who attends a webinar, etc.). I may not be the smartest person in the room and I’m totally fine with that but I’m at the proverbial table for a reason and it’s not just my boyish good looks and charm (laugh – it’s a joke). I’ll never try to be all things to all people; it’s impossible. I know my limitations. I can only attempt be the best me I can be at any given moment.

That 20-something also couldn’t have imagined one internship would one day allow him to travel the world and speak to, work with, and teach thousands of people. There are very few things as humbling when people tell you how you’ve inspired them or helped them and it made an impact.

Learn from your mistakes. Learn from every experience – good or bad. Learn from those around you. You never know what may shape and spark things to come … and remember to give back.