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

By: on August 15, 2019 in SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, Upgrade, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019, Windows Server Failover Cluster | No Comments

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:

  1. 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

  2. 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

  3. 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

  4. 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:

  1. Add the Windows Server 2016 nodes to the 2012 R2 WSFC.
  2. 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

  3. Set 2012R2UPGN1 and 2016UPGN1 to synchronous.
  4. 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

  5. Remove the SQL Server 2012 replicas from the AG.
  6. Evict the Windows Server 2012 R2 nodes from the WSFC.
  7. Upgrade the WSFC functional level.

    Figure 11. Upgrading and verifying the cluster functional level

  8. 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:

  1. Add the Windows Server 2016 nodes to the 2012 R2 WSFC
  2. Join the instances on the Windows Server 2016 nodes as replicas to UpgAG.
  3. This time upgrade all four instances in place to SQL Server 2016.

    Figure 10. All SQL Server 2016 instances

  4. Set both 2012R2UPGN1 and 2016UPGN1 to synchronous.
  5. Manually fail UpgAG from 2012R2UPGN1 to 2016UPGN1. This succeeds.
  6. Remove the 2012 replicas from the AG.
  7. Evict the 2012 R2 nodes from the WSFC.
  8. Upgrade the WSFC functional level.
  9. 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:

  1. Add the Windows Server 2016 nodes to the 2012 R2 WSFC
  2. 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

  3. 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

  4. 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

  5. Removing the 2012 replicas from the AG has no change on 2016UPGN1.

    Figure 16. Same status for 2016UPGN1

  6. 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

  7. Evict the 2012 R2 nodes from the WSFC.
  8. 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!




Independence Day Training Sale

By: on July 1, 2019 in SQLHAU, Training | No Comments

Hi everyone. Quick note. From today (July 1) until 11:59 PM Eastern on July 8, we are having a limited time sale on all SQLHAU training – 30% off everything. Use discount code JULY4 to get 30% off your entire order.

The sale includes all SQLHAU training scheduled on our events page including the two upcoming in person classes in Chicago (Modernizing Your SQL Server Infrastructure Boot Camp and the Always On Availability Group Boot Camp) and the two online live classes – Almost On: A Guide to Troubleshooting Availability Groups and FCIs in a few weeks and the Always On Availability Groups Boot Camp in December.

Supporting the Future – Win a Seat to the Availability Groups Boot Camp in August

By: on June 12, 2019 in Availability Groups, SQLHAU, Training | No Comments

Giving back is intrinsic to SQLHA’s values. Nearly every time we offer public training, we’ve given away a seat in at least one class. In 2016, it was WIT and we saluted the troops in 2017. The upcoming classes in Chicago in August are no exception. This time, we’re giving away one seat for the Always On Availability Groups Boot Camp from August 7 – 9.

For this giveaway, we’re focusing on the future. If you are between the ages of 18 and 25, a high school senior, currently enrolled in college, or just graduated college within the last six months, you are eligible to be in the running for that free seat. This is a very unique opportunity to get real world skills at a young age, so good luck to all entrants.

The Rules

  1. You have to be able to prove that you are at least 18 years of age but no older than 25, a high school senior, currently enrolled in college, or just graduated college in the last six (6) months.
  2. Send an e-mail to sales at sqlha dot com with the subject Give Me Real World Education and tell us why you think you deserve the seat and how it would impact you. You really have to demonstrate that you truly would benefit from attending.You do not have to send a tome, but one or two lines won’t cut it either. The grammar police won’t hold it against you if your e-mail is not up to snuff; we prefer heartfelt over perfect. Having said that, see #10 of The Fine Print. There is one exception.
  3. Entries must be in by Friday, June 28, at 5PM Eastern. A winner will be chosen and notified by Friday, July 5.
  4. Do not make or send a video, write a Word document, etc.; that will disqualify you. This should be e-mail only.

The Fine Print

  1. One entry per person.
  2. Winners will not be eligible for a free seat in a future class and are ineligible for winning any other free SQLHA LLC giveaway for 12 months after winning the seat in the class (excluding any giveaways in the class). If you cannot attend the class where you are chosen as a winner, you forfeit the prize.
  3. Do not enter if you cannot attend; it is not fair to those who can and a waste of everyone’s time.
  4. You have to get yourself to the class. That means you are responsible for all travel and expenses including, but not limited to: airfare, taxis, food, hotel, and so on. If you cannot meet this obligation for the class, please don’t enter. We’re just providing the class itself.
  5. Entries without the proper subject will be disqualified. Sorry.
  6. While we do not have delicate sensibilities, keep your entries clean.
  7. You are responsible for any taxes you may need to pay as a result of winning this contest.
  8. You must be eligible to win. For example, some who work in certain jobs or roles would be ineligible. Know if you can before you enter.
  9. All entries must be in English.
  10. While we understand that writing is not everyone’s forte, anyone who uses text speak such as ur will be disqualified as well. We have to have some standards, you know.

Not between the ages of 18 and 25 and still in or just out of school?

We have a few seats left in each class, so don’t miss your opportunity. Use the discount code GIVE20 to get 20% off of the Chicago classes. Don’t miss out!

Why SQLHA for your training needs?

Show your boss this. We offer the best in person training which includes labs. Keeping our class sizes smaller, our classes have great interaction. Between the labs and being in the room with one of the world’s recognized experts for availability on SQL Server, it doesn’t get much better.

