Why Load Balancer required in Azure VM AAG ?

In an on-premises SQL Server Always On Availability Group, the Windows Server Failover Cluster moves the Listener's virtual IP between cluster nodes during failover using standard network protocols such as ARP, so no load balancer is needed. 

In Azure, VMs cannot move or claim a virtual IP in the same way because Azure uses software-defined networking. Therefore, an Azure Load Balancer (or a DNN listener in newer deployments) provides a stable listener endpoint and routes client connections to the current primary replica after failover.

Here's why it's needed:

1. Availability Group Listener needs a single endpoint

Applications should not connect directly to individual SQL Server VMs because the primary replica can change during failover.  Instead, they connect to an Availability Group (AG) Listener, which provides:

• A single DNS name (for example, SQLAGListener)
• A single IP address

The listener always points to whichever SQL Server instance is currently the primary replica.

2. Azure networking works differently from on-premises

In an on-premises Windows Server Failover Cluster:

• The cluster can move a virtual IP (VIP) between nodes during failover.
• The network automatically updates ARP tables, so clients reach the new active node. (ARP =Address Resolution Protocol , kind of mapping b/w IP with MAC/Physical address)

In Azure

• You cannot move an IP address between VM network interfaces the same way.
• Azure virtual networking does not support the traditional floating IP mechanism used by Windows Failover Clustering.  (Azure never exposes MAC/Physical address to public, that's reason we need Load balancer 

Because of this limitation, Azure uses an Azure Load Balancer to present a stable frontend IP while directing traffic to the active SQL Server VM.

3. Health probes identify the active node

The Azure Load Balancer continuously checks a configured health probe.

• Primary replica → responds to the probe.
• Secondary replica → does not respond.

The load balancer sends SQL traffic only to the VM that is healthy (the current primary).

Example:

Application
      |
AG Listener (listener.contoso.com)
      |
Azure Load Balancer
      |
-----------------------------
|                           |
VM1 (Primary)         VM2 (Secondary)
Probe: Healthy        Probe: Unhealthy

If failover occurs:

Application
      |
AG Listener
      |
Azure Load Balancer
      |
-----------------------------
|                           |
VM1 (Secondary)       VM2 (Primary)
Probe: Unhealthy      Probe: Healthy

The application continues using the same listener name without needing any connection string changes.

4. Enables automatic failover

Without a load balancer:

• Clients would connect directly to a specific VM.
• After failover, connections would fail until the application was reconfigured or DNS updated.

With a load balancer:

• The frontend IP remains the same.
• Traffic is automatically redirected to the new primary.
• Existing applications continue using the same listener.

5. Why Floating IP (Direct Server Return) is enabled

The load-balancing rule is typically configured with Floating IP (Direct Server Return) enabled because:

• SQL Server Availability Group listeners require this behavior.
• It allows the listener IP to function correctly with the Windows Failover Cluster.