Five Windows Server 2019 Improvements for SQL Server Deployments

By: on April 25, 2018 in SQL Server, Windows Server 2019 | No Comments

Yesterday I blogged about enhancements to availablity groups in recent updates to SQL Server 2016 and 2017. Today it’s Windows Server’s turn.

Announced last month, Windows Server 2019 will be released later this year. I know there has been a lot of buzz about SQL Server on Linux since SQL Server 2017 was released, but most deployments I see are still on good ol’ Windows Server. There has been no announcement yet which versions of SQL Server will be supported on Windows Server 2019. That said, every version of Windows Server pushes things forward a bit for clustered configurations, and there are three specific improvements that I feel are good for WSFCs and SQL Server in general. All of the features I talked about are in Build 17650 or later which was released on April 24 (yesterday).

There is one new feature right now that I’d love to say works with SQL Server but it doesn’t – cluster sets. I hope they eventually will, but I doubt it will be in Windows Server 2019.

Improvement #1

This one is a general Windows Server improvement that may benefit SQL Server deployments both clustered and non-clustered. I was poking around in setting a server up and formatting some disks. Lo and behold I saw this:

Figure 1. Disk allocations greater than 64K

Your eyes are not deceiving you – disk allocation sizes bigger than 64K! Interesting, no?

Improvement #2

This one is specifically for Always On Failover Cluster Instances (FCIs) that are not using Storage Spaces Direct, but more traditional shared storage. WIndows Server 2016 introduced Storage Replica (SR), but it was only in Datacenter Edition. SR is just Microsoft’s implementation of disk-based replication that works with WSFCs. In Windows Server 2019, SR is coming to Standard Edition. It was introduced in Build 17639. However, there are currently some restrictions which will most likely rule it out for many, namely:

  • SR replicates a single volume, not an unlimited number of volumes). So if you have one disk associated with your FCI, it’s great. If you have more than one, not so much.
  • You can only have one partnership.
  • The volume you are replicating can only be up to 2TB. That is probably the biggest dealbreaker for many SQL Server deployments.

As they note “We will continue to listen to your feedback and evaluate these settings through our telemetry during Insider previews of Windows Server 2019. These limitations may change several times during the preview phase and at RTM.” Make your voices heard – if you want to see this actually useful for SQL Server deployments, provide Microsoft with feedback. Follow the feedback info in the 17639 post I link above. Or just bother Ned Pyle on Twitter. He’ll love it 🙂

Improvement #3

This improvement is for Always On Availablity Groups (AGs). Starting with SQL Server 2016, you could deploy an AG on a Windows Server Failover Cluster (WSFC) without a domain. That particular configuration of a WSFC is called a Workgroup Cluster, but its fatal flaw is that for a witness resource, you were stuck with either a disk (which invalidates the whole non-shared disk deployment model) or cloud witness (which not everyone can use). Well, Windows Server 2019 fixes that problem.

Announced on April 16, you can now use a file share witness (FSW) with a Workgroup Cluster. FSW prior to this specific implementation required a domain, hence the problem in Windows Server 2016. It’s all done with local ACLs on the file share, and when you create the FSW, you provide the credentials. This first screen grab is what you see when you first input the command to create the FSW

Figure 2. Adding a FSW in a Workgroup Cluster

This next one is when it is complete.

Figure 3. FSW successfully configured

All the gory details are in the blog post I link, but this is exciting stuff.

Improvement #4

WSFCs no longer use NTLM and use Kerberos and certificates for authentication. This means that if you’re deploying Windows Server 2019 and your security folks want to disable NTLM, go right ahead.

Improvement #5

Last, and certainly not least, is the one I’m proabably the most excited about. One of the questions I’m asked the most is “How can we change the domain for our FCI or AG configuration?” The answer has been to unconfigure/reconfigure. Going back to the early days of clustering Windows Server, you could not change the domain. That all changes in Windows Server 2019. We finally have the ability to move a WSFC from one domain to another and not have to reconfigure anything! This is big, folks. How to do it from a pure Windows Server perspective is documented here.


