Building a SQL Server Availability Group from Scratch: On-Premises and Azure

The request came in at 11:00 on a Wednesday: build a new Availability Group before the weekend change window. Two SQL Server 2022 instances, on-premises, an existing WSFC cluster, four databases totalling 900 GB. Automatic seeding was selected to avoid a four-hour manual backup-restore cycle.

The AG was created successfully. The databases did not appear on the secondary. The seeding state in the DMV read IN_PROGRESS for eleven minutes, then moved to FAILED. The error was not logged in the obvious places. The secondary was missing the GRANT CREATE ANY DATABASE permission that automatic seeding requires. The permission had not been in any checklist the team used.

The answer

Building an Always On Availability Group requires satisfying a set of prerequisites that span Windows, networking, SQL Server configuration, and account permissions before the first T-SQL statement is written. Seeding — the process of initialising the secondary databases — can be done manually with backup and restore, or automatically by SQL Server, but the two methods have different prerequisites, different failure modes, and significantly different operational trade-offs. Monitoring seeding is not optional: both methods transfer large volumes of data over the availability group endpoint and can fail silently without the right queries watching them.

What you will learn

• What Windows, network, and SQL Server prerequisites must be in place before creating an AG — and how they differ between on-premises and Azure VMs
• Which SQL Server and Windows permissions are required, and by which accounts
• How to create the availability group using T-SQL
• How manual and automatic seeding work, when to use each, and what each requires
• How to monitor seeding progress in real time and detect failures before they go silent

Scope: SQL Server 2016–2022 Always On Availability Groups. Automatic seeding (SEEDING_MODE = AUTOMATIC) was introduced in SQL Server 2016 and extended in subsequent releases. On-premises procedures assume Windows Server 2016 or later and an existing or new Windows Server Failover Cluster. Azure VM procedures assume standard SQL Server Marketplace images. Behaviour varies by AG topology, SQL Server edition, and network environment. Validate all observations in your environment before applying them to production.

Prerequisites

Windows and cluster prerequisites (both environments)

Prerequisite: A healthy Windows Server Failover Cluster must exist before any AG work begins. If the cluster has not been built yet, see Building a Windows Server Failover Cluster for SQL Server: On-Premises and Azure for the full WSFC build procedure, including AD permissions, firewall rules, quorum configuration, and Azure NSG rules.

Before creating an Availability Group, every node that will host a replica must meet the following:

• Windows Server 2016 or later (Windows Server 2022 recommended for SQL Server 2022).
• The Failover Clustering Windows feature installed on all nodes.
• All nodes must be members of the same Windows Server Failover Cluster (WSFC) — or, for a Distributed AG, separate clusters. A basic AG cannot span nodes that are not in the same cluster.
• All nodes must be joined to the same Active Directory domain. SQL Server service accounts, computer accounts, and the cluster name object (CNO) require domain membership.
• Cluster validation must pass (or pass with reviewed warnings) before the cluster is used for production workloads.

-- Validate all intended nodes together
Test-Cluster -Node @('SQL-NODE1', 'SQL-NODE2')

Review all warnings. Network-related warnings should not be dismissed in stretched topologies.

Network prerequisites

The AG replication endpoint uses TCP, port 5022 by default. Every replica must be able to reach every other replica on this port in both directions.

Additional ports:

Verify endpoint connectivity between nodes before proceeding:

Test-NetConnection SQL-NODE2.contoso.com -Port 5022
Test-NetConnection SQL-NODE1.contoso.com -Port 5022

On-premises firewall rules

Open the required ports on the Windows Firewall on every replica node. Run on each node:

# AG HADR endpoint
New-NetFirewallRule `
    -DisplayName "SQL Server HADR Endpoint (TCP 5022)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 5022 `
    -Action Allow

# SQL Server instance
New-NetFirewallRule `
    -DisplayName "SQL Server Instance (TCP 1433)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 1433 `
    -Action Allow

# WSFC cluster communication
New-NetFirewallRule `
    -DisplayName "WSFC Cluster Communication (TCP 3343)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 3343 `
    -Action Allow

New-NetFirewallRule `
    -DisplayName "WSFC Cluster Communication (UDP 3343)" `
    -Direction Inbound `
    -Protocol UDP `
    -LocalPort 3343 `
    -Action Allow

# RPC endpoint mapper
New-NetFirewallRule `
    -DisplayName "RPC Endpoint Mapper (TCP 135)" `
    -Direction Inbound `
    -Protocol TCP `
    -LocalPort 135 `
    -Action Allow

If a rule with the same display name already exists, use Set-NetFirewallRule targeting the rule name instead of New-NetFirewallRule. After applying rules, re-run Test-NetConnection to confirm port 5022 is reachable bidirectionally between all replica nodes before proceeding.

Azure-specific prerequisites

On Azure VMs, several networking differences apply versus on-premises:

