Patching SQL Server Availability Group instance

Question

I would appreciate some advice please.

I need to patch a SQL 2019 Availability Group environment with two local replicas.

High level of steps I plan to take:

• Change failover mode from automatic to manual on primary
• Patch secondary replica first
• Failover the AG to the patched secondary
• Patch the new secondary (i.e. old primary)
• failback to old primary

Is it really critical for me to change Synchronous to Asynchronous commit mode on the Primary before starting?

My concern with doing this is that the instance in question has over 300 databases, some of which are very large. If I change the availability mode to asynchronous then when I finish patching the secondary, and failover the AG to the patched secondary, I will need to resume data movement on the new secondary (i.e. the old primary) on each database. For an instance of over 300 databases, this is a time consuming process.

My question therefore is:

• If I am able to secure downtime for the task, and I don't have transactions going on during the patching process, is it safe to carry on the process without changing to asynchronous commit mode? In which case, all I need to do is change to manual failover, patch secondary, failover to patched secondary, then patch new secondary (i.e. old primary) and failback to old primary.

Also, if for whatever reason, I am unable to secure downtime for the task, and the primary is in use while the secondary is being patched. Will I be running a risk of data loss if I leave the availability mode as synchronous during the patching process?

Kindly advise please.

thank you.

Answer 1

I hope you have read Microsoft's guide about Upgrade availability group replicas.

Is it critical for me to change Synchronous to Asynchronous commit mode on the Primary before starting?

From the above link:

To prevent the AG from unintended failovers during the upgrade process, remove availability failover from all synchronous-commit replicas before you begin.

As long you are fully synced between replicas, the critical part is "Remove automatic failover on all synchronous-commit replicas".

For an instance of over 300 databases, this is a time consuming process.

Did you test this with a TSLQL script instead of using it individually from the GUI?

If I am able to secure downtime for the task, and I don't have transactions going on during the patching process, is it safe to carry on the process without changing to asynchronous commit mode? In which case, all I need to do is change to manual failover, patch secondary, failover to patched secondary, then patch new secondary (i.e. old primary) and failback to old primary.

Yes.

Before failing over an AG, verify that the synchronization state of the failover target is SYNCHRONIZED.

I am unable to secure downtime for the task, and the primary is in use while the secondary is being patched. Will I be running a risk of data loss if I leave the availability mode as synchronous during the patching process?

No, as long as you change automatic failover to manual to avoid accidental failover. Transaction logs will not truncate unless your transactions are copied to all secondary replicas.

• PTL_SQL: thank you for your responses, much appreciated and all noted. Yes, i have read the article in that link. Thanks again.