Best way to build an AAG cluster in Azure? Do yo uuse the AZ Port template, the Market Place template "SQL with High Avaialability" or build the SQL VMs separatelyu and add them to a WGC and create the AAGS?

Question

Hello. We have used the Az Market place template, "SQL Server With High Availability" to build two Az SQL Server 2022 2-Node Active AAG clusters each with an Az file share witness. But have hit problems with it - can't increase storage without evicting the each node from the cluster, although the storage is not shared? Other issue: both nodes sometimes stop, causing an outage. Some of these we think were due to patching by Az Update Manager and both nodes going down for patching at the same time - so need to put them in an Avaialbility set- but the template won't let you, and you can't seem to be able to do it afterwords. Other occurrences, not found the cause yet. Any advice/ideas appreciated, as never had this hassle on Prem? P.S We tried Az Man Instance, Nov 2022 release (Kerberos support), but its Kerberos Auth didn't work reliably, not with our AD anyway.

Answer 1

Hi @Ewen Miller,

Thanks for the response.You've encountered several issues with the Azure Marketplace template for SQL Server with High Availability. Here are some suggestions to address these challenges:

1.As you mentioned, manually configuring your SQL Server Always On Availability Groups (AAG) can provide more control and flexibility. This approach allows you to place your VMs in an Availability Set or across Availability Zones, which can help mitigate issues with patching and node outages.

2.For the storage issue, consider using Azure Managed Disks, which can be resized without downtime. This can help avoid the need to evict nodes from the cluster.

3.Implement a rolling update strategy to avoid both nodes being patched simultaneously. You can use Azure Update Management to schedule updates and ensure that only one node is updated at a time.

4.Adjusting the HEALTH_CHECK_TIMEOUT to 60000 and considering a change to FAILURE_CONDITION_LEVEL to 2, as suggested by MS Support, can help improve the stability of your cluster. These settings can help prevent unnecessary failovers and improve the overall health monitoring of your cluster.

5.If possible, configure your AAG in a multi-subnet environment within the same Azure virtual network. This setup can simplify your deployment and eliminate the need for an Azure Load Balancer or distributed network name (DNN) for your AAG.

Here is a similar thread that might be helpful to you: https://learn.microsoft.com/en-us/answers/questions/1810594/deploying-sql-server-with-high-availability-fails

For more information, please refer the document: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-prerequisites-tutorial-multi-subnet?view=azuresql

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.