SQL Server 2017 Archives

August 15, 2019

AGs and WSFC OS Rolling Upgrades: What Works and What Doesn’t

It’s been a crazy summer so far. I haven’t had much time to blog but an interesting scenario came up recently that I wanted to share with everyone. I have also missed writing technical posts, and this is a good one to scratch the itch.

In Windows Server 2016, Microsoft added the ability to do a rolling OS upgrade with a Windows Server Failover Cluster (WSFC). This is Microsoft’s official documentation. The SQL Server side of things is not well documented, especially for Always On Availability Groups (AGs), so I did a few tests. The results are fairly interesting. All start out with SQL Server 2012 SP4. The goal is to get to Windows Server 2016 and some version of SQL Server, whether it is the same or different.

A few things you should know

A cluster OS rolling upgrade works/is supported N-1, so you can upgrade Windows Server 2012 R2 to 2016 or Windows Server 2016 to 2019, but not Windows Server 2012 R2 to 2019.
Running validation will fail when you add a Windows Server 2016 node to a 2012 R2 WSFC.
I strongly recommend only adding the upper version nodes with that version. I saw some weird things when I tried to do it the other way.

One of the reasons this upgrade method is important is that especially in virtualized or public cloud-based architectures, you’re no longer tied to hardware for OS uprgades so how do you upgrade clustered configurations of SQL Server?

Strong Warning

The configuration for a cluster rolling upgrade allows for mixed Windows Server versions to coexist in the same WSFC. This is NOT a deployment method. It is an upgrade method. DO NOT use this for normal use. Unfortunately, Microsoft did not put a time limit on how long you can run in this condition, so you could be stupid and do something like have a mixed Windows Server 2012 R2/2016 WSFC. Fire, ready, aim. The WSFC knows about this and you’ll see a warning with an Event ID of 1548.

Figure 1. 1548 warning

Also, what is shown below when adding replicas to lower versions of SQL Server is also NOT a normal deployment method. It’s an upgrade tactic only. Don’t be dumb – especially since it’s a one way street once the AG fails over to the newer version.

If it was me, I would do all of this in one weekend if I could. Circumstances may not allow that, but don’t drag this on very long.

One very good reason to not stay this way is that when dealing with the cluster functional level, unless all nodes are at the higher version, you’re stuck at the older functionality/behaviors. A good example of a benefit you could not take advantage of is cloud witness in Windows Server 2016 if you are at a functional level of 8. 9 is Windows Server 2016 and 10 is Windows Server 2019.

Figure 2. Cluster functional level of 8, or Windows Server 2012 R2

The Tests

Detailed steps are not here, just the overall process to see what does/does not work. For a real production move, there are things I would add to this list but that isn’t what I was going for here. I also did not necessarily use the latest/greatest updates. I was testing process here so it was pretty quick, down, and dirty. There are also many more variants. You can try those yourself.

Test 1

2 x SQL Server 2012 SP4/Windows Server 2012 R2 nodes

2 x SQL Server 2012 SP4/Windows Server 2016 nodes

Figure 3. All 2012 instances

High level process:

Add the Windows Server 2016 nodes to the 2012 R2 WSFC. The WSFC is mixed, and looks similar to the screen shots below.
Figure 4. Windows Server 2012 R2 Node

Figure 5. Windows Server 2016 node
I joined the instances on the Windows Server 2016 nodes as replicas to UpgAG. This query output shows that everything has joined and seemingly looks okay. Or is it?
Figure 6. Joining the WS2016-based SQL Server 2012 instances to the AG
I then set 2012R2UPGN1 and 2016UPGN1 to synchronous data movement since I’m trying to go to WS2016. That works fine.
Figure 7. Changing how data is synchronized between 2012R2UPGN1 and 2016UPGN1
I should be able to manually initiate a failover from 2012R2UPGN1 to 2016UPGN1 right? Wrong. For whatever reason, SQL Server 2012 in this configuration can’t update possible owners on the AG resource at the WSFC level.
Figure 8. Failure to fail over

Result – Does not work. I tried to get around this by manually dealing with possible owners, but the configuration was not having it. It looks like if you want to do a rolling cluster upgrade and stay on all SQL Server 2012, you’re out of luck.

Test 2

2 x SQL Server 2012 SP4/Windows Server 2012 R2 nodes

2 x SQL Server 2012 SP4/Windows Server 2016 nodes

High level process:

