0

So this week I was patching our SQL Servers that host our availability groups. Never had an issue with them in the past, fairly standard process. I have two servers (call them SQL1 and SQL2) in the cluster and one Availability Group (AG1) uses SQL1 as the primary, and three Availability Groups (AG2-4) uses SQL2 as its primary. They were running v15.0.4013.40, so Server 2019 and this week I had to install CU3 and CU4.

My process was:

  1. I moved AG1 to use SQL2 as the primary
  2. Proceed to patch SQL1
  3. Switch all availability groups to async commit
  4. Reboot SQL1
  5. Return all availability groups to sync commit
  6. Return AG1 to use SQL1 as the primary

In step 2 I patched both CU3 and then CU4 successively. Then, when trying to switch all AGs to sync commit, the databases threw errors in the Availability Group Dashboard and they wouldn't allows access to their databases. I couldn't see any logged reason to why it refused to do this. It was only resolved when I then patched SQL2 with both CUs, and it again allowed to sync commit.

My question is did anyone else have this issue? Was this likely due to me trying to be efficient and patch two CUs in the same interval without doing it one by one? Just trying to work out how I don't do this again as it caused me quite the headache

Improve this question
4
  • I do not see reason as to why change the AG mode to Async Commit. You have to just remove automatic failover, just in case but there is no need to change the commit mode.
    – Shanky
    Apr 7, 2020 at 13:09
  • Your description is somewhat vague ("they wouldn't allow access to their databases") so unless someone has run across this issue, or has the time and motivation to set up a repro, it's unlikely you'll get a good answer here. In general, as Shanky mentioned, switching to asyn commit during upgrades is not a common practice, and if someone did do that, they would probably wait until both nodes were upgraded before switching back. The CU install may have had nothing at all to do with the issue either--no way to know without a repro attempt.
    – Tony Hinkle
    Apr 7, 2020 at 13:20
  • @TonyHinkle In the past I would have issues when rebooting a node when in synchronous commit, though yes I've always considered it might not be necessary. When I say it wouldn't allow access, logins to the database failed. I guess I hoped someone might have come across the same thing, or at least let me if applying two CUs at once was a bad idea
    – TEDDY HORTON-TURNER
    Apr 7, 2020 at 14:08
  • 2
    You don't need to apply 2 CUs at the same time as they are cumulative, so if you are at CU2 and want to get to CU4, you just install CU4--there is no need to install CU3. We reboot nodes that are synchronous every week with no issues, but they are all 2014 and 2016.
    – Tony Hinkle
    Apr 7, 2020 at 15:10

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.