Important Availability Group Improvements in SQL Server 2016 SP2 and 2017 CU6

By: on April 24, 2018 in Availability Groups, SQL Server 2016, SQL Server 2017 | No Comments

Hi everyone. If you’re using Always On Availability Groups (AGs), Microsoft has put a few improvements/fixes in recent patches that you should be aware of.

First and foremost, SQL Server 2016 Service Pack 2 was just released today. There are two major improvements in it for AGs:

1. SQL Server 2016 now has full Microsoft Distributed Transaction Coordinator (DTC) support. SQL Server 2016 had partial support for DTC with one of the two scenarios (cross instance/cross platform), but not intra-instance DBs. SQL Server 2017 had both, and now that was backported so SQL Server 2016 supports all DTC scenarios with AGs. This is great news.

2. Fixing Service Broker so that on failovers, AGs close open connections.

In SQL Server 2017 CU6, the big change is that the distribution database in replication can now be made highly available using AGs. There are a bunch of caveats, but this is a pretty big deal. See this link for all the info. As with full DTC support, they are going to port this back to a CU for SQL Server 2016 SP2, so SQL Server 2016 will also be getting this capability.


Revisiting Storage Spaces Direct and SQL Server FCIs

By: on March 29, 2018 in Availability Groups, FCI, Storage Spaces Direct, Windows Server 2016, Windows Server Failover Cluster | 2 Comments

Let me be up front here: I really do like Storage Spaces Direct (S2D). I’ve been talking about it a lot in presentations (including just recently at SQLBits – click here to see the video), and first mentioned it in more detail back in 2016 around a benchmark with SQL Server. It makes implementing failover cluster instances (FCIs) *so* much easier (especially under virtualization) and it adds a lot of IO scalability when done right. It’s a great architecture if you have the need. We’ve helped customers plan it, and if you need we can help you, too. Just contact us.

For those of you unfamilar with S2D, it’s a way to create storage for FCIs using storage that is local to the nodes themselves – not via SAN, iSCSI, etc.  All of the official Microsoft documentation can be found here (no real mention of SQL Server), and if you want to know more, see my presentation linked above. I also teach S2D in some of my classes, where you may even get hands on experience with it.

That said, there are a few gotchas, some of which unfortunately affect SQL Server architectures – one of which could be a temporary showstopper for some, at least in its current form in Windows Server 2016. Let me explain.

Arguably, the biggest thing about S2D is that the solutions currently have to be certified (see this bit of documentation from MS for more detail). This obviously doesn’t really affect, say, virtualized versions or ones up in the public cloud such as in Azure in a meaningful way, but it’s still technically a requirement much like logoed hardware for Windows Server supportability. Anyone want to point me to the logo stamped on your VMs? Didn’t think so. Now, from a pure FCI perspective none of this is an issue. The way a Windows Server failover cluster (WSFC) is currently designed, it is expecting that all nodes participating in the WSFC are also using/needed S2D. Why am I mentioning this? Disaster recovery.

By its nature as needing shared storage, FCIs are more often than not a “local” HA solution and you add something else to it to make it business continuity friendly like an AG, log shipping, underlying storage-based replication which is transparent to the FCI, etc. For an FCI using traditional storage methods such as a SAN, none of this is a problem. It all just works and is supported just fine. Storage Replica in Windows Server 2016 (also Datacenter Edition like S2D) is also a great option for FCIs.

However, Storage Replica and Storage Spaces Direct cannot and do not work currently together in Windows Server 2016, and I am hoping they do in the future. SR cannot stretch an S2D WSFC right now. That means the D/R aspect of S2Ds with FCIs has to come in some other way. Here’s the Uservoice item for stretching an S2D WSFC to another site. It should be noted that as of the writing of this blog post, Windows Server 2019 was recently announced but nothing I’ve seen publcly points to SR and S2D working together in that upcoming release, nor would I bank on that.