• No multicast or broadcast: WSFC requires unicast cluster communication. Azure VMs operate in unicast mode by default; no additional configuration is needed for this, but it means split-brain detection works differently.
• Cloud witness instead of a file-share witness: A file-share witness hosted on an Azure VM introduces a single point of failure. Use an Azure Storage Account as a cloud witness instead. The storage account must be General Purpose, Standard tier with locally redundant storage. Every cluster node must reach <account>.blob.core.windows.net over TCP 443.
• Azure Load Balancer for the listener: Azure VMs cannot use a floating IP directly. The AG listener requires an Internal Load Balancer (ILB) in each region with a health probe rule pointing to the probe port (default 59999) on the SQL Server instances.
• No DHCP for the cluster IP: The cluster IP resource must use a static IP address within the subnet, not DHCP.
• Accelerated Networking: Enable Accelerated Networking on the SQL Server VM NICs for lower latency between replicas. This is strongly recommended for synchronous-commit replicas.

Configure the cloud witness:

$Parameters = @{
    CloudWitness = $true
    AccountName  = '<storage-account-name>'
    AccessKey    = '<storage-account-access-key>'
}

Set-ClusterQuorum @Parameters
Get-ClusterQuorum

Azure NSG rules

Azure VMs require Network Security Group (NSG) inbound rules to allow AG traffic. Apply these rules to the NSG associated with the SQL Server VM subnet. Run from a machine with the Az PowerShell module installed:

$nsg = Get-AzNetworkSecurityGroup `
    -ResourceGroupName '<resource-group>' `
    -Name '<nsg-name>'

# Allow HADR endpoint between replicas
Add-AzNetworkSecurityRuleConfig `
    -NetworkSecurityGroup $nsg `
    -Name 'Allow-HADR-Endpoint' `
    -Priority 200 `
    -Direction Inbound `
    -Access Allow `
    -Protocol Tcp `
    -SourcePortRange '*' `
    -DestinationPortRange '5022' `
    -SourceAddressPrefix 'VirtualNetwork' `
    -DestinationAddressPrefix '*' | Out-Null

# Allow SQL Server instance traffic
Add-AzNetworkSecurityRuleConfig `
    -NetworkSecurityGroup $nsg `
    -Name 'Allow-SQL-Instance' `
    -Priority 210 `
    -Direction Inbound `
    -Access Allow `
    -Protocol Tcp `
    -SourcePortRange '*' `
    -DestinationPortRange '1433' `
    -SourceAddressPrefix 'VirtualNetwork' `
    -DestinationAddressPrefix '*' | Out-Null

# Allow ILB health probe for the AG listener (port 59999)
# Source must be AzureLoadBalancer; a VirtualNetwork-scoped rule is not sufficient
Add-AzNetworkSecurityRuleConfig `
    -NetworkSecurityGroup $nsg `
    -Name 'Allow-ILB-Probe-Listener' `
    -Priority 220 `
    -Direction Inbound `
    -Access Allow `
    -Protocol Tcp `
    -SourcePortRange '*' `
    -DestinationPortRange '59999' `
    -SourceAddressPrefix 'AzureLoadBalancer' `
    -DestinationAddressPrefix '*' | Out-Null

# Allow ILB health probe for the WSFC cluster core IP (port 58888)
# The cluster core IP requires a separate frontend IP and probe port on the same ILB
Add-AzNetworkSecurityRuleConfig `
    -NetworkSecurityGroup $nsg `
    -Name 'Allow-ILB-Probe-Cluster' `
    -Priority 221 `
    -Direction Inbound `
    -Access Allow `
    -Protocol Tcp `
    -SourcePortRange '*' `
    -DestinationPortRange '58888' `
    -SourceAddressPrefix 'AzureLoadBalancer' `
    -DestinationAddressPrefix '*' | Out-Null

Set-AzNetworkSecurityGroup -NetworkSecurityGroup $nsg

The Allow-ILB-Probe rule is required even if port 59999 does not carry application traffic. Without it, the ILB health probe cannot reach the cluster probe port, the backend node is marked unhealthy, and listener traffic is not forwarded.

SQL Server prerequisites

On every instance that will host a replica:

• SQL Server Enterprise Edition (or Developer Edition for testing). Standard Edition supports Basic Availability Groups: one database per AG, no readable secondary, no automatic failover in some configurations.
• Always On Availability Groups must be enabled in SQL Server Configuration Manager. This requires a SQL Server service restart.
• The SQL Server and SQL Server Agent services must run under a domain account or a Group Managed Service Account (gMSA). Local accounts, Local System, and Network Service cannot authenticate against other nodes for HADR endpoint communication.
• All replicas must use the same SQL Server collation.
• Database files and backup paths do not need to match across replicas when using automatic seeding, but must be consistent for manual seeding unless explicit MOVE clauses are used in the restore.
• All databases added to the AG must be in the FULL recovery model.
• All databases must have at least one full backup taken after the FULL recovery model was set.

Verify Always On is enabled on each instance:

SELECT
    @@SERVERNAME                          AS instance_name,
    SERVERPROPERTY('IsHadrEnabled')       AS hadr_enabled,
    SERVERPROPERTY('IsClustered')         AS is_clustered,
    SERVERPROPERTY('Edition')             AS edition;

Expected on each replica:

hadr_enabled = 1
is_clustered = 1

Required permissions

This is the most frequently incomplete checklist item. Document it explicitly.

Windows level

The CNO must have permission to create and manage Virtual Computer Objects (VCOs) in Active Directory. In environments with restrictive AD delegation, the cluster VCO and listener VCO may need to be pre-staged by an AD administrator.

SQL Server level — HADR endpoints

Each SQL Server instance must be able to authenticate over its HADR endpoint. When service accounts are domain accounts, grant CONNECT permission explicitly on the endpoints:

-- Run on each instance
-- Replace service accounts with the actual domain\account values

-- Allow the secondary's service account to connect to the primary's endpoint
CREATE LOGIN [CONTOSO\sql-svc-node2] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CONTOSO\sql-svc-node2];

-- Allow the primary's service account to connect to the secondary's endpoint
-- (run this on the secondary)
CREATE LOGIN [CONTOSO\sql-svc-node1] FROM WINDOWS;
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [CONTOSO\sql-svc-node1];

When using certificates instead of Windows Authentication (common in workgroup or cross-domain environments), the certificate-based authentication procedure is described in the Microsoft documentation linked in the official references section.

SQL Server level — automatic seeding

Automatic seeding requires one additional permission on the secondary instance that is not required for manual seeding:

-- Run on each secondary instance
ALTER AVAILABILITY GROUP [<AG name>] GRANT CREATE ANY DATABASE;

Without this permission, automatic seeding will fail with an error similar to:

Msg 35250, Level 16, State 13
The connection to the primary replica is not active. The command cannot be processed.

Or silently transition to FAILED state in sys.dm_hadr_automatic_seeding. This is the most common cause of automatic seeding failures at the permission stage.

Create the HADR endpoint

Run on each instance. If the endpoint already exists (some SQL Server versions create it automatically), verify it is STARTED.

-- Check for existing endpoint
SELECT
    name,
    state_desc,
    role_desc,
    connection_auth_desc
FROM sys.database_mirroring_endpoints;

If no endpoint exists:

CREATE ENDPOINT Hadr_endpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING
    (
        ROLE = ALL,
        AUTHENTICATION = WINDOWS NEGOTIATE,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );

If the endpoint exists but is not STARTED:

ALTER ENDPOINT Hadr_endpoint STATE = STARTED;

Create the Availability Group

Run on the primary replica. Replace [AGName], server names, endpoint URLs, and database names with actual values.

CREATE AVAILABILITY GROUP [AGName]
WITH
(
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    FAILURE_CONDITION_LEVEL = 3,
    HEALTH_CHECK_TIMEOUT = 30000,
    DB_FAILOVER = OFF,
    DTC_SUPPORT = NONE,
    REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0
)
FOR DATABASE
    [DB1],
    [DB2]
REPLICA ON
    N'SQL-NODE1'
    WITH
    (
        ENDPOINT_URL = N'TCP://SQL-NODE1.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)
    ),
    N'SQL-NODE2'
    WITH
    (
        ENDPOINT_URL = N'TCP://SQL-NODE2.contoso.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
    );

Join the secondary instance to the AG (run on the secondary):

ALTER AVAILABILITY GROUP [AGName] JOIN;

Create the AG listener

An AG listener provides a single virtual network name and IP address that clients connect to regardless of which replica is currently primary. The cluster name resource (and associated DNS record) handles redirection after failover without requiring changes to connection strings.

On-premises listener

Run on the primary replica. The IP address must be static, in the correct subnet, and not already assigned to any other resource. The DNS name must be unique within the domain.

-- Single-subnet (most common case)
ALTER AVAILABILITY GROUP [AGName]
ADD LISTENER N'AGNameListener'
(
    WITH IP
    (
        (N'192.168.1.100', N'255.255.255.0')
    ),
    PORT = 1433
);

For stretched clusters or geographically separated sites with one subnet per site, add one IP address per subnet:

ALTER AVAILABILITY GROUP [AGName]
ADD LISTENER N'AGNameListener'
(
    WITH IP
    (
        (N'192.168.1.100', N'255.255.255.0'),  -- Site A
        (N'192.168.2.100', N'255.255.255.0')   -- Site B
    ),
    PORT = 1433
);

Verify the listener and its IP addresses:

SELECT
    agl.dns_name,
    agl.port,
    aglip.ip_address,
    aglip.ip_subnet_mask,
    aglip.is_primary
FROM sys.availability_group_listeners AS agl
JOIN sys.availability_group_listener_ip_addresses AS aglip
    ON aglip.listener_id = agl.listener_id
WHERE agl.dns_name = N'AGNameListener';

Verify the listener is reachable from a client machine:

Test-NetConnection AGNameListener.contoso.com -Port 1433

Active Directory note: The listener create operation creates a Virtual Computer Object (VCO) in Active Directory. In environments with restrictive AD delegation, the cluster computer account (CNO) must have permission to create child computer objects in the target OU, or the VCO must be pre-staged by an AD administrator before running ADD LISTENER.

Azure listener — Internal Load Balancer

Azure VMs cannot own a floating IP directly. The AG listener on Azure VMs requires an Internal Load Balancer (ILB) in front of the listener IP. The ILB uses a TCP health probe on a probe port (default 59999) to detect which node is current primary and routes listener traffic accordingly.

Step 1: Create the Internal Load Balancer

The ILB requires two frontend IPs: one for the AG listener, and one for the WSFC cluster core IP resource. Each must be a static private IP within the VM subnet that is not currently in use. Floating IP (Direct Server Return) must be enabled on every load balancing rule — without it, the return traffic path breaks and connections fail.

The two probe ports must be different from each other and must not fall within the OS dynamic port range in use on the cluster nodes (see Step 6 for port reservation).

$rg            = '<resource-group>'
$location      = '<azure-region>'   # e.g., 'westeurope'
$vnetName      = '<vnet-name>'
$subnetName    = '<subnet-name>'
$ilbName       = 'ilb-ag'

$listenerIP    = '10.0.1.100'       # Static IP for AG listener — must match ADD LISTENER T-SQL
$clusterCoreIP = '10.0.1.50'        # Static IP of the WSFC cluster core IP resource
$listenerProbe = 59999              # Probe port for AG listener
$clusterProbe  = 58888              # Probe port for WSFC cluster core — must differ from listener probe

$vnet   = Get-AzVirtualNetwork -ResourceGroupName $rg -Name $vnetName
$subnet = Get-AzVirtualNetworkSubnetConfig -VirtualNetwork $vnet -Name $subnetName

# Frontend IP 1 — AG listener
$feListener = New-AzLoadBalancerFrontendIpConfig `
    -Name             'fe-ag-listener' `
    -PrivateIpAddress $listenerIP `
    -SubnetId         $subnet.Id

# Frontend IP 2 — WSFC cluster core IP
$feCluster = New-AzLoadBalancerFrontendIpConfig `
    -Name             'fe-wsfc-core' `
    -PrivateIpAddress $clusterCoreIP `
    -SubnetId         $subnet.Id

$bePool = New-AzLoadBalancerBackendAddressPoolConfig `
    -Name 'be-sql-nodes'

# Health probe for AG listener
$probeListener = New-AzLoadBalancerProbeConfig `
    -Name              'probe-ag-listener' `
    -Protocol          Tcp `
    -Port              $listenerProbe `
    -IntervalInSeconds 5 `
    -ProbeCount        2

# Health probe for WSFC cluster core IP
$probeCluster = New-AzLoadBalancerProbeConfig `
    -Name              'probe-wsfc-core' `
    -Protocol          Tcp `
    -Port              $clusterProbe `
    -IntervalInSeconds 5 `
    -ProbeCount        2

# LB rule for AG listener — EnableFloatingIP required
$ruleListener = New-AzLoadBalancerRuleConfig `
    -Name                    'rule-ag-listener' `
    -FrontendIpConfiguration $feListener `
    -BackendAddressPool      $bePool `
    -Probe                   $probeListener `
    -Protocol                Tcp `
    -FrontendPort            1433 `
    -BackendPort             1433 `
    -EnableFloatingIP

# LB rule for WSFC cluster core IP — EnableFloatingIP required
$ruleCluster = New-AzLoadBalancerRuleConfig `
    -Name                    'rule-wsfc-core' `
    -FrontendIpConfiguration $feCluster `
    -BackendAddressPool      $bePool `
    -Probe                   $probeCluster `
    -Protocol                Tcp `
    -FrontendPort            $clusterProbe `
    -BackendPort             $clusterProbe `
    -EnableFloatingIP

New-AzLoadBalancer `
    -ResourceGroupName      $rg `
    -Name                   $ilbName `
    -Location               $location `
    -Sku                    Standard `
    -FrontendIpConfiguration @($feListener, $feCluster) `
    -BackendAddressPool     $bePool `
    -Probe                  @($probeListener, $probeCluster) `
    -LoadBalancingRule      @($ruleListener, $ruleCluster)

Step 2: Add the SQL Server VMs to the backend pool

$ilb    = Get-AzLoadBalancer -ResourceGroupName $rg -Name $ilbName
$bePool = Get-AzLoadBalancerBackendAddressPoolConfig -LoadBalancer $ilb -Name 'be-sql-nodes'

foreach ($vmName in @('SQL-NODE1', 'SQL-NODE2')) {
    $vm  = Get-AzVM -ResourceGroupName $rg -Name $vmName
    $nic = Get-AzNetworkInterface -ResourceGroupName $rg `
               -Name ($vm.NetworkProfile.NetworkInterfaces[0].Id -split '/')[-1]

    $nic.IpConfigurations[0].LoadBalancerBackendAddressPools = @($bePool)
    Set-AzNetworkInterface -NetworkInterface $nic
}

Step 3: Create the AG listener via T-SQL

The listener IP address must exactly match the ILB frontend IP.

-- Run on the primary replica
ALTER AVAILABILITY GROUP [AGName]
ADD LISTENER N'AGNameListener'
(
    WITH IP
    (
        (N'10.0.1.100', N'255.255.255.255')
    ),
    PORT = 1433
);

Step 4: Configure the probe port on the AG listener IP resource

Creating the listener via T-SQL creates an IP Address resource in WSFC under the AG role. Configure all cluster parameters on that resource so the node can respond to the ILB health probe. The SubnetMask must be 255.255.255.255 for Azure ILB — a host-specific route, not the subnet mask of the VNet subnet.

First, get the cluster network name (needed for the Network parameter):

# Run on any cluster node
Get-ClusterNetwork | Select-Object Name, Role, Address

Use the name of the network with Role = ClusterAndClient or Cluster. This is typically Cluster Network 1 but confirm in your environment.

Configure the AG listener IP resource:

# Run on any cluster node
$agName             = 'AGName'
$listenerIP         = '10.0.1.100'       # Must match the ILB frontend IP for the AG listener
$listenerProbePort  = 59999
$clusterNetworkName = 'Cluster Network 1' # Confirm name with Get-ClusterNetwork above

# Locate the IP Address resource under the AG role
$listenerIPResource = Get-ClusterResource |
    Where-Object {
        $_.ResourceType -eq 'IP Address' -and
        $_.OwnerGroup   -eq $agName
    }

if (-not $listenerIPResource) {
    throw "AG listener IP resource not found under group '$agName'. Confirm the listener was created via T-SQL."
}

# All five parameters are required; omitting any will cause the probe to fail
Get-ClusterResource -Name $listenerIPResource.Name |
    Set-ClusterParameter -Multiple @{
        Address    = $listenerIP
        ProbePort  = $listenerProbePort
        SubnetMask = '255.255.255.255'
        Network    = $clusterNetworkName
        EnableDhcp = 0
    }

Step 5: Configure the probe port on the WSFC cluster core IP resource

The WSFC cluster core IP (the IP resource in Cluster Core Resources, separate from the AG listener) also needs its own probe port configured against the ILB. This allows the cluster name itself to be reachable via the ILB frontend.

$clusterCoreIPName  = 'Cluster IP Address' # Default name; verify in Failover Cluster Manager
$clusterCoreIP      = '10.0.1.50'          # Must match the ILB frontend IP for the WSFC core
$clusterProbePort   = 58888

Get-ClusterResource -Name $clusterCoreIPName |
    Set-ClusterParameter -Multiple @{
        Address    = $clusterCoreIP
        ProbePort  = $clusterProbePort
        SubnetMask = '255.255.255.255'
        Network    = $clusterNetworkName
        EnableDhcp = 0
    }

Verify the cluster core IP resource name if the default does not apply:

Get-ClusterResource | Where-Object { $_.ResourceType -eq 'IP Address' } |
    Select-Object Name, OwnerGroup, State

Resources with OwnerGroup matching the cluster name (not the AG name) are the cluster core IP resources.

Step 6: Reserve probe ports from the OS dynamic port range

Run on every cluster node. The OS dynamic port range (49152–65535) can assign probe ports to other processes before the cluster resources claim them, causing the probe to fail intermittently. Reserve the ports explicitly:

# Run on each cluster node
netsh int ipv4 add excludedportrange tcp startport=59999 numberofports=1 store=persistent
netsh int ipv4 add excludedportrange tcp startport=58888 numberofports=1 store=persistent

# Verify the exclusions are active
netsh int ipv4 show excludedportrange tcp

Timing: Configure exclusions before bringing the cluster resources online. If the probe port is already in use when the exclusion is added, the command fails. In that case, stop the cluster resource, add the exclusion, then bring the resource back online.

Step 7: Bring cluster resources online and verify

# Bring the AG cluster role online
Start-ClusterGroup -Name $agName

# Confirm both the listener network name and IP resource are online
Get-ClusterResource |
    Where-Object { $_.OwnerGroup -eq $agName } |
    Select-Object Name, ResourceType, State

Confirm probe status in the Azure portal: navigate to the ILB → Health probe status. Only the node currently owning the AG primary role should show healthy for the listener probe. Only the node currently owning the cluster core resources should show healthy for the cluster core probe.

Verify the listener is reachable from a client machine:

Test-NetConnection AGNameListener.contoso.com -Port 1433

SQL IaaS Agent Extension: If the SQL Server IaaS Agent Extension (SqlIaasExtension) is installed and the AG was deployed through the Azure portal AG wizard, some of the ILB and probe port configuration steps may be handled automatically. The steps above apply to manual deployments regardless of extension status.

Seeding options

Seeding initialises the secondary databases. SQL Server 2016 and later support two methods: manual and automatic. The choice has material consequences for network bandwidth, backup chain integrity, and failure visibility.

Manual seeding

Manual seeding uses standard backup and restore. The DBA takes a full backup (and any log backups taken after the full) on the primary, copies them to the secondary, restores with NORECOVERY, and then joins the database to the AG.

When to use manual seeding:

• Large databases in bandwidth-constrained environments where you need to control transfer timing.
• When you want to restore from an existing backup set rather than triggering a new backup.
• When security policy requires encrypted backup files rather than data flowing unencrypted over the AG endpoint.
• When the secondary already has a copy of the database (disaster recovery standby being converted to an AG member).

Procedure:

On the primary, take a full backup and a log backup:

BACKUP DATABASE [DB1]
TO DISK = N'\\backup-share\DB1_FULL.bak'
WITH FORMAT, COMPRESSION, STATS = 10;

BACKUP LOG [DB1]
TO DISK = N'\\backup-share\DB1_LOG1.trn'
WITH COMPRESSION, STATS = 10;

On the secondary, restore with NORECOVERY (do not use RECOVERY — the database must remain in restoring state):

RESTORE DATABASE [DB1]
FROM DISK = N'\\backup-share\DB1_FULL.bak'
WITH
    NORECOVERY,
    MOVE N'DB1_Data' TO N'D:\Data\DB1.mdf',
    MOVE N'DB1_Log'  TO N'L:\Log\DB1_log.ldf',
    STATS = 10;

RESTORE LOG [DB1]
FROM DISK = N'\\backup-share\DB1_LOG1.trn'
WITH NORECOVERY, STATS = 10;

Join each restored database to the AG on the secondary:

ALTER DATABASE [DB1] SET HADR AVAILABILITY GROUP = [AGName];

Automatic seeding

Automatic seeding (SEEDING_MODE = AUTOMATIC) transfers a backup stream directly from the primary to the secondary over the HADR endpoint without the DBA manually managing backup files. SQL Server performs an internal VDI backup on the primary and streams it to the secondary as part of the seeding process.

When to use automatic seeding:

• When all databases are in the same relative path on both replicas (or the secondary can create any database — see CREATE ANY DATABASE permission above).
• When network bandwidth between replicas is sufficient for the database sizes within the maintenance window.
• When adding new databases to an existing AG and you want them seeded without manual intervention.
• When building AGs in automated deployment pipelines.

Required additional step on the secondary (as noted in the permissions section):

ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;

If SEEDING_MODE = AUTOMATIC was specified at AG creation, seeding begins automatically after the secondary joins. If the AG was created with SEEDING_MODE = MANUAL and you later want to use automatic seeding for a specific replica, change the mode:

ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON N'SQL-NODE2'
WITH (SEEDING_MODE = AUTOMATIC);

Key constraints on automatic seeding:

• The secondary must be able to create databases. The GRANT CREATE ANY DATABASE permission on the AG enables this.
• If the primary and secondary have different default data file paths, the secondary will use its own default paths. Verify these are appropriate before enabling automatic seeding.
• Automatic seeding uses the HADR endpoint (TCP 5022), not a separate channel. During seeding, the endpoint carries both the seeding stream and replication traffic. On large databases, this can saturate the endpoint.
• If automatic seeding fails, the database does not appear on the secondary. The failure is recorded in sys.dm_hadr_automatic_seeding and in the SQL Server error log, but not always in a way that triggers default alerts.

Monitoring seeding progress

Monitor automatic seeding

The primary DMV for automatic seeding is sys.dm_hadr_automatic_seeding. Query this on the primary:

SELECT
    ag.name                                          AS availability_group,
    ar.replica_server_name                           AS secondary_replica,
    adc.database_name,
    s.start_time,
    s.completion_time,
    s.is_source,
    s.current_state,
    s.failure_state,
    s.failure_state_desc,
    s.error_code,
    CAST(
        s.transferred_size_bytes / 1024.0 / 1024.0
        AS decimal(18,2)
    )                                                AS transferred_mb,
    CAST(
        s.database_size_bytes / 1024.0 / 1024.0
        AS decimal(18,2)
    )                                                AS database_size_mb,
    CASE
        WHEN s.database_size_bytes > 0
        THEN CAST(
            100.0 * s.transferred_size_bytes / s.database_size_bytes
            AS decimal(5,2)
        )
        ELSE NULL
    END                                              AS percent_complete
FROM sys.dm_hadr_automatic_seeding AS s
JOIN sys.availability_groups AS ag
    ON ag.group_id = s.ag_id
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = s.ag_remote_replica_id
JOIN sys.availability_databases_cluster AS adc
    ON adc.group_database_id = s.ag_db_id
ORDER BY
    ag.name,
    adc.database_name,
    s.start_time DESC;

The current_state column returns one of:

For real-time throughput, query sys.dm_hadr_physical_seeding_stats on the secondary (or primary when is_source = 1):

-- Run on the secondary replica during active seeding
SELECT
    @@SERVERNAME                                     AS connected_instance,
    pss.local_database_name,
    pss.remote_machine_name,
    pss.role_desc,
    pss.internal_state_desc,
    CAST(
        pss.transferred_size_bytes / 1024.0 / 1024.0
        AS decimal(18,2)
    )                                                AS transferred_mb,
    CAST(
        pss.total_disk_io_wait_time_ms / 1000.0
        AS decimal(10,3)
    )                                                AS disk_io_wait_sec,
    CAST(
        pss.total_network_wait_time_ms / 1000.0
        AS decimal(10,3)
    )                                                AS network_wait_sec,
    pss.is_compression_enabled,
    pss.start_time,
    DATEDIFF(SECOND, pss.start_time, SYSDATETIME()) AS elapsed_sec
FROM sys.dm_hadr_physical_seeding_stats AS pss;

This DMV is only populated while seeding is active. It returns no rows when seeding is idle or complete.

Monitor automatic seeding history

Completed seeding operations are written to sys.dm_hadr_automatic_seeding with a completion_time populated. For historical visibility, also query the SQL Server error log for seeding-related messages:

EXEC sp_readerrorlog 0, 1, 'seeding';

Monitor manual seeding (backup/restore progress)

During a RESTORE DATABASE operation, progress is visible through sys.dm_exec_requests:

SELECT
    r.session_id,
    r.command,
    r.status,
    r.percent_complete,
    CAST(
        r.estimated_completion_time / 1000.0 / 60.0
        AS decimal(10,2)
    )                AS estimated_completion_minutes,
    r.cpu_time,
    r.total_elapsed_time,
    t.text           AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.command IN ('RESTORE DATABASE', 'RESTORE LOG', 'BACKUP DATABASE', 'BACKUP LOG')
ORDER BY r.percent_complete DESC;

After the restore and after joining the database to the AG, the secondary transitions into redo catch-up. Monitor the redo queue during initial synchronization:

SELECT
    ar.replica_server_name,
    adc.database_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size          AS send_queue_kb,
    drs.log_send_rate                AS send_rate_kb_per_sec,
    drs.redo_queue_size              AS redo_queue_kb,
    drs.redo_rate                    AS redo_rate_kb_per_sec,
    CASE
        WHEN drs.redo_rate > 0
        THEN CAST(
            drs.redo_queue_size * 1.0 / drs.redo_rate
            AS decimal(10,1)
        )
        ELSE NULL
    END                              AS estimated_redo_completion_sec,
    drs.last_hardened_time,
    drs.last_redone_time
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = drs.replica_id
JOIN sys.availability_databases_cluster AS adc
    ON adc.group_database_id = drs.group_database_id
WHERE drs.is_local = 0
ORDER BY
    ar.replica_server_name,
    adc.database_name;

A non-zero redo_queue_size that is decreasing means the secondary is catching up. A redo queue that is not decreasing while the primary is active means the secondary redo thread cannot keep pace with the primary’s log generation rate.

Validate the AG after seeding

After seeding completes and all databases show SYNCHRONIZED, run a full health check:

SELECT
    ag.name                                  AS availability_group,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ar.seeding_mode_desc,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc          AS replica_health,
    adc.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc          AS database_health,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.log_send_queue_size                  AS send_queue_kb,
    drs.redo_queue_size                      AS redo_queue_kb,
    drs.last_hardened_time,
    drs.last_redone_time
FROM sys.availability_groups AS ag
JOIN sys.availability_replicas AS ar
    ON ar.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ars.replica_id = ar.replica_id
JOIN sys.availability_databases_cluster AS adc
    ON adc.group_id = ag.group_id
LEFT JOIN sys.dm_hadr_database_replica_states AS drs
    ON drs.replica_id = ar.replica_id
   AND drs.group_database_id = adc.group_database_id
ORDER BY
    ag.name,
    ar.replica_server_name,
    adc.database_name;

For synchronous replicas, confirm:

synchronization_state_desc  = SYNCHRONIZED
synchronization_health_desc = HEALTHY
is_suspended                = 0
send_queue_kb               = 0
redo_queue_size             = 0 (or very low)

For asynchronous replicas, SYNCHRONIZING is normal. send_queue_kb and redo_queue_kb will be non-zero and reflect WAN latency or load, not a failure.

Ongoing AG health monitoring

Use this query as a scheduled monitoring check or SQL Server Agent alert condition:

-- Alert: any synchronous secondary not in SYNCHRONIZED state
SELECT
    ag.name                            AS availability_group,
    ar.replica_server_name,
    adc.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size            AS data_loss_exposure_kb,
    drs.is_suspended,
    drs.suspend_reason_desc
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = drs.replica_id
JOIN sys.availability_databases_cluster AS adc
    ON adc.group_database_id = drs.group_database_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = ar.group_id
WHERE drs.is_local = 0
  AND
  (
      (
          ar.availability_mode_desc = 'SYNCHRONOUS_COMMIT'
          AND drs.synchronization_state_desc <> 'SYNCHRONIZED'
      )
      OR drs.is_suspended = 1
      OR drs.synchronization_health_desc = 'NOT_HEALTHY'
  )
ORDER BY ag.name, ar.replica_server_name, adc.database_name;

Any rows returned indicate an active HA/DR exposure. Add this query to SQL Server Agent as a multi-response alert or feed it into an external monitoring system.

On-premises versus Azure: key differences summary

Do not do this

• Do not create the AG before verifying the HADR endpoint is STARTED on all replicas. An endpoint in STOPPED state produces connection errors that are easy to misread as permissions issues.
• Do not use automatic seeding without granting CREATE ANY DATABASE to the AG on the secondary. The seeding will start, appear to progress briefly, and then fail. The error is not always surfaced prominently.
• Do not restore the secondary database with RECOVERY before joining it to the AG. A database restored with RECOVERY is online and cannot be joined to an AG as a secondary. If this happens, drop the database and redo the restore with NORECOVERY.
• Do not run automatic seeding for databases that exceed the available bandwidth in the seeding window. A 500 GB database over a 100 Mbit link requires approximately eleven hours at line rate with no overhead. Plan accordingly or use manual seeding with pre-staged backup files.
• Do not set SEEDING_MODE = AUTOMATIC and then set SEEDING_MODE = MANUAL after AG creation without understanding that this only affects future databases added to the AG, not databases already in a failed seeding state.
• Do not skip the GRANT CREATE ANY DATABASE step when adding new databases to an existing AG that uses automatic seeding. The grant is per-AG, not per-database — but if it was never issued, every new database will fail to seed automatically.
• Do not assign a DHCP-leased address to the AG listener IP on-premises. If the lease expires or is reassigned, the listener becomes unreachable. Reserve the IP statically in the subnet and configure it as a static cluster IP resource.
• Do not create the listener on Azure without configuring the probe port on the cluster IP resource. The ADD LISTENER statement will succeed and the listener will appear in sys.availability_group_listeners, but the ILB health probe will receive no response, the backend nodes will be marked unhealthy, and no traffic will reach the SQL Server instances through the listener.
• Do not create the Azure ILB load balancing rule without EnableFloatingIP (Direct Server Return). Without it, the reply packet is sourced from the ILB frontend IP, which the SQL Server NIC does not own, and the ILB drops the return traffic. The symptom is a connection that appears to establish and then immediately resets.

Official references

• Always On Availability Groups overview — Microsoft Learn
• Prerequisites, restrictions, and recommendations for Always On — Microsoft Learn
• CREATE AVAILABILITY GROUP — Microsoft Learn
• Automatic seeding for secondary replicas — Microsoft Learn
• sys.dm_hadr_automatic_seeding — Microsoft Learn
• sys.dm_hadr_physical_seeding_stats — Microsoft Learn
• sys.dm_hadr_database_replica_states — Microsoft Learn
• Configure an availability group listener — Microsoft Learn
• Pre-stage cluster computer objects in Active Directory — Microsoft Learn
• Configure an ILB listener for Always On availability groups in Azure — Microsoft Learn
• Tutorial: Manually configure an AG on Azure VMs (single subnet) — Microsoft Learn
• Deploy SQL Server on Azure Virtual Machines — Microsoft Learn
• Deploy a cloud witness for a Failover Cluster — Microsoft Learn

Conclusion

The availability group was not built incorrectly. The HADR endpoint was open, the cluster was healthy, and the CREATE AVAILABILITY GROUP statement succeeded on the first attempt. The seeding failed because the permission that automatic seeding requires was not on the checklist anyone used, and sys.dm_hadr_automatic_seeding was not being watched.

Prerequisites for an AG span four distinct layers: Windows, network, SQL Server, and account permissions. Any one of those layers being incomplete produces a different failure at a different point in the build. The monitoring queries for seeding are not optional — they are how you confirm that the databases you think are protected are actually protected.

Build the cluster. Enable Always On. Create the endpoints. Create the AG with the right seeding mode. Grant CREATE ANY DATABASE if using automatic seeding. Then watch sys.dm_hadr_automatic_seeding or your restore progress until every database reads SYNCHRONIZED. Only then is the AG built.

Continue reading

• Building a Windows Server Failover Cluster for SQL Server: On-Premises and Azure
• SQL Server Always On: Why Synchronous Commit Does Not Mean RPO = Zero
• Extending a Two-Node SQL Server Availability Group Across Azure Regions
• SQL Server tempdb on Azure Ephemeral Disks: It Does Not Need Persistence. Its Directory Does.

Marios Pavlidis Principal Database Administrator