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?

August 3, 2020

We Want YOU to Help the RDBMS Licensing Session at VMworld 2020

Hello everyone. Dave Welch from House of Brick (Blog | Twitter) and I will be co-presenting session HCP1634 “Licensing Oracle and SQL Server on vSphere” at VMworld 2020 this September. I will be focusing on SQL Server while Dave will be covering Oracle. Many of you may only use one of the database engines, while a fair number of you may have both in your data centers (or cloud providers). We know it’s going to be a great session but we want your input to make it even better.

Because VMworld 2020 is going to be virtual that means some sessions (including ours) will be pre-recorded. That puts a damper on interaction. We’d like to change that as much as possible and make it feel as if you were there in the proverbial room with us. Specifically, we would like to know some of your pain points around licensing SQL Server and/or Oracle in virtualized environments (on premises and/or cloud) as well as glean information about what you deploy so our session can reflect the audience. Normally we would poll the room at various points during the session, but that won’t be possible. We’re also taking questions that we’ll incorporate into the session as well. We cannot promise we will get to every single one, but historically, the licensing session has left quite a bit of time for Q&A, and we want to be able to still do that. The only way to make that happen is to gather them ahead of time.

How do you participate? It’s easy – take our survey and fill out the relevant bits that apply to you. Any names or e-mail addresses, organization name, and role information provided is completely optional. We will not use them for promotional purposes nor store them permanently. If we use your question, the name will allow us to possibly follow up should we need to do so.

The survey is live now and will close at midnight on August 11th. What are you waiting for? Go fill it out!

We thank you in advance and we will “see” you virtually at VMworld 2020.

May 18, 2020

Configure SQL Server Backup to URL Using Azure Cloud Shell

Happy Monday, everyone.

I had to do something this weekend that involved the SQL Server feature backup to URL which allows you to back up databases in a SQL Server instance directly to Azure blob storage. That PowerShell there is quite the adventure … and cumbersome. There’s a much easier way to get this done using Cloud Shell up in Azure. I used Bash, but I’m sure there’s an equivalent Azure CLI PowerShell way as well.

Backup to URL/Restore from URL works from Transact-SQL and the SQL Server PowerShell cmdlets. There is no UI option in SSMS. Figure 1 shows what happens when I tried to restore a valid backup stored in a URL in version 18.4. I hope this eventually gets fixed. For all graphics in this post, click on them to enlarge.

Figure 1 – Thanks for playing … don’t try again.

Storage Accounts and Access Keys

This post assumes you already have a storage account in Azure. To do nearly anything, you need one. You can create a special one just for backups if desired, but it’s not necessary. Work with your IT admins to see what the general direction is here if you already have a presence in Azure.

Whatever storage account you use, you’ll need three things: the name of the resource group that contains the storage account if using Cloud Shell, the storage account name, and the access key for the storage account. You can get the storage account name and the key from the Azure Portal. It can be found on the Access keys blade for the Storage Account as shown in Figure 2.

As an aside, the fact all of the menu things are not capitalized for the first letter completely drives me NUTS. Yes, I get that’s how things are today so get off my lawn. But if people stuck to it or used it consistently, I’d be fine – annoyed, but none worse the wear. Look at the screen shot below. Storage Explorer (which is a specific service in Azure) versus everything else (i.e. Access keys). Boy, that shift key is really, really hard for those extra words, no? Anyway …

Figure 2 – Access Keys in Azure.

If you don’t want to use bash to put the storage account key into a variable, you can just cut and paste from the Azure Portal. Stick it in a Notepad (or equivalent on your OS) document if you want that you don’t save; it’s just a file to keep open so you don’t have to keep navigating back to that blade.

To get the access key using in Bash and set it to a variable, execute the following command:

[code]storageaccountkey= $(az storage account keys list -n “storage_account_name” -g “resource_group_name” –output tsv –query “[?keyName == ‘key1’].value”)[/code]

Tip: you could also put the storage account name into a variable since you’re going to use it a few times, but if you have it handy, it’s just as easy to cut and paste. Don’t overthink things.

Containers and the Shared Access Signature

The backup file you generate in SQL Server or restore from is stored in a container as a blob.

If there is already a container you will use, you can skip this step. If a container is not created already or you want to use a different one, create a container in the storage account. Below is the syntax for Bash:

[code]az storage container create –name “container_name” –account-name “storage_account_name” –account-key $storageaccountkey –fail-on-exist[/code]

Now you have to generate the shared access signature (SAS). This is how you will set up the secure access to the blob up in Azure. -o tsv is the same as –output tsv which takes care of the double quotes at the beginning and end of the output. Remember to set the value for the expiration date (–expiry) well into the future. The date is in UTC format.

[code]az storage container generate-sas -n “container_name” –account-name “storage_account_name” –account-key $storageaccountkey –permissions “rwdl” –expiry “2020-05-31T00:00Z” -o tsv[/code]

A big long string of text like the one below is generated. After that happens, you use it in the Transact-SQL used to create the credential in SQL Server.

[code]se=2020-05-31T00%3A00Z&sp=rwdl&sv=2018-11-09&sr=c&sig=%2BRUJldVzQHFdh7lZAeynfRuZr5dUeUohfVwKHZZX3jE%3D[/code]

