Extending a Two-Node SQL Server Availability Group Across Azure Regions

The cluster validation completed successfully. All four nodes reported Up. The databases were synchronized and the replicas were connected. Region A had been experiencing elevated latency for two hours and the team had decided to fail over.

The DBA connected directly to SQL-B1 and issued the manual failover command. The Availability Group listener went silent. SQL-B1’s role appeared as RESOLVING in the DMV. Sixty-three seconds later, the replica returned to SECONDARY. Error 41131. Region A was still the primary.

The post-mortem found two problems. SQL-B1 and SQL-B2 had never been added as possible owners of the AG cluster resource in WSFC — adding them to the cluster did not do that automatically. And a long-running index rebuild had been active on the primary when the command was issued, which would have placed the former primary in a slow REVERTING state even if the failover had succeeded.

The answer

Extending an Always On Availability Group to a second Azure region requires more than adding replicas to the cluster and the AG. Four operational issues frequently surface only during an actual failover attempt: the quorum witness may carry regional bias that strands the surviving cluster when the original region goes down, new WSFC nodes are not automatically added as possible owners of the AG cluster resource, a large active transaction can force the former primary into a slow REVERTING state after failover, and a planned lossless failover requires the target replica to reach SYNCHRONIZED before the command is issued. This article covers the full extension procedure and each of these four issues in detail.

What you will learn

• How to prepare WSFC and AG connectivity for a multiregion Azure extension
• Why the cloud witness is required and how to replace a file-share witness without disrupting quorum
• Why new replica nodes are not automatically possible owners of the AG cluster resource and how to verify and correct the configuration
• How to check for large active transactions before a planned regional failover and what REVERTING means when it appears on the former primary

Scope: SQL Server 2016–2022 Always On Availability Groups on Azure VMs. WSFC PowerShell cmdlet behaviour reflects Windows Server 2016 and later. Cloud witness is supported from Windows Server 2016. Distributed Availability Groups are a separate architecture and are not covered here. Behaviour on third-party cloud providers and on-premises stretched clusters follows the same general model but infrastructure configuration differs.

Target architecture

A common starting point is two Availability Group replicas in one Azure region:

Azure Region A
├── SQL-A1 — Primary
└── SQL-A2 — Synchronous secondary

Regional disaster recovery can be added by extending the existing Windows Server Failover Cluster and Availability Group with two SQL Server nodes in another Azure region:

Azure Region A
├── SQL-A1
└── SQL-A2

Azure Region B
├── SQL-B1
└── SQL-B2

SQL Server supports one primary replica and up to eight secondary replicas, subject to SQL Server edition and version restrictions. A practical four-replica configuration is:

Remote replicas are normally asynchronous because keeping them synchronous exposes production commits to WAN latency.

During a planned regional failover, the selected remote replica can temporarily be changed to synchronous commit. After it reaches SYNCHRONIZED, a lossless manual failover can be performed. Microsoft uses this approach for controlled failovers to remote replicas.

A limitation of this single-AG architecture is that commit and failover modes belong to individual replicas; they do not automatically change based on which region is active. After a regional failover, the replica configuration should therefore be reviewed so that the new primary has an appropriate local synchronous secondary.

For stricter regional isolation and independent WSFC clusters, a Distributed Availability Group may be a better design. However, that is a different architecture from extending the existing WSFC cluster.

1. Prepare connectivity between regions

The Azure virtual networks must be connected using VNet peering or a site-to-site VPN. Microsoft documents both as supported methods for connecting Availability Group replicas across Azure regions.

At minimum, validate:

• Active Directory and DNS resolution in both directions.
• SQL Server Database Engine connectivity.
• HADR endpoint connectivity, normally TCP 5022.
• WSFC cluster communication, including UDP/TCP 3343.
• RPC endpoint mapper TCP 135 and the configured dynamic RPC range.
• SMB TCP 445 where required.
• HTTPS TCP 443 from every cluster node to Azure Storage for the cloud witness.
• Listener connectivity between application networks and both Azure regions.

Example endpoint test:

Test-NetConnection SQL-A1.contoso.com -Port 5022
Test-NetConnection SQL-A2.contoso.com -Port 5022
Test-NetConnection SQL-B1.contoso.com -Port 5022
Test-NetConnection SQL-B2.contoso.com -Port 5022

The SQL Server version, cumulative update level, service accounts, collation, file paths and server configuration should also be aligned across replicas.

2. Validate and add the new WSFC nodes

Install the Failover Clustering feature on the two new servers and run cluster validation before adding them.

