Always On: Availability Groups and FCIs | Guided by A Guide to Cloud

Always On technologies

Simple explanation

Always On Availability Groups (AGs) are like having a team of scribes copying the same document. One scribe (primary) writes, and the others (secondaries) make exact copies in real time. If the primary scribe is sick, a secondary takes over immediately.

Failover Cluster Instances (FCIs) are like a shared office — one person works at the desk at a time, but if they leave, someone else sits down and picks up exactly where they left off. The desk (shared storage) stays the same.

Always On Availability Groups

AG architecture on Azure VMs

Component	Purpose
Primary replica	Handles all read-write workloads
Secondary replicas	Receive replicated data; can be readable
Listener	Virtual network name + IP — applications connect here
Synchronous mode	Zero data loss (RPO = 0), slight latency
Asynchronous mode	Better performance, possible data loss (RPO > 0)

Setting up AGs on Azure VMs

Create a Windows Server Failover Cluster (WSFC) across 2+ Azure VMs
Enable Always On in SQL Server Configuration Manager
Create the AG — define primary and secondary replicas
Configure the listener — Azure Load Balancer required for the VIP
Add databases to the AG

-- Create the Availability Group
CREATE AVAILABILITY GROUP NorthStarAG
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE NorthStarERP, NorthStarHR
REPLICA ON
    'SQL-VM-1' WITH (
        ENDPOINT_URL = 'TCP://SQL-VM-1:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)),
    'SQL-VM-2' WITH (
        ENDPOINT_URL = 'TCP://SQL-VM-2:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

-- Create the listener
ALTER AVAILABILITY GROUP NorthStarAG
ADD LISTENER 'NorthStarAGListener' (
    WITH IP ((N'10.0.1.100', N'255.255.255.0')),
    PORT = 1433);

Azure Load Balancer for AG listener

On Azure VMs, the AG listener requires an Azure Load Balancer (or distributed network name for Windows Server 2019+):

Load Balancer provides the floating virtual IP
Health probe on port 59999 detects which VM is the primary
Only the primary responds to the probe → traffic routes there
Alternative: Windows Server 2019+ supports Distributed Network Name (DNN) — no load balancer needed, simpler setup

AGs on Managed Instance

MI uses AGs internally for built-in HA (Business Critical tier). For cross-region DR:

MI failover groups use distributed AG technology under the hood
You don’t configure AGs directly on MI — failover groups handle it
The MI link (to on-prem) also uses distributed AG technology

Failover Cluster Instances (FCIs)

FCIs provide instance-level failover — the entire SQL Server instance fails over to another node.

FCI architecture on Azure VMs

Component	Purpose
Active node	Runs the SQL Server instance
Passive node(s)	Stand by, ready to take over
Shared storage	Azure Shared Disks, Storage Spaces Direct, or Azure Premium File Share
WSFC	Windows cluster manages failover detection

Shared storage options

FCI Storage Options on Azure
Storage	Performance	Availability Zones	Complexity
Azure Shared Disks	Good (Premium SSD)	Limited (zone constraints)	Low
Storage Spaces Direct (S2D)	Excellent (local NVMe)	Yes (cross-zone)	High
Azure Premium File Share	Good	Yes (ZRS)	Low

AG vs FCI comparison

Availability Groups vs Failover Cluster Instances
Feature	Availability Groups	Failover Cluster Instances
Failover scope	Database-level	Instance-level (all databases)
Shared storage	No (each replica has own storage)	Yes (required)
Readable secondaries	Yes	No (passive nodes can't serve queries)
Multiple databases	Selected databases join the AG	All databases on the instance
System databases	Not replicated	Shared (on shared storage)
Azure platform	SQL on VMs (full), MI (built-in)	SQL on VMs only
SQL Server edition	Enterprise (Standard with 1 DB limitation)	Enterprise or Standard

Question

What is the key architectural difference between AGs and FCIs?

Click or press Enter to reveal answer

Answer

AGs replicate at the database level — each replica has its own storage. FCIs failover at the instance level — all nodes share the same storage, and only one node is active.

Click to flip back

Question

Why does an AG listener on Azure VMs need an Azure Load Balancer?

Click or press Enter to reveal answer

Answer

Azure doesn't support gratuitous ARP. The Load Balancer provides the floating virtual IP and uses a health probe to detect which VM is the primary replica, routing traffic accordingly.

Click to flip back

Question

What shared storage options exist for FCI on Azure VMs?

Click or press Enter to reveal answer

Answer

Azure Shared Disks (simplest, zone constraints), Storage Spaces Direct (best performance, complex), and Azure Premium File Shares (zone-redundant, moderate performance).

Click to flip back

Knowledge Check

Kenji needs database-level HA with readable secondaries for offloading report queries on Azure VMs. Which solution should he implement?

🎬 Video coming soon

Next up: Log Shipping and HA/DR Operations — configure log shipping and manage HA/DR monitoring and troubleshooting.