How does automated patching work?

Question

How does automated patching work for a 3-node Always On AG cluster with SQL on Azure VMs? Does it use a rolling update process that leaves the original primary node still as primary after the patching is completed?

Answer 1

The automated patching of SQL server on Azure VM depends on SQL Server IaaS Agent Extension. The SQL Server IaaS agent extension helps the automated management and administration tasks.

Refer: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management?view=azuresql&tabs=azure-powershell

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-patching?view=azuresql

During the patching of the Always on Availability Group, the primary node can change as part of the patching process. E.g. if the primary node needs to be restarted or it becomes unavailable, the AG automatically fails over.

Hope this helps

Answer 2

Hello Cooper, David A. [ISS] (Contractor)

In an Always On Availability Group (AG) cluster with SQL on Azure VMs, automated patching typically uses a rolling update process to apply patches to the nodes in the cluster.

The process works as follows:

1. The patch is first applied to the secondary replica nodes one at a time.
2. During the patching process, the secondary replica nodes are disconnected from the primary replica node to avoid any potential data conflicts.
3. Once the patching is complete on a secondary replica node, it is reconnected to the primary replica node and resumes synchronizing with the primary replica node.
4. After all secondary replica nodes have been patched, the primary replica node is then patched in the same way as the secondary replica nodes.
5. Once the patching is complete on the primary replica node, it is reconnected to the secondary replica nodes and resumes serving as the primary replica node.

During this process, the original primary node may still be the primary node after patching is completed. However, it is possible that another replica node could be selected as the primary node depending on the specific failover settings and the state of the nodes in the cluster.

It's important to note that the exact process for automated patching may vary depending on the specific configuration of the Always On AG cluster, the version of SQL Server, and other factors. It's recommended to review the documentation provided by Microsoft or consult with a certified Azure expert for guidance on how to best configure automated patching for your specific environment.

Please accept as answer and upvote if the above information is helpful for the benefit of the community.