Home / Azure

AzureRunbookAdvanced

SQL Server Always On availability groups on Azure VMs: multi-subnet, zones and no load balancer

Build a two-node SQL Server AG across availability zones using a multi-subnet design that removes the need for an Azure load balancer, with a cloud witness and tested failover.

Deepak Bhardwaj2 min read

Applies toSQL Server 2019/2022 Enterprise on Azure VMs, Windows Server 2019/2022 failover clustering

Architecture

 Zone 1: vm-sql1 (subnet snet-sql-1, 10.1.1.0/27)   Zone 2: vm-sql2 (subnet snet-sql-2, 10.1.2.0/27)
             └──────────── WSFC + AG ───────────────┘
 Cloud witness: storage account (ZRS)    Listener: one IP per subnet

Placing each replica in its own subnet lets the WSFC and AG listener use multiple IPs. Clients with MultiSubnetFailover=True connect to whichever IP is online, so no Azure Load Balancer or Distributed Network Name workaround is needed.

1. VMs and network

  • Two VMs, same size, zones 1 and 2 (add a third replica in zone 3 for read scale or extra protection).
  • Each VM gets a primary IP plus secondary IPs reserved for the cluster IP and the listener IP in its subnet.
  • Storage per the standalone SQL VM post (data ReadOnly cache, log no cache).
  • Domain-joined (AD DS or Entra Domain Services).

2. Cluster

Install-WindowsFeature Failover-Clustering -IncludeManagementTools   # both nodes
New-Cluster -Name sqlclu01 -Node vm-sql1, vm-sql2 -StaticAddress 10.1.1.10, 10.1.2.10 -NoStorage -ManagementPointNetworkType Singleton
Set-ClusterQuorum -CloudWitness -AccountName stwitnesscin -AccessKey <key>

Tune for Azure (relaxed thresholds avoid failovers on short network blips):

(Get-Cluster).SameSubnetThreshold = 15
(Get-Cluster).CrossSubnetThreshold = 20
(Get-Cluster).SameSubnetDelay = 1000
(Get-Cluster).CrossSubnetDelay = 1000

3. Availability group

-- on both: enable Always On in SQL Server Configuration Manager, restart, create endpoints
CREATE ENDPOINT hadr_endpoint STATE = STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL);

-- primary
CREATE AVAILABILITY GROUP AG1
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON)
FOR DATABASE Sales
REPLICA ON
 'VM-SQL1' WITH (ENDPOINT_URL = 'TCP://vm-sql1.contoso.local:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC),
 'VM-SQL2' WITH (ENDPOINT_URL = 'TCP://vm-sql2.contoso.local:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC);

ALTER AVAILABILITY GROUP AG1 ADD LISTENER 'ag1-listener' (
  WITH IP (('10.1.1.11','255.255.255.224'), ('10.1.2.11','255.255.255.224')), PORT = 1433);

On the secondary: ALTER AVAILABILITY GROUP AG1 JOIN; ALTER AVAILABILITY GROUP AG1 GRANT CREATE ANY DATABASE;

4. Client connection string

Server=tcp:ag1-listener,1433;Database=Sales;MultiSubnetFailover=True;Encrypt=True;

Older clients without MultiSubnetFailover support: set the listener's RegisterAllProvidersIP=0 and lower HostRecordTTL (for example 60), accepting slower reconnects.

5. Things the AG doesn't copy

Logins (with matching SIDs), SQL Agent jobs, linked servers, credentials. Script them to the secondary, or use contained AGs (SQL Server 2022).

6. Test failover

-- on the secondary that should become primary
ALTER AVAILABILITY GROUP AG1 FAILOVER;

Then test an unplanned case: stop the primary VM and measure how long the app takes to reconnect.

Tip

Synchronous commit across zones adds the inter-zone round-trip (usually ~1–2 ms) to every commit. Measure HADR_SYNC_COMMIT waits before and after go-live.

Related fixes