upgrade 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!

February 15, 2012

Stop Automatically Updating Production Servers

Everything old is new again – well, not really. Many of the core concepts I teach or talk about be it in a session or onsite as part of knowledge transfer on a consulting engagement haven’t changed over the years. Implementations may change, but ultimately, the things that drive them do not. If you care about such arcane concepts like uptime, availability, and mission critical, you understand that downtime is bad. Very bad. Yet I still see and talk to quite a few customers who like to cause themselves unnecessary pain. I mean, it’s perfectly legal here in the USA to own a gun and shoot yourself in the foot, but why would you? It makes no sense.

Move Forward, Not Backward

A big part of staying mission critical is patching your servers – be it an application, the OS, hardware drivers, BIOS … you get the idea. You want to stay supported and up to date. The farther behind you are, the tougher it is to catch up or make the next major upgrade (say, SQL Server 2008 to SQL Server 2012) because you are not at the right minimum level. But that’s not the focus here – the focus is on those more regular patches that get installed such as security updates, browser updates, and so on. Those are the thorns in the proverbial rose bush.

Most companies have outage windows to apply updates and perform other maintenance to servers regardless of OS and other choices. That makes sense and is definitely needed – 100% uptime is largely a fallacy. (Sorry to burst your bubble if you really believed it was possible.) But whether it’s a security patch or a “routine”, more infrequent service pack, care must be taken.

Summertime … And The Updates Are Easy

George Gershwin is probably rolling in his grave, but I parody with the deepest respect. Microsoft has made it painfully easy (emphasis on painful) to update your servers. If you’re connected to the interwebz, you can set the handy, dandy Windows Update feature to download updates automatically. Even better – you can even have Windows automatically install them for you, too. What’s next? A tiled interface that makes Windows harder to use and makes your Server look like Windows Phone? Nah, something that silly could never never happen in a million years. Oh wait …

All kidding and sarcasm aside, look at your production servers: are they set to automatically update? If so, you are NOT mission critical. You may think you are, but you’re not. You’ll never convince me otherwise. Why?

Mission critical is a mindset. Part of that mindset is caring about things like SLAs, RTOs, and RPOs. Allowing servers to spontaneously download and update themselves – possibly requiring a reboot or worse causing one without you even asking – is 180 degrees from that mindset. You might as well just remove any redundancy and leave your chances to fate, who is a cruel mistress. You NEVER want to see this should you have to log into a production server:
Updating for the sake of updating is never the right approach. File under “just because you can, doesn’t mean you should”. Key tenets of mission critical are predictability, consistency, and stability. Servers, especially those participating in a clustered configuration (Windows and/or SQL Server), should be evaluated before applying an update to see if they actually need them. Some do NOT apply, and others may be specific to a configuration (such as a cluster). All patches should be tested prior to going on a production box, and some should be given more weight than others. For example, a fix that touches, say, TCP/IP would be one I would test the heck out of since servers don’t rely on networking or anything. 🙂
To add to #2, I’m going to stick on the consistency point for a moment: servers, especially ones considered mission critical, should all be configured the same. You don’t want varying patch levels and such. That spells trouble with a capital T. (Thank you, Meredith Wilson … I’m on a musical roll tonight!)
If you are going to do anything automatic, just download the updates but for heaven’s sake, don’t let the server automatically apply them. That would be a huge step forward.

The Bottom Line

Look, I realize many of you struggle on a daily basis with your servers and doing this one thing – automatic updates – seemingly makes your lives easier by taking a big task off your proverbial plate. In reality, it may be making it harder. And I also know that in smaller shops especially, you don’t always have the time or (wo)manpower to evaluate each patch/update. I get it. There’s a happy medium somewhere. Don’t turn short term gain into long term pain. There’s no easy or right answer here per se. You have to update. Do what’s best for you, but realize you may blow anything relating to uptime and reliability in the process.

February 13, 2012

Upgrades and Slipstreamed Installations

I’m in the process of testing upgrades to SQL Server 2012. In the process, I’m trying different paths. The current one I’m in the middle of is a clustered instance which will go from SQL Server 2005 SP4 –> SQL Server 2008 SP1 –> SQL Server 2008 R2 SP1 CU3 –> SQL Server 2012. The SQL Server 2008 and 2008 R2 installations were slipstreamed.

However, when I went to go upgrade to SQL Server 2008 R2 SP1 CU3 using my slipstreamed media, the upgrade kept failing. When I went to go look at the log files, one log file (SqlBrowser_Cpu32_1) had the culprit:

=== Verbose logging started: 2/13/2012 22:33:26 Build type: SHIP UNICODE 5.00.7601.00 Calling process: C:Program FilesMicrosoft SQL Server100Setup BootstrapSQLServer2008R2x64setup100.exe ===
MSI (c) (A8:4C) [22:33:26:965]: Resetting cached policy values
MSI (c) (A8:4C) [22:33:26:965]: Machine policy value ‘Debug’ is 0
MSI (c) (A8:4C) [22:33:26:965]: ******* RunEngine:
******* Product: C:Slipstream 2K8 R2 SP1 CU31033_ENU_LPx64setupsqlbrowser.msi
…
MSI (s) (34:F0) [22:33:27:012]: MainEngineThread is returning 1639
Info 1639. Invalid command line argument. Consult the Windows Installer SDK for detailed command line help.
;”C:SLIPSTREAM
…

It looks like the code, for whatever reason, barfed with the spaces only at this point but was OK with it earlier. I renamed the directory to get rid of the spaces and it worked. I entered a bug on Connect here, so go and vote for it.