Microsoft recommends validating the existing and proposed nodes together before extending a cluster.

$Nodes = @(
    'SQL-A1',
    'SQL-A2',
    'SQL-B1',
    'SQL-B2'
)

Test-Cluster -Node $Nodes

Review all validation warnings. Network warnings should not be dismissed automatically in a stretched cluster because incorrect network classification, routing or firewall rules can later cause quorum or AG lease failures.

Add the nodes:

Add-ClusterNode -Name 'SQL-B1'
Add-ClusterNode -Name 'SQL-B2'

Verify cluster membership:

Get-ClusterNode |
    Format-Table Name, State, NodeWeight, DynamicWeight

All four nodes should report Up.

3. Replace the file-share witness with a cloud witness

A file-share witness hosted in one region can introduce regional bias. For a stretched multi-region Azure cluster, a cloud witness provides a neutral quorum vote backed by Azure Storage.

Microsoft describes cloud witness as suitable for multisite, multizone and multiregion deployments, and recommends it for Azure VM clusters where shared-disk quorum is not required. The storage account should use General Purpose, Standard and locally redundant storage, and every node must reach Azure Storage over HTTPS TCP 443.

Check the existing configuration:

Get-ClusterQuorum

Get-ClusterNode |
    Format-Table Name, State, NodeWeight, DynamicWeight

Test Azure Storage connectivity from every cluster node:

Test-NetConnection `
    <storage-account>.blob.core.windows.net `
    -Port 443

Change the quorum witness:

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

Set-ClusterQuorum @Parameters

Verify the result:

Get-ClusterQuorum

Expected output:

QuorumType     : Majority
QuorumResource : Cloud Witness

Set-ClusterQuorum replaces the current witness configuration. The existing file-share witness should not be manually deleted before changing quorum; WSFC manages the core witness resource as part of the quorum transition. Microsoft documents Set-ClusterQuorum -CloudWitness as the supported PowerShell method.

Review node votes separately

Changing the witness does not automatically redesign node voting.

Get-ClusterNode |
    Format-Table Name, State, NodeWeight, DynamicWeight

Microsoft guidance for Azure SQL Server clusters recommends giving votes to nodes that can host the primary through automatic failover and generally excluding DR-only nodes from voting. The correct configuration depends on the required site-survival behavior and must be validated using failure scenarios, not simply by counting nodes.

Do not modify NodeWeight without first modelling:

• Loss of Region A.
• Loss of Region B.
• Loss of inter-region connectivity.
• Loss of the cloud witness.
• Simultaneous node and witness failures.

4. Enable Always On and configure HADR endpoints

Enable Always On Availability Groups on each new SQL Server instance using SQL Server Configuration Manager, then restart the SQL Server service.

Verify:

SELECT
    @@SERVERNAME AS instance_name,
    SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled,
    SERVERPROPERTY('IsClustered') AS is_clustered;

Expected:

is_hadr_enabled = 1
is_clustered    = 1

Create or verify the HADR endpoint:

SELECT
    dme.name,
    dme.state_desc,
    dme.role_desc,
    dme.connection_auth_desc,
    te.port
FROM sys.database_mirroring_endpoints AS dme
JOIN sys.tcp_endpoints AS te
    ON te.endpoint_id = dme.endpoint_id;

The endpoint must be STARTED.

When SQL Server services run under domain or group-managed service accounts, verify that the SQL Server service identities have CONNECT permission on the HADR endpoints of the other replicas.

5. Add the remote replicas

Run the ADD REPLICA statements on the current primary.

Microsoft requires ENDPOINT_URL, AVAILABILITY_MODE and FAILOVER_MODE when adding a replica with Transact-SQL. After the replica is defined on the primary, it must be joined locally on the secondary instance.

ALTER AVAILABILITY GROUP [MyAG]
ADD REPLICA ON N'SQL-B1'
WITH
(
    ENDPOINT_URL = N'TCP://SQL-B1.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = MANUAL,
    SECONDARY_ROLE
    (
        ALLOW_CONNECTIONS = READ_ONLY
    )
);
GO

ALTER AVAILABILITY GROUP [MyAG]
ADD REPLICA ON N'SQL-B2'
WITH
(
    ENDPOINT_URL = N'TCP://SQL-B2.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = MANUAL,
    SECONDARY_ROLE
    (
        ALLOW_CONNECTIONS = READ_ONLY
    )
);
GO

Run locally on each new secondary:

ALTER AVAILABILITY GROUP [MyAG] JOIN;
GO

6. Seed the databases