Create the SQL Server Credential

Creating the the credential in SQL Server is a simple Transact-SQL statement.

[code]IF NOT EXISTS
( SELECT * FROM sys.credentials
WHERE name = ‘https://storage_account_name.blob.core.windows.net/container_name’)
BEGIN
CREATE CREDENTIAL [https://storage_account_name.blob.core.windows.net/container_name]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’,
SECRET = ‘se=2020-05-31T00%3A00Z&sp=rwdl&sv=2018-11-09&sr=c&sig=%2BRUJldVzQHFdh7lZAeynfRuZr5dUeUohfVwKHZZX3jE%3D’
END;
GO[/code]

That’s it. You should be able to back up to and restore from a blob in Azure. Figure 3 shows a successful database backup of WideWorldImporters to Azure using Transact-SQL.

Figure 3- It really works!

The backup file WideWorldImporters.bak can be seen in Figure 4.

Figure 4 – WideWorldImporters in Azure blob storage

The error message below appears if something is configured wrong.

[code]Msg 3201, Level 16, State 1, Line 33
Cannot open backup device ‘https://storage_account_name.blob.core.windows.net/container_name/WideWorldImporters.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 33
BACKUP DATABASE is terminating abnormally.[/code]

Chances are the problem was between the brain and the keyboard, such as fat fingering the name of the container or storage account. Some examples include extra characters at the beginning of the SAS such as a question mark (?) or having double quotes before and after the SAS.

Why Use Cloud Shell and Bash?

In my experience, it is many less lines of code. If you do not have to create the container and want to just cut and paste the storage account name and access key, it is literally one line of code you need to write to generate the SAS required for the credential in SQL Server.

Hope this helps some of you out there.

January 17, 2020

Security Is an Availability Problem

I’ve been hearing more and more from people I know as well as news stories that security is on people’s minds in one way or another. It’s a fundamental IT tenet that security should be a priority. With the rise of ransomware as well as the number of hacks and data leaks going up, things have reached a fever pitch – and I do not think we have hit a peak yet.

The reason I’m writing this blog is because every data breach and ransomware incident becomes an availability problem in one way or another. Mission critical encompasses many things – including both security and availability. Until recently not many saw the two as being in the same boat. Welcome to my world. Come in, have a dip in the pool – the water’s fine!

Let’s take the example of one of the most recent victims of ransomware, Travelex, one of the world’s largest travel-related companies. Travelex was hit with ransomware on December 31, 2019. Happy New Year, right? It hasn’t been for Travelex. They do business with lots of people including direct consumers. One of their businesses is money exchange at airports – I’ve seen their kiosks and storefronts at numerous ones including Heathrow.

They have been silent until today. On their customer information page in the UK about the incident, a video was posted from Tony D’Souza, the CEO of Travelex. First, taking nearly three weeks for a response to a very public incident is not necessarily a good thing in my estimation. I get the need to deal with things. I’m not perfect in all my communications but I’m also not Travelex. A big part of these incidents is incident management – including the public side of things. The customer page is reach, but I’m talking about the overall PR effort.

As of today, January 17, 2020, the screen grab below is their website’s front page in the USA (click to make larger).

The Travelex home page as of 1/17/20

They are partially back up and working. According to a BBC report,

However, while he said the system used by staff is now working, there was no word on when the firm’s main UK website would be returned to service.

That means customers are still unable to order currency online, either from Travelex itself or through the network of banks that use its services, including Barclays, Lloyds, RBS, and the finance websites of Sainsbury’s and Tesco.

Ouch. That is a lot of lost business for their partners AND Travelex themselves with seemingly no end in sight. You get the idea.

Security starts well before someone accesses a databases or a system. FedEx had an issue a few years ago that cost them hundreds of millions of dollars because it got in through a subsidiary in Europe. IT needs to be able to cope with the modern threats. Most do a great job, but sadly, there will always be one attack vector you may not have thought of. Covering as many as you can is important. Not doing anything or saying, “No one will care” should have people polishing their resumes. Just look at the City of Baltimore which got hit with ransomware; it cost them at least $18.2 million.

Security is more than worrying about PII, credit card numbers, HIPAA, but as data professionals, that is one of our primary concerns. For every company that worries about what port to configure for SQL Server and frets that 1433 is insecure, worry about bigger problems like prioritizing the security of data at rest (including backups) as well as on the wire. A port scanner will find that SQL Server instance pretty quickly. Obfuscation may slow a hacker down and annoy them, not stop them. Put some effort into a robust backup strategy that has your backups safe, offsite, and tested so you know you can restore them if you get hit with something like ransomware. Even if you secure SQL Server but you have a ransomware incursion, you still may need those backups if you can’t access the systems. Everything matters here.

We always take security into account when we’re helping our customers design or evaluate solutions. Security is multi-layered. You do not want to let a security problem become an availability issue. Could your business survive being nearly completely down for over two weeks and counting like Travelex? I doubt it. Want to make sure you never find out the answer to that question? Contact us today.

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.

12 3 4 Next ›Last »