June 10, 2011

SQL Server Denali, Upgrading, Supported Platforms, and You

Dan Jones just blogged about the proposed upgrade paths and supported platforms for Denali. I’m glad they are publishing this information long before a new CTP is released. In that blog post, Dan encourages you to reach out to them via Connect (linked in his blog post) and I would say the same: if you have any issues or concerns, or agree with what I’m going to post here, let ’em know! Speak now or forever hold your peace.

Here’s my take on what Microsoft is proposing. You may disagree and I would love to hear your comments.

Windows Server 2003 Is Dead
If you haven’t gotten the hint, Microsoft has released two OSes (Windows Server 2008 and Windows Server 2008 R2) since Windows Server 2003/R2. Yet, I still know many customers (talk to them often) that still only support Windows Server 2003. I’ll just say this: if you haven’t started looking at, evaluating, certifying, deploying, etc. Windows Server 2008 (and at this point, it should be Windows Server 2008 R2) in your IT organization, start now. That should be a huge priority.

Supporting Windows Server 2008 with Service Pack 2 Is A Mistake
I’m focusing mainly on the server side. I know Windows Server 2008 (no R2) is Vista server. I’m in full agreement that Vista the desktop OS should be supported. Supporting Windows Server 2008 with Service Pack 2 is a huge problem for me. Why? Let me state my case.

Windows Server 2008 mainstream support ends in 2013.
Check the Microsoft lifecycle site for your country if you don’t believe me – it’s slated to end mainstream support in July, of 2013. We do not know any RTM dates, but it’s fair to assume most of you at the earliest won’t even think about Denali until sometime in 2012, if not later. Do you want to deploy on an OS that is already out of mainstream support and you can’t get hotfixes for? Yeah, I didn’t think so. This also plays into the whitepaper I wrote about supportability and staying ahead of the curve.
From an AlwaysOn standpoint, Windows Server 2008 R2 SP1 brings much more to the table.
Windows failover clustering, which both the SQL Server failover clustering feature as well as the new availability groups feature rely upon, has been further enhanced in Windows Server 2008 R2. The biggest improvement is PowerShell integration.
Windows vNext is around the corner.
Again, we don’t know release dates but Microsoft recently unveiled the first looks at Windows 8 the client OS, which means its server counterpart is also in the works. If Denali ships with Windows Server 2008 SP2 support, that means their support needs to worry about Denali on three different OS platforms with their own quirks. That’s not a scenario I would want if I was Microsoft.
[EDIT – Added] Server Core and failover clustering.
I’m not giving away any trade secrets, but you may have heard that Denali will be supported on the Server Core variation of Windows (i.e. no GUI). Server Core is part of both Windows Server 2008 and Windows Server 2008 R2. However, if you want to do something like failover clustering, cluster validation (the Windows process) only has a PowerShell cmdlet (not a cluster.exe implementation), and that would require Windows Server 2008 R2.
32-bit is dead. Long live 64-bit.
Windows Server 2008 (no R2) does have a 32-bit flavor. That means Denali would have to ship a 32-bit server (which may be the main reason behind the decision to support that OS). However, let’s face it: 32-bit is a dying breed in most environments. Truth be told, I haven’t implemented anything but an x64-based SQL Server (even under virtual machines) in my test environments as well as at all of my customers for at least 3 or 4 years going back to Windows Server 2003. I would argue most of you who need 32-bit need legacy support in some way, and that is done via some old platform. Your new apps are probably moving on, even if you are using Windows Server 2003 64-bit and not Windows Server 2008/R2.Windows has killed 32-bit. I believe Exchange has. Why not SQL? I don’t understand the wishy-washy reluctance to not embrace 64-bit fully and kick 32-bit to the curb. It’s time to move on. 32-bit has been a great platform, but let’s retire it with dignity.This does not mean that Microsoft shouldn’t support 32-bit on the desktop … I’m purely talking about deploying it in a production capacity.There’s another big reason I would recommend this: make the testing matrix smaller. Removing 32-bit as a server platform allows MS to have better testing coverage for everything else. The more platforms, the more testing, the bigger the matricies. Having done QA in the past, I know this is huge.

What About SQL Server 2000 Upgrades?
It’s clear by the list you won’t be able to go directly from SQL Server 2000 to Denali. Hopefully by now most of you know that SQL Server 2000 is not supported on any version or variation of Windows Server 2008. While Windows Server 2003 supports SQL Server 2000, 2005, and 2008, it’s gone. Kaput. Forget about it. In that context, it makes sense that upgrading in-place would not be possible to Denali. This is much like the SQL Server 7.0 upgrade scenario where in some cases with later versions of SQL Server you need to do a “double hop” (i.e. get it to 2000, and then upgrade to a later version). In this case, you’d have to do an intermediate step to 2005 or 2008. If you’re going to have to go from 2000 to Denali, start thinking about that and come up with a short term plan so you can minimize your downtime in the Denali upgrade.

I know that the decision here will not make some of you happy, but it does make sense at this point to cut the 2000 lifeline directly. I know customers out there are still using it. You have options, but you need to just figure out what you want and need to do.

What Version of SQL Server 2005, 2008, or 2008 R2 Are You On?
So the published list of minimum upgradeable SQL Server versions (meaning you can be at that version or greater) is 2005 SP4, 2008 SP2, or 2008 R2 SP1. If you’re not at any of those, as with what I said about 2000, start planning to get there now. It’ll save you a lot of grief and aggrivation in the long term.

Popular Posts

Categories