Zero-Downtime SQL Server Migration with Distributed Availability Groups: A Step-by-Step Runbook

This is the hands-on companion to Distributed Availability Groups: Your DBA Tool for Migrations and Multi-Site Deployments. That article covers what a DAG is and why it exists. This one covers how to actually run a migration with one, including the step people skip: proving the link is caught up before you cut over.

Prerequisites

Step 1: Stand Up the Target-Site AG

The target AG is a normal AlwaysOn AG. It does not contain the migrating database yet. Create it with its replicas, its listener, and nothing else:

CREATE AVAILABILITY GROUP AG_DatacenterB
FOR
REPLICA ON
'SQLB01' WITH (
    ENDPOINT_URL = 'TCP://sqlb01.contoso.com:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC
),
'SQLB02' WITH (
    ENDPOINT_URL = 'TCP://sqlb02.contoso.com:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC,
    SEEDING_MODE = AUTOMATIC
);
GO
ALTER AVAILABILITY GROUP AG_DatacenterB
ADD LISTENER 'ag-dcb-listener' (
    WITH IP ((N'10.20.0.10', N'255.255.255.0')),
    PORT = 1433
);

For automated, repeatable AG setup instead of doing this by hand, see the AlwaysOnSetup tool and the traditional vs. automated setup comparison.

Step 2: Create the DAG

The DAG itself is created on the primary AG's side first, then joined from the secondary AG's side. Both statements reference the same DAG name and both AG names.

On Site A (existing primary AG):

CREATE AVAILABILITY GROUP DAG_Production
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'AG_DatacenterA' WITH (
    LISTENER_URL = 'TCP://ag-dca-listener.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
),
'AG_DatacenterB' WITH (
    LISTENER_URL = 'TCP://ag-dcb-listener.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
);

On Site B (new secondary AG), join the same DAG:

ALTER AVAILABILITY GROUP DAG_Production
JOIN
AVAILABILITY GROUP ON
'AG_DatacenterA' WITH (
    LISTENER_URL = 'TCP://ag-dca-listener.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
),
'AG_DatacenterB' WITH (
    LISTENER_URL = 'TCP://ag-dcb-listener.contoso.com:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
);
GO
ALTER AVAILABILITY GROUP AG_DatacenterB JOIN AVAILABILITY GROUP ON DISTRIBUTED;

With automatic seeding, SQL Server now starts copying the database from Site A to Site B on its own. Depending on database size and link bandwidth, this can take anywhere from minutes to days. Don't schedule a cutover date yet; schedule a checkpoint to review progress.