Add the Windows Server 2016 nodes to the 2012 R2 WSFC.
Using the configuration from Test 1 that bombed, perform an in-place upgrade to SQL Server to 2016 on the two Windows Server 2016 nodes. Since everything at SQL Server 2012 failed, I wanted to see if this would make a difference.
Figure 9. Mixed versions of SQL Server
Set 2012R2UPGN1 and 2016UPGN1 to synchronous.
I failed UpgAG over to 2016UPGN1 from 2012R2UPGN1. This was successful, and the SQL Server 2016 replicas can now synchronize, but they cannot with the 2012 ones. That makes sense given 2012 is downlevel and upon failing over, the database was upgraded to 2016.
Figure 10. Replica states after failing over to SQL Server 2016
Remove the SQL Server 2012 replicas from the AG.
Evict the Windows Server 2012 R2 nodes from the WSFC.
Upgrade the WSFC functional level.
Figure 11. Upgrading and verifying the cluster functional level
Party.

Result – Success! This mixed combo worked and I was left with both Windows Server and SQL Server upgraded with minimal downtime and little fuss.

Test 3

2 x SQL Server 2012 SP4/Windows Server 2012 R2 nodes

2 x SQL Server 2012 SP4/Windows Server 2016 nodes

High level process:

Add the Windows Server 2016 nodes to the 2012 R2 WSFC
Join the instances on the Windows Server 2016 nodes as replicas to UpgAG.
This time upgrade all four instances in place to SQL Server 2016.
Figure 10. All SQL Server 2016 instances
Set both 2012R2UPGN1 and 2016UPGN1 to synchronous.
Manually fail UpgAG from 2012R2UPGN1 to 2016UPGN1. This succeeds.
Remove the 2012 replicas from the AG.
Evict the 2012 R2 nodes from the WSFC.
Upgrade the WSFC functional level.
Drink a beverage of your choice.

Result -Similar to Step 2, this worked with none of the problems of the all SQL Server 2012 configuration.

Test 4

2 x SQL Server 2012 SP4/Windows Server 2012 R2 nodes

2 x SQL Server 2017 CU 15 + GDR/Windows Server 2016 nodes

Figure 12. SQL Server 2012 and 2017 instances

High level process:

Add the Windows Server 2016 nodes to the 2012 R2 WSFC
When joining the SQL Server 2017 instances on the Windows Server 2016 nodes as replicas to UpgAG, there is a different behavior. Note the join state highlighted. That is not good. Can that be changed?
Figure 13. SQL Server 2017 replicas with a state of NOT_JOINED
Setting things to synchronous does not seem to help.
Figure 14. SQL Server 2017 replicas still do not have a join state that is expected
Changing ownership of UpgAG to 2016UPGN1 does not move the needle much … but it moves it a little. Note that 2016UPGN2 now has a status of JOINED_STANDALONE.
Figure 15. Failing the AG over changes the secondary replica running SQL Server 2017, but not the primary
Removing the 2012 replicas from the AG has no change on 2016UPGN1.
Figure 16. Same status for 2016UPGN1
Failing UpgAG to 2016UPGN2 however gets everthing where it should be for a join state.
Figure 17. 2016UPGN1 now has a join state that is expected
Evict the 2012 R2 nodes from the WSFC.
Upgrade the WSFC functional level.

Result – Success! This one was not straightforward, and I do not know why when you join a SQL Server 2017 replica to a lower level AG in this configuration it gives a status of NOT_JOINED. Clearly that did not happen in Test 2 or Test 3. That is a question for the dev team and another day.

Final Thoughts

While three of the configurations worked in the end, one gave me a bit of heartburn, and the fourth just didn’t work. As always, you should plan upgrades and test them before doing them on your production boxes. If you’re looking for help on upgrades or anything else SQL Server or infrastructure-related, reach out to us. We’d love to help you get to your destination!

October 18, 2018

Windows Server Is Still a Thing and SQL Server Still Runs on Top of It

If you’ve been in hibernation, today you woke up to a world where Microsoft has embraced open source and Linux. What was once unthinkable is now happening. What is going on? Why am I even talking about this?

Since the introduction of SQL Server 2017 and the support for Linux-based deployments, I’ve had a steady stream of questions from C-levels on down to DBAs asking in essence this: “Do I need to abandon SQL Server on Windows Server and learn Linux?” I would use something stronger if this was a casual conversation, but the answer is an emphatic “NO!” SQL Server still runs just fine and is supported on Windows Server (including Windows Server 2019, which is just released). Support is not ending any time soon. Linux is just another option and there may be enhancements specific to each platform because of their differences. It’s not an “either/or” thing. So breathe, OK? If you have a use case for Linux, by all means deploy SQL Server on it.

Just last month at Ignite while working the SQL Server area, I heard a lot of these same statements, but this time from a largely non-SQL Server-centric crowd. Sure, SQL Server 2019 deepens things with things like AGs on containers and the Big Data Cluster story which is right now based on Linux-, not Windows-based containers. I am hoping they work out things so Windows-based containers are supported for SQL Server in these advanced configurations, but remember that Windows and containers is much newer than Linux and containers. Let’s see how this shakes out before you start having nervous sweats. The Windows Server story for containers is much better now than it was.