Let’s add AGs to the party. In a traditional Windows Server-based AG, an instance participating as a replica can be standalone or an FCI. FCI + AG is a fully supported architecture from a pure WSFC and SQL Server standpoint right now. S2D complicates this, though. Why?

S2D WSFC configurations currently do not expect a non-S2D node. So if you have an FCI across n WSFC nodes with S2D for storage, and then want to extend that by an AG, it’s technically not an architecture that is currently supported in the traditional sense. Does it work? Yes, I’ve configured it. Will Microsoft give you best effort support if you’ve done this and run into an issue? Absolutely but at some point if it’s a serious issue, they could say “This isn’t a supported architecture, implement something supported.”

S2D across multiple sites is not an architecure that is currently supported, either. FCI + AG would be stretching the S2D WSFC, albeit in a non-traditional way. Since stretched S2D clusters are not supported anyway … it’s “complicated”. Yes, from a pure SQL Server point of view (and my perspective, quite frankly), the FCI + AG techically isn’t a stretched S2D WSFC, but in a way it is because of the mixing of S2D and non-S2D nodes in the same S2D WSFC. I can see both sides of the coin here.

If you try to add a non-S2D node to a WSFC configured to use S2D for storage, here’s what you see during the validation process in Windows Server 2016:

Running validation for a new node in an S2D WSFC

How do you achieve D/R right now with S2D-based FCIs? Outside of platform-based methods for things like virtualization and IaaS, at the OS/SQL Server layer, log shipping is the easiest way. No clustered anything needs to be involved. SQL Server 2017 has the NONE-type AG which does not require any clustering whatsoever, but I’d avoid that for reasons I’ve discussed and talked about elsewhere. A Distributed AG would also technically work since it would span two different WSFCs; it is not a stretched single WSFC with S2D. Only the nodes participating in the S2D-based FCI would be in the same WSFC. Distributed AGs are an Enterprise Edition-only feature, which rules it out for many people.

Bottom line: right now: if you want to combine FCIs and AGs, the safest and currently the only fully supported way is to use traditional storage methods for your FCIs that are not S2D-based. I really hope that Microsoft officially supports the FCI + AG scenario where the FCI uses S2D in the near future. Stay tuned!


Mission Critical Moment #2 Is Here

By: on March 26, 2018 in AWS, Azure, FCI, Hyper-V, Mission Critical Moment, Pacemaker, Public Cloud, Virtualization, Vmware, Windows Server Failover Cluster | No Comments

I just got done uploading the second in our new, free video series and it’s now live. This time around I’m tackling a question I get all the time as well as a problem I see – FCIs when people virtualize as well as in the public cloud. Click here to see it.

New Upcoming One Day Class in MA

By: on March 9, 2018 in Training | No Comments

Happy Friday, everyone. It’s been a crazy few weeks between heading over to the UK to speak at SQLBits, quite a bit of customer work, and then being in Redmond this week for the MVP Summit. I had a great Training Day at Bits, and if you ever get the chance to go, Bits should be one of your SQL Server destinations. I learned quite a bit at MVP Summit this week. There’s a lot I wish I could talk about, but all in good time.

Speaking of training, I’ll be announcing 2018 classes and dates soon. There are some things I need to button up on this end of things before I do. That said, there is one class that I announced recently that some of you may not know about. For the first time in a few years, I’ll be teaching a one day class on Friday, April 6, near home in the Boston area at the Microsoft office in Burlington, MA. It’ll be nice not to have to hop on a plane but drive about 15 minutes up the road. It came about because the New England SQL Server group approached me, and I am happy to do it with them.

The class I’ll be teaching is “Planning SQL Server Availability Solutions in a Physical and Cloudy World”, and yes, there will be a lab. The price of the class is $250, but through the end of today, it is $50 off knocking the price down to $200. If you register after today but before the end of Friday, March 16, you’ll get $25 off which brings the price to $25. For information and how to register, click here.