New-sqmDistributedAvailabilityGroup `
    -PrimaryInstance   "SQL01" -PrimaryAgName   "AG_DatacenterA" -PrimaryFqdn   "SQL01.contoso.com" `
    -SecondaryInstance "SQL02" -SecondaryAgName "AG_DatacenterB" -SecondaryFqdn "SQL02.contoso.com" `
    -ServiceAccount "CONTOSO\SqlServiceAccount" -SeedingMode Automatic

Add-sqmDatabaseToDistributedAg `
    -SqlInstance "SQL01" -AvailabilityGroupName "DAG_Production" `
    -DatabaseName "MyDb" -SecondaryInstance "SQL02"

Step 3: Verify the Link Is Actually Caught Up

Query the replica states on both sides. You want synchronization_state_desc = SYNCHRONIZING (async replicas never show SYNCHRONIZED) with a small and stable log_send_queue_size and redo_queue_size:

SELECT
    ag.name                          AS ag_name,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.log_send_queue_size,
    drs.redo_queue_size,
    drs.secondary_lag_seconds,
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag   ON ar.group_id = ag.group_id
WHERE ag.name IN ('AG_DatacenterA', 'AG_DatacenterB')
ORDER BY ag.name, ar.replica_server_name;

Run this query several times over a few minutes, not once. A queue that's shrinking is catching up. A queue that's flat or growing under normal load means the link can't keep pace, and cutting over now risks the data sitting in that queue.

• secondary_lag_seconds climbing steadily: network or throughput problem on the DAG link itself
• redo_queue_size stuck high while log_send_queue_size is low: Site B can receive logs faster than it can apply them, check disk latency and CPU on the secondary AG's primary replica
• synchronization_health_desc not HEALTHY on sys.dm_hadr_availability_replica_states: stop, don't cut over until this clears

Test-sqmDistributedAgReadiness -SqlInstance "SQL01" -TargetInstance "SQL02"

Step 4: Planned Cutover

1. Freeze writes at the application layer, or schedule the cutover for a genuinely quiet window (this is not the async failover; you're choosing to do it clean)
2. Re-run the sync-state query from Step 3, or the readiness check below. Confirm log_send_queue_size and redo_queue_size are at or near zero
3. Fail over the primary AG internally if needed, so the forwarder replica in Site A is caught up with a synchronous replica
4. Sync logins, SQL Agent jobs, linked servers, operators, and alerts to Site B before you flip roles, not after. Applications will hit login failures on the new primary otherwise:

   Sync-sqmAgNode -SqlInstance "SQL01" -AvailabilityGroup "AG_DatacenterA"

   Sync-sqmAgNode auto-detects the current primary and copies these objects to all secondaries, including SID/password transfer for SQL logins.
5. If the listener also needs to move with the primary role, do it before the failover, not after:

   Move-sqmAlwaysOnListener -SqlInstance "SQL01" -SourceAgName "AG_DatacenterA" -TargetAgName "AG_DatacenterB" -TargetInstance "SQL02"

   Move-sqmAlwaysOnListener recreates the listener on the target AG with the same IP and port; DNS still needs a manual update, which the command's output documents
6. Promote the secondary AG to primary:

   ALTER AVAILABILITY GROUP DAG_Production FAILOVER;

   Run this against a replica in the AG you're promoting (AG_DatacenterB), or use Invoke-sqmDistributedFailover, which re-checks readiness first and refuses to fail over if any replica isn't SYNCHRONIZED:

   Invoke-sqmDistributedFailover -SqlInstance "SQL01" -AvailabilityGroupName "DAG_Production" -Force

7. Verify AG_DatacenterB is now primary and AG_DatacenterA has flipped to secondary in the DAG
8. Update connection strings, DNS aliases, or listener redirection to point at AG_DatacenterB's listener
9. Unfreeze writes and monitor application reconnects

Done correctly, these steps take minutes, not a maintenance window. The actual downtime is however long step 1 lasted, which for most applications with connection retry logic is close to zero.

Rollback Plan

If something looks wrong immediately after cutover, before Site A has drifted, fail back the same way: confirm the (now reversed) sync state, then run ALTER AVAILABILITY GROUP DAG_Production FAILOVER; against Site A. This only works cleanly if you haven't already let production traffic write meaningfully to Site B, since the DAG link is asynchronous in the failback direction too.

Invoke-sqmDistributedFailover -SqlInstance "SQL02" -AvailabilityGroupName "DAG_Production" -Rollback -Force

Invoke-sqmDistributedFailover's -Rollback switch skips the readiness check that would otherwise block it, since you're explicitly failing back to recover from a bad cutover, not doing a routine planned one.

Common Pitfalls

Treating the DAG link like a synchronous replica

It never is. Even with zero measured lag, a cutover in the middle of a burst of writes can leave the last few transactions behind. That's what Step 3's repeated checks are for.

Cutting over without testing failover first

Run ALTER AVAILABILITY GROUP ... FAILOVER in a non-production DAG at least once before the real migration. The syntax is unforgiving about which replica you run it from.

Forgetting the within-AG sync mode

The DAG link (between AGs) is async. The replicas inside each AG can and should still be synchronous where latency allows, so each site keeps its own zero-data-loss local failover independent of the migration.

Logins and jobs left behind

A database failover doesn't bring its logins, SQL Agent jobs, or linked servers with it. If Site B was never used to run production traffic before, applications and jobs fail on the new primary with login errors, not because the DAG did anything wrong. Run Sync-sqmAgNode before cutover, and again afterward once Site A becomes secondary, to keep both sides consistent.

A database goes NotSynchronizing after a seeding hiccup

A dropped connection or restart during seeding can leave a database stuck out of sync without the AG reporting an outright failure. Rather than diagnosing manually, Repair-sqmAlwaysOnDatabases checks every AG database's sync health and, for anything unhealthy, removes it and re-seeds it automatically (it also calls Invoke-sqmSqlAlwaysOnAutoseeding first to make sure autoseed is actually on).

No monitoring after cutover

Migrating is not a one-time event with a clean end state. Keep monitoring the (now reversed) DAG link and forwarder queue depth in case you need to fail back.

Summary

A DAG migration is only zero-downtime if the async link is actually caught up when you promote the target AG. The mechanics (create AG, create DAG, join DAG, failover) are a handful of statements. The discipline is in Step 3: checking, not assuming, that the queue is empty before you flip roles.

Build the target AG early, let seeding run, verify sync state repeatedly, and treat the cutover itself as the shortest and least interesting part of the migration.