This isn’t the only thing I’ve been hearing. There’s a rumbling in some corners that many feel Microsoft is abandoning Windows Server and it’s all about Azure. That’s just simply not true. Do I think Microsoft’s marketing has been amiss the past few years with regards to Windows Server which has had an unfortunate effect of de-emphasizing it? Yes. I may be a Microsoft MVP, but that’s my honest opinion. Windows Server, quite frankly, hasn’t seen a lot of love and I want them to correct that. There are some awesome features such as Storage Spaces Direct (which I’ve blogged about and has its challenges with SQL Server, but it’s still a big win for Windows Server). People just don’t konw about them.

Let’s be honest: there has been a LOT of marketing/push for Azure, and I think people are hearing Azure in a way that says to them Microsoft does not care about Windows Server. Nothing could be further from the truth. As a Cloud and Datacenter Management MVP (subcategory High Availability, i.e. Cluster), I’m a Windows Server MVP in addition to my SQL Server MVP award so I see both sides of the coin. I can tell you the Windows Server PMs give a hoot and there are a LOT of improvements in Windows Server 2019. Here’s a link to a blog post that links to many of the Ignite presentataions that talk about what’s new in Windows Server 2019.

While we’re at it, there’s also a negative impression around Hyper-V. As I am also a VMware vExpert, I will be the first to tell you that I see more vSphere than I do Hyper-V.. I am fluent in both hypervisros and have helped customers implement them. I conducted an informal poll yesterday on Hyper-V usage and by golly, there are people using it in the wild for on premises virtualization of SQL Server. That said, the predominant vendor I see is VMware. I’d be lying if I said otherwise. Hyper-V is a “just another” feature of WIndows Server, while vSphere is a product of VMware. I think it makes a fundamental difference in perception.

At the same time, I can see where these perceptions are coming from. I hope SQL Server ups its Windows Server game to not make people think support on it is going away, and Windows Server needs to be talked about in general more. I love Microsoft, but I’ll praise when necessary, and criticize when they need it. I’m doing the latter here, but it comes from a place of caring.

Do you feel SQL Server is de-emphasizing its use of Windows Server? Do you see that as a problem if it winds up being the case a few years down the road? Do you think Microsoft is abandoning Windows Server or due to the over emphasis on Azure, or has made it seem irrelevant? Do you think Hyper-V is still viable? Let me know your thoughts below.

April 24, 2018

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

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.

June 7, 2017

Full DTC Support for AGs Now in SQL Server 2017

SQL Server 2016 introduced support for the Microsoft Distributed Transaction Coordinator (DTC, or MS DTC) and Always On availability groups (click here for more info). However, what is in SQL Server 2016 is only half of the story. For a cross database transaction, it can happen either in the same instance of SQL Server (and therefore, as databases participating in a single AG), or to another database in another instance of SQL Server or some other data source like Oracle. The functionality in SQL Server 2016 covers the latter. That means that if you have two databases in an SQL Serer 2016 AG, you still have the old way – meaning, distributed transactions won’t work properly.

This has now been fixed as of SQL Server 2017 CTP 2.0. Somehow I missed this line in the release notes for CTP 2.0:

DTC support for Always On Availability Groups for all cross database transactions among databases that are part of the availability group, including for databases that are part of same instance.

As far as I know, this will not be backported to SQL Server 2016, but if you need full DTC support with AGs, it’s now there in SQL Server 2017 – a good reason to consider looking at it now. Want help with planning a SQL Server 2017 deployment on Windows Server or Linux? Contact us.

April 19, 2017

Big TempDB Improvement In SQL Server 2017 CTP 2.0 Setup

SQL Server 2016 is the first version of SQL Server which allows you to configure multiple files during Setup. That’s good, but it had a very severe limitation: the data files could only be created with a size up to 1GB (1024 MB). For anyone sizing TempDB properly, clearly you’re going to have sizes that are greater than 1GB, so you still had to configure TempDB manually even in SQL Server 2016. I brought this up to Microsoft in early March because I have been working with a customer who was trying to configure TempDB to their standards using SQL Server 2016, but couldn’t via Setup due to this limitation. It was a great discussion with quite a few folks (including fellow MVPs), and being honest, I didn’t think anything would be done about it in the immediate future. I was wrong.

As of SQL Server 2017 CTP 2.0, this limitation no longer exists. Setup now allows the data files you create to be up to 256GB.

Creating a 256GB data file for TempDB

Microsoft listens, folks. This combined with the AG improvements I blogged about earlier in CTP 2.0 make it a welcome release.

Thinking about SQL Server 2017 or just looking to get more up to date than you are now? Contact us today to help you get started down the path …

Popular Posts

Categories