A Letter to Myself at 20

By: on June 11, 2019 in Advice | No Comments

I don’t always participate in T-SQL Tuesday, but this month’s topic appealed to me. Hosted by Mohammad Darab (blog | Twitter), his idea is to write a letter to my 20 year old self and give some advice. I hope you find it both useful and entertaining. Enjoy!

Dear Allan,

You’ve learned a lot in just over a quarter of a century – sometimes not the way you would have liked, but hey, welcome to life. It’ll be a journey, not a destination. Below are some tips that will serve you well.

Working Harder is Not a Life Goal

Besides work, you will average over 50,000 miles a year on planes and play in two big bands as well as a few different small ensembles (among other things you do). Your schedule still tires people out (something you’ll hear often). You’re just as busy – if not busier – today than you were at 20. However, you require more than an hour or two of sleep. Remember to eat and drink. Listen to your body. You’re not invincible. You’re no good to anyone if you’re tired, run down, or sick. You’ll never have perfect work/life balance but taking time for yourself is important. Sometimes a simple recharge – it could be a quick power nap or a week off at the right time – can make all the difference in the world.

Turn off the computer and have a life outside of work. That last bit of whatever will still be there in the AM to finish. Life is about moments – as you get older and time goes by quicker, this becomes more apparent. Spend time with people and in places that matter because they – or you – could be gone tomorrow. At 20, 40 or 50 seems old. It’s not. You will experience loss and pain. Celebrate the good times, don’t wallow in the bad ones. Memories are forever; don’t miss them because you were stuck in front of a screen.

Try to save a few shekels along the way from all that hard work. Don’t pass up that Roboto mask or seeing Rush in Los Angeles in 2015, though. However, all the money in the world will not buy happiness or health. The former comes from within, the latter only if you do what I say above.

You Can Say “No”

“No” is an acceptable answer to many questions and situations. Saying “yes” to everything makes you a doormat, even if well intentioned. You can’t be all things to all people, nor will you please everyone. Also related: exposure bucks don’t pay the bills. Your time has value, even if you choose to give it for free. If you don’t and people don’t like it, that’s their problem. You will always have haters no matter how much good you do or help.

Make major decisions with the bigger picture in mind. Be able to pivot if necessary. You have a great gut instinct. Trust it.

Choose People Wisely

While you are ultimately responsible for yourself and your actions, surround yourself with people (friends, family, colleagues) that support and love you but at the same time can be honest and kick your behind when needed. You need people in your life who won’t put you on a pedestal. Having people in your corner that have your back and can offer trusted advice is crucial.

Unfortunately, some people will disappoint and hurt you both personally and professionally. When your Spidey sense is tingling, listen. Don’t let the haters and those who hurt you harden you or control your narrative. Rise above the noise.

Side people note: every interaction you have with people – good and bad – matters. You’ll see the impact of this more and more in the years to come. Be confidient and humble. You don’t know it all. Definitely don’t worry what people think or say.

Go with the Flow

You may or may not still be known to be outspoken, opinionated, and passionate (stop chuckling, people). As you get older you realize that life has a funny way of not always working out as you thought it might. Loosen up and go with the flow where possible. You have a terrible poker face, though. People will make memes about you (you’ll find out what a meme is). Have a laugh.

Worry about what you can control, don’t fret (too much) over what you can’t. This will serve you well personally and professionally. Always push forward, don’t live with regrets, and don’t let the stress consume you. Playing “what if” or living in the past is not productive.

Perfection Is the Enemy of Good (or Done …)

You are and will always be your own harshest critic. The bar of quality you set for your work is impossibly high. This is a blessing … and a curse.

Remember how you thought it’d be fun and awesome to write books? Yeah, about that. They’re rewarding, but a LOT of work. You won’t make much money, so being an author is not a “retire early and live on the royalties” plan. With your attention to detail and level of perfection expected (among other things that will happen along the way …)  the books you write are never simple to birth – think along the lines of having quintuplets. You will inevitably disappoint some people with how long it takes and feel bad about it. Get over yourself. Good and done is better than perfect and not done yet.

I’ll leave you with one last thought: Tokyo is every bit as amazing as you thought it would be, but if you can, get there about ten years earlier than I did. The CD shopping will be even more insane.


Your 47-Year-Old Self

Getting SQL Server 2019 CTP 3.0, Kubernetes, and Availability Groups Working

By: on June 10, 2019 in Availability Groups, Kubernetes, SQL Server 2019 | No Comments

Happy Monday everyone.

I haven’t written much about them yet (key emphasis there …) but AGs now being supported for containers in SQL Server 2019 is a big deal. Recently, SQL Server 2019 CTP 3.0 was released, but there’s a slight problem: if you try to deploy an AG with Kubernetes, you may see the following errors when trying to deploy the pods with the YAML that contains their definition. The services (i.e. instances of SQL Server) get created, but the pods do not.

YAML execution error (click to make bigger)

The fix, however, is easy. For CTP 3.0, a few things changed for the SQL Server operator. You have to modify the operator.yaml file with two changes which are documented below.

The first new thing is the word “update” is added to the verbs list in the section below.

The second is a new section that now must exist after MSSQL_K8S_NAMESPACE.

The updated operator.yaml file can be downloaded from my repository.

Also, remember in the YAML file where you define the pods, update the following two lines:


Hope this helps some of you.