Availability Set for SQL Server on Azure VMs

Question

(Not sure if this should asked in Azure forum or SQL Server forum...)

We do manual patching of our 3-node on-prem Always On SQL clusters using rolling updates: patch DR secondary, patch HA secondary, fail over, patch new HA secondary, fail back.

Now we're building a 3-node SQL cluster on Azure VM's.

Are availability sets relevant feature if we will still do our manual patching process on the Azure cluster? I searched but couldn't find much on Always On cluster relationship to availability sets if any.

Answer 1

Hello, @David C !

Are availability sets a relevant feature if we do our patching manually on Azure SQL Server VMs?

Yes. Always On availability groups on Azure Virtual Machines still need to take into consideration redundancy provided by availability sets or availability zones:

Always On availability group on SQL Server on Azure VMs

To increase redundancy and high availability, SQL Server VMs should either be in the same availability set, or different availability zones. Placing a set of VMs in the same availability set protects from outages within a data center caused by equipment failure (VMs within an Availability Set do not share resources) or from updates (VMs within an availability set are not updated at the same time). Availability Zones protect against the failure of an entire data center, with each Zone representing a set of data centers within a region. By ensuring resources are placed in different Availability Zones, no data center-level outage can take all of your VMs offline.

So even if we ignore the benefits of update domains, fault domains provide redundancy protection against transient hardware failures and network or power interruptions.

Additional reading:

• Always On availability group on SQL Server on Azure VMs
• Availability sets overview

Answer 2

Hello David C

Thanks for posting your question in Q&A

Availability sets are relevant for high availability in Azure VMs, even if you are doing manual patching. The purpose of availability sets is to ensure that your VMs are spread across multiple update domains, so that if there is a planned maintenance event, only one update domain is impacted at a time. This helps to ensure that your VMs are highly available during maintenance events.

In your case, you can still use the manual patching process you described for your 3-node SQL cluster on Azure VMs, but you should also place the VMs in an availability set to ensure that they are spread across multiple update domains. This will help to ensure that your VMs are highly available during maintenance events, even if you are doing manual patching.

For more information on availability sets and how to manage the availability of your VMs in Azure, you can refer to the following article: https://github.com/MicrosoftDocs/azure-docs/blob/main/articles/virtual-machines/maintenance-and-updates.md

Let me know if you have any other questions. I'd be happy to help.

If this does answer your question, please feel free to mark it as the answer as a token of appreciation.

Answer 3

Hi @David C,

When you patch your Azure VMs manually, you can use the availability set to ensure that the instances are spread across multiple VMs and minimize the risk of downtime. You would patch one VM at a time, just as you would in an on-premises environment, and then fail over the availability group to the updated VM before patching the next one.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".