enter image description here

Our database structure utilizes two roles in the Window Failover Cluster Manager.

  • One is for FCI - switching between the active/passive node in the Primary Data center.
  • One is for SQL Availability Group setup to copy data from the primary data center to the DR data center. This also use for manual failover in the case of disaster scenario occurs.

Our IT team wants to perform OS patching on these servers regularly on a scheduled down time, the application will be offline when this happens. This helps us out a bit because we don't have to worry about failover to a node while patching another, but instead we can assume we can bring the database offline completely during patching period.

My question is what is the best way to do this since IT team probably does not know much about FCI or Database. I don't want our databases to failover unexpectedly because during this process one of the active SQL node may needs reboot. I'm thinking of giving them the following instruction.

  1. RDP into the node active Cluster Group, you can find this by running the powershell command: Get-ClusterGroup and find the OwnerNodeValue of the "Cluster Group".

enter image description here

  1. Open up Failover Cluster Manager Expands to Roles, there are two roles one for AG and one for FCI.
  2. Right click on each and select "Stop Role"
  3. Perform patch, restart ect..
  4. Once done, RDP back in and "Start Role" on the two roles to resume.

Would this work and is it considered good practice? I am hoping to force the failover service to stop so that the SQL nodes don't fail-over to another node during a server reboot.

  • 1
    Just by curiosity... Why do you want to make sure they won't failover from one node to the other in your primary DC ? Both node should be able to handle the load of being primary so a failover should not be seen as a "bad thing"... Usually, patching strategy is to patch the secondary then failover on it and make sure everything works fine before patching the primary node. That way, if there is an issue with the patch, you won't break all of your nodes. Jul 14 '20 at 17:24
  • You have a point, though that is only work for the Primary data center because it is automatic failover. Our DR data center does not have automatic fail over, I wonder if the Cluster Group go down would cause funny or unexpected behavior in the DR nodes. Even if it works flawlessly the Cluster Group now fail over to the DR node, we will have to manually intervene to fail it back to the primary data center post patch.
    – Fylix
    Jul 14 '20 at 17:26
  • 1
    If your AG doesn't have the automatic failover (which it typical for DR), it won't failover to DR. Technically, you could patch your DR site first but without moving anything there Jul 14 '20 at 17:55
  • 4
    Does this answer your question? patching SQL Server with Availability Group and Secondary Replica - OS and SQL Patching can follow this same strategy. Jul 14 '20 at 19:13

Approaching this by thinking about each SQL instance separately.

Patching SQLDR\Inst_B

This is a secondary replica, so it can be patched/rebooted independently.

  1. Switch SQLDR\Inst_B to be an ASYNC replica (if it's not already)
  2. Patch SQLDB\Inst_B and reboot (windows patches, SQL patches, etc.)
  3. Verify SQLDR comes back up and the AG is healthy.
  4. Switch back to SYNC if required.

Notes: While Inst_B is offline, the T-Logs on Inst_A will be kept around to replay when Inst_B returns. I wouldn't recommend patching Inst_B while doing index maintenance or large data loads on Inst_A. If Inst_B is down for a long period of time (think days) then you'll probably want to break the AG replica.

Patching SQLFCIPrimary\Inst_A

  1. Patch passive node and reboot.
  2. Failover to Passive node. This will cause your (hopefully brief, e.g. < 1 min) outage.
  3. Confirm AG is healthy
  4. Patch new passive node and reboot.
  5. Failback if required.

Notes: Since you are using MANUAL failover, there is no risk that you'll inadvertently failover to Inst_B.

This is the simplest way to accomplish regular patching, and just needs one or two FCI failovers. There is no reason to stop WSFC roles.

Whether you do Inst_A or Inst_B first doesn't matter, but I'm risk adverse, so I'd start with Inst_B.

Not the answer you're looking for? Browse other questions tagged or ask your own question.