For manual seeding:

1. Take a recent full backup on the primary.
2. Take all required transaction-log backups.
3. Restore the full and log backups on each new secondary with NORECOVERY.
4. Join each restored database to the Availability Group.

Microsoft documents RESTORE WITH NORECOVERY followed by joining the restored database as the standard manual preparation process.

Example:

RESTORE DATABASE [ApplicationDB]
FROM DISK = N'X:\Backup\ApplicationDB_FULL.bak'
WITH
    NORECOVERY,
    STATS = 5;
GO

RESTORE LOG [ApplicationDB]
FROM DISK = N'X:\Backup\ApplicationDB_LOG.trn'
WITH
    NORECOVERY,
    STATS = 5;
GO

Join the database on each secondary:

ALTER DATABASE [ApplicationDB]
SET HADR AVAILABILITY GROUP = [MyAG];
GO

For large databases or constrained inter-region bandwidth, manual backup, copy and restore normally provides greater operational control than automatic seeding.

7. Verify the AG resource possible owners

This is a critical post-extension check.

Adding a server to the Windows Server Failover Cluster does not guarantee that the node is automatically included as a possible owner of an existing Availability Group cluster resource. After adding the new replicas, verify the WSFC possible-owner configuration explicitly.

If the target node is not a possible owner, SQL Server may accept the manual failover request, but WSFC will be unable to bring the Availability Group resource online on that node. The failover then times out, and the target replica remains or returns to the SECONDARY role.

From the standard Availability Group DMV, the DBA will normally observe the replica moving through or remaining in one of the supported role_desc values:

PRIMARY
SECONDARY
RESOLVING

More detailed transition phases such as:

Secondary_Normal
Resolving_Pending_Failover
Resolving_Normal
Primary_Pending

are internal Availability Group state-transition values exposed through the availability_replica_state_change Extended Event and may also appear in the SQL Server error log. They should not be presented as values returned by sys.dm_hadr_availability_replica_states.role_desc.

A failed transition may therefore appear operationally as:

Manual failover initiated
    ↓
Target DMV role becomes RESOLVING
    ↓
WSFC cannot assign or online the AG resource
    ↓
Failover times out with error 41131
    ↓
Target DMV role returns to SECONDARY

Identify the Availability Group cluster resource:

Get-ClusterResource |
    Where-Object ResourceType -eq 'SQL Server Availability Group' |
    Format-Table Name, State, OwnerNode, OwnerGroup

Check the possible owners:

Get-ClusterResource -Name 'MyAG' |
    Get-ClusterOwnerNode

The list should include every WSFC node that hosts a replica of that Availability Group:

SQL-A1
SQL-A2
SQL-B1
SQL-B2

If the list is incomplete, explicitly set the complete intended list:

$Owners = @(
    'SQL-A1',
    'SQL-A2',
    'SQL-B1',
    'SQL-B2'
)

Get-ClusterResource -Name 'MyAG' |
    Set-ClusterOwnerNode -Owners $Owners

Set-ClusterOwnerNode replaces the existing possible-owner list. It does not append a single node, so always provide the complete intended list.

Availability Group failover itself should still be initiated through SQL Server Management Studio or Transact-SQL, not by manually moving the cluster resource through Failover Cluster Manager.

8. Configure regional listener connectivity

A multiregion AG listener requires connectivity in each region. Depending on the topology, this can involve:

• Multiple listener IP resources.
• One Azure Load Balancer per region.
• A multi-subnet listener.
• A Distributed Network Name listener.

Microsoft states that when an AG spans Azure regions and uses load-balancer-based listener connectivity, each region requires its own load balancer.

Applications should use a supported client driver and:

MultiSubnetFailover=True

Example:

Server=tcp:MyAGListener,1433;
Database=ApplicationDB;
Integrated Security=SSPI;
MultiSubnetFailover=True;

This allows supported drivers to attempt listener IP addresses in parallel and reduces reconnection time during subnet failover.

9. Validate replica health

Run on the primary:

SELECT
    ag.name AS availability_group,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    adc.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc AS database_health,
    drs.is_suspended,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    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
WHERE ag.name = N'MyAG'
ORDER BY
    ar.replica_server_name,
    adc.database_name;

For asynchronous replicas, SYNCHRONIZING is normal.

For a planned lossless failover, the selected target must temporarily use synchronous commit and every database must report:

SYNCHRONIZED
HEALTHY
is_suspended = 0

10. Check for large active transactions before failover

Synchronous commit guarantees that committed log records are hardened on the secondary. It does not guarantee that every transaction is complete or that the secondary has redone every log record into its data files.

A large active transaction interrupted by failover can leave the former primary ahead of the common recovery point. SQL Server must then undo already-applied changes before that database can resume synchronization.

Before a planned regional failover, run:

SELECT
    DB_NAME(dt.database_id) AS database_name,
    st.session_id,
    at.transaction_begin_time,
    DATEDIFF
    (
        MINUTE,
        at.transaction_begin_time,
        SYSDATETIME()
    ) AS transaction_age_minutes,
    dt.database_transaction_log_record_count,
    CAST
    (
        dt.database_transaction_log_bytes_used
        / 1024.0 / 1024.0
        AS decimal(18,2)
    ) AS log_used_mb,
    es.login_name,
    es.host_name,
    es.program_name,
    es.status,
    ib.event_info AS last_submitted_command
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_session_transactions AS st
    ON st.transaction_id = at.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
    ON dt.transaction_id = at.transaction_id
JOIN sys.dm_exec_sessions AS es
    ON es.session_id = st.session_id
OUTER APPLY sys.dm_exec_input_buffer
(
    st.session_id,
    NULL
) AS ib
WHERE dt.database_id = DB_ID(N'ApplicationDB')
  AND es.is_user_process = 1
ORDER BY
    dt.database_transaction_log_bytes_used DESC,
    at.transaction_begin_time;

A simpler check is:

DBCC OPENTRAN (N'ApplicationDB');

Do not initiate a planned regional failover while a large deployment, index rebuild, batch update, bulk load or cleanup transaction is running.

11. Perform a controlled regional failover

For a planned failover with no intended data loss:

1. Stop or quiesce application writes.
2. Confirm that no large transaction is running.
3. Change the selected remote replica to synchronous commit.
4. Wait for all databases to become SYNCHRONIZED.
5. Verify that the target is a possible owner.
6. Connect directly to the target SQL Server instance.
7. Execute the failover.
8. Validate the listener, application connectivity and database health.
9. Reconfigure replica commit and failover modes for the new active region.

Change the selected target:

ALTER AVAILABILITY GROUP [MyAG]
MODIFY REPLICA ON N'SQL-B1'
WITH
(
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
);
GO

After every database is synchronized, connect directly to SQL-B1:

ALTER AVAILABILITY GROUP [MyAG] FAILOVER;
GO

Do not use Failover Cluster Manager to move the AG resource. WSFC is not aware of database synchronization state and can initiate an unsafe or prolonged transition.

12. Why the former primary may start reverting

A successful planned failover can still leave a database on the former primary in:

NOT SYNCHRONIZING
REVERTING

This can occur even when both replicas used synchronous commit.

If a large transaction was active during failover, the former primary may have already applied changes that are beyond the common recovery point selected after the role transition. The former primary, now a secondary, must receive pages from the new primary and reverse those changes before normal synchronization can resume.

Microsoft calls this process Undo of Redo. It is inherently slow and is most visible when failover interrupts a large transaction.

The new primary may report:

NOT SYNCHRONIZING

while the former primary reports:

REVERTING

This does not automatically mean that the database must be removed, restored or reseeded.

Do not:

• Restart SQL Server.
• Remove the database from the AG.
• Attempt immediate failback.
• Run SET HADR RESUME unless is_suspended = 1.
• Reinitialize the database while rollback is progressing.

13. Check the reverting state

Run locally on the former primary, which is now the secondary:

SELECT
    @@SERVERNAME AS connected_instance,
    d.name AS database_name,
    d.state_desc AS database_state,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.database_state_desc,
    drs.is_suspended,
    drs.suspend_reason_desc,
    drs.last_received_time,
    drs.last_hardened_time,
    drs.last_redone_time,
    drs.redo_queue_size,
    drs.redo_rate
FROM sys.databases AS d
LEFT JOIN sys.dm_hadr_database_replica_states AS drs
    ON drs.database_id = d.database_id
   AND drs.is_local = 1
WHERE d.name = N'ApplicationDB';

During the Undo of Redo phase, the local state normally reports:

REVERTING

14. Monitor rollback progress

SQL Server exposes performance counters for the amount of log requiring undo and the amount remaining.

WITH counters AS
(
    SELECT
        counter_name,
        cntr_value
    FROM sys.dm_os_performance_counters
    WHERE object_name LIKE N'%Database Replica%'
      AND instance_name = N'ApplicationDB'
      AND counter_name IN
      (
          N'Total Log requiring undo',
          N'Log remaining for undo',
          N'Redo Bytes Remaining'
      )
)
SELECT
    MAX
    (
        CASE
            WHEN counter_name = N'Total Log requiring undo'
            THEN cntr_value
        END
    ) AS total_undo_kb,
    MAX
    (
        CASE
            WHEN counter_name = N'Log remaining for undo'
            THEN cntr_value
        END
    ) AS remaining_undo_kb,
    MAX
    (
        CASE
            WHEN counter_name = N'Redo Bytes Remaining'
            THEN cntr_value
        END
    ) AS redo_remaining_bytes,
    CAST
    (
        100.0 *
        (
            MAX
            (
                CASE
                    WHEN counter_name =
                         N'Total Log requiring undo'
                    THEN cntr_value
                END
            )
            -
            MAX
            (
                CASE
                    WHEN counter_name =
                         N'Log remaining for undo'
                    THEN cntr_value
                END
            )
        )
        /
        NULLIF
        (
            MAX
            (
                CASE
                    WHEN counter_name =
                         N'Total Log requiring undo'
                    THEN cntr_value
                END
            ),
            0
        )
        AS decimal(6,2)
    ) AS percent_complete
FROM counters;

Progress is occurring when Log remaining for undo continues decreasing.

The AlwaysOn_health Extended Events session can also report reverting progress through hadr_trace_message. Microsoft recommends using these counters and Extended Events because the SQL Server error log may contain little progress information during a long reverting operation.

Do not do this

• Do not use Failover Cluster Manager to move the AG resource. WSFC is not aware of AG synchronization state and can trigger an unsafe or prolonged transition. Initiate failover through SQL Server Management Studio or Transact-SQL.
• Do not declare the extension complete without performing a test planned failover. An AG that is synchronized but whose new nodes are missing from the possible-owner list will fail to bring the resource online — which only becomes visible when failover is attempted.
• Do not initiate a planned regional failover while a large batch, index rebuild, or bulk load is running on the primary. A transaction active at the time of failover can leave the former primary in REVERTING for a prolonged period.
• Do not restart SQL Server, remove the database from the AG, or attempt immediate failback when the former primary is REVERTING. The Undo of Redo operation completes on its own; interrupting it lengthens recovery or forces a reseed.

Final checklist

Before declaring the regional extension complete, verify:

[ ] Both new servers pass cluster validation
[ ] Both new servers are members of the existing WSFC
[ ] Cloud witness is online
[ ] Node votes have been deliberately reviewed
[ ] HADR endpoint TCP 5022 works in both directions
[ ] New replicas are connected
[ ] All databases have been seeded and joined
[ ] New replica nodes are possible owners of the AG resource
[ ] Listener connectivity works from both regions
[ ] MultiSubnetFailover=True is used by supported clients
[ ] A planned failover has been tested
[ ] Large-transaction checks are included in the failover runbook
[ ] Reverting-state monitoring is documented

Official references

• Always On Availability Groups overview — Microsoft Learn
• Add a secondary replica to an availability group — Microsoft Learn
• Deploy a cloud witness for a Failover Cluster — Microsoft Learn
• Set-ClusterQuorum — Microsoft Learn
• Get-ClusterOwnerNode — Microsoft Learn
• Set-ClusterOwnerNode — Microsoft Learn
• Test-Cluster — Microsoft Learn
• sys.dm_hadr_database_replica_states — Microsoft Learn
• sys.dm_hadr_availability_replica_states — Microsoft Learn
• ALTER AVAILABILITY GROUP — Microsoft Learn
• Perform a planned manual failover of an availability group — Microsoft Learn

Conclusion

Two nodes. Connected to the cluster. Databases synchronized. Replicas showing Connected in the DMV. None of that was enough, because WSFC had never been told those nodes could own the AG resource. The cluster knew about them; the AG resource did not.

The gap between “extended” and “able to fail over cleanly” is where these four issues live. Check the quorum witness placement before a regional failure makes it matter. Verify the possible-owner list before a failover makes it visible. Check for large transactions before issuing the command. Understand that REVERTING is not a crisis requiring intervention — it is a recovery process requiring patience and monitoring. Work through the checklist. Test the failover. The post-mortem should be about something else.

Continue reading

• SQL Server Always On: Why Synchronous Commit Does Not Mean RPO = Zero
• SQL Server tempdb on Azure Ephemeral Disks: It Does Not Need Persistence. Its Directory Does.
• SQL Server: Regaining Sysadmin Access When All Administrator Accounts Are Locked Out

Marios Pavlidis Principal Database Administrator