SQL Server Distributed Availability Group Migration Guide
A step-by-step DAG migration playbook for SQL Server — prerequisites, T-SQL setup, seeding, failover to Tessell, and post-migration cleanup.
- A Distributed Availability Group (DAG) is a SQL Server 2016+ enterprise feature linking two independent availability groups across separate clusters.
- The Global Primary sends a single log stream to the Forwarder, which redistributes it to all secondary replicas.
- DAGs eliminate shared WSFC requirements, enabling replication across different clouds, data centers, and Active Directory domains.
- DAGs support cross cloud migration, disaster recovery, SQL Server version upgrades, and OS migrations with near zero data loss.
- Choose DAG over traditional AGs for databases exceeding 500GB or any migration where source and target cannot share a WSFC.
A Distributed Availability Group (DAG) is a special type of SQL Server availability group that spans two separate, independent availability groups. Unlike traditional Always On Availability Groups that require all replicas within a single Windows Server Failover Cluster (WSFC), a distributed availability group connects AGs across different clusters, data centers, clouds, or Active Directory domains — without requiring a shared WSFC between them.
DAGs are the recommended approach for migrating large-scale SQL Server databases across cloud environments with minimal downtime. They enable continuous data replication between a source environment (on-premises, Azure VMs, or AWS EC2) and a target environment, allowing a controlled cutover with near-zero data loss. This guide covers how to migrate SQL Server databases to Tessell’s managed SQL Server HA service using a distributed availability group, including prerequisites, step-by-step implementation, post-migration tasks, and troubleshooting.
What Is a Distributed Availability Group?
A distributed availability group is an enterprise feature introduced in SQL Server 2016 that creates a replication relationship between two independent availability groups. Each availability group operates on its own Windows Server Failover Cluster, with its own set of replicas, listeners, and failover policies. The DAG layer connects them by establishing a transaction log stream from the primary replica of the first AG (the Global Primary) to the primary replica of the second AG (the Forwarder).
The Forwarder is a key concept in DAG architecture. It is the primary replica of the secondary availability group. It receives the log stream from the Global Primary and then redistributes it to its own secondary replicas. This two-tier forwarding model means the Global Primary only maintains a single outbound log stream regardless of how many total replicas exist across both AGs. This is architecturally more efficient than a traditional AG, where the primary must maintain separate log streams to every replica.
Because each AG operates on its own WSFC, distributed availability groups eliminate the cluster boundary constraints that limit traditional AGs. There is no requirement for shared Active Directory domains, no need for stretched clusters across data centers, and no dependency on synchronized cluster quorum across sites. This independence makes DAGs ideal for cross-cloud and cross-datacenter scenarios where traditional AG deployment would be impractical or impossible.
Requirements: SQL Server 2016 or later, Enterprise Edition. The target AG can run a higher SQL Server version than the source, which makes DAGs particularly useful for version upgrade migrations.
DAG vs Traditional Availability Group
Understanding how a distributed availability group differs from a traditional Always On Availability Group is essential for selecting the right approach.
| Feature | Traditional AG | Distributed AG |
|---|---|---|
| Cluster | Single WSFC required for all replicas | Independent WSFCs per availability group |
| Scope | Single data center or stretched cluster | Cross-datacenter, cross-cloud, cross-region |
| Failover | Automatic or manual | Manual only |
| Data Movement | Multiple log streams to all replicas | Single stream to Forwarder, then redistributed |
| Primary Use Cases | Local high availability within a site | DR, cross-cloud migration, version/OS upgrades |
| Version Flexibility | Same SQL Server version required across replicas | Target AG can run a higher version |
| Domain Requirement | Same AD domain or trusted domains | No shared domain required; certificate-based auth supported |
A distributed availability group is the right choice when the source and target environments cannot share a WSFC, which is the case for virtually all cross-cloud migration scenarios, including migrations to Tessell.
When to Use Distributed Availability Groups
Distributed availability groups serve several enterprise scenarios beyond basic replication. The following are the most common use cases.
Cross-Cloud Migration: Migrate SQL Server databases from on-premises, Azure VMs, or AWS EC2 to a managed DBaaS platform with minimal downtime. DAG provides continuous replication during the migration window, enabling a controlled cutover with near-zero data loss. For databases exceeding 500GB, DAG is the recommended migration method to Tessell’s managed SQL Server service.
Disaster Recovery Across Data Centers: Establish DR between geographically separated data centers without stretching a single WSFC across sites. Each data center operates its own independent cluster, and the DAG provides the replication link between them.
SQL Server Version Upgrades: Upgrade from SQL Server 2019 to 2022 (or 2016/2017 to 2019) with zero downtime. Build the new version environment, configure DAG replication, allow it to synchronize, then fail over. The old environment becomes the rollback path.
Operating System Migrations: Migrate from an older Windows Server version to a newer one without performing cluster OS rolling upgrades. DAG decouples the OS migration from the database migration by allowing each AG to run on its own OS version.
Scale Read Workloads: Extend read capacity across two availability groups, supporting up to 16 readable secondary replicas. This is useful for reporting, analytics, and geographically distributed read workloads.
Cross-Domain Replication: Replicate databases between environments in different Active Directory domains or forests using certificate-based endpoint authentication, without requiring domain trust relationships.
Prerequisites
Before configuring a distributed availability group for migration to Tessell, verify that the following requirements are met.
SQL Server Version and Edition: Both the source and target must run SQL Server 2016 or later, Enterprise Edition. The target can run a higher version than the source (e.g., source on 2019, target on 2022). Standard Edition does not support distributed availability groups.
Source Availability Group: The source SQL Server instance must have an existing Always On Availability Group configured with the databases to be migrated. All databases in the AG must use the FULL recovery model. Databases in SIMPLE or BULK_LOGGED recovery model cannot participate in availability groups.
Listeners: Both the source AG and the target Tessell AG require configured listeners. On Azure, use an Azure Load Balancer (not DNN — see limitation below). On AWS, use a Network Load Balancer. DNS resolution must be configured so each side can resolve the other’s listener name.
DNN Limitation: If the source SQL Server on Azure VM is configured with a Distributed Network Name (DNN) listener, the DAG cannot be configured on top of it. Reconfigure the source AG to use a Virtual Network Name (VNN) listener with an Azure Load Balancer before proceeding.
Network Connectivity: Establish connectivity between the source infrastructure and Tessell’s VPC using VPC peering, AWS Direct Connect, Azure ExpressRoute, or site-to-site VPN. Required ports: 5022 (database mirroring endpoint), 1433 (SQL Server), and 59999 (health probe if using Azure Load Balancer). Minimum bandwidth: 100 Mbps. For links with greater than 10ms latency, use asynchronous replication mode.
Service Accounts and Permissions: The service accounts running SQL Server on both source and target must have CONNECT permission on the database mirroring endpoints. If the environments are in different domains, use certificate-based endpoint authentication.
Tessell SQL Server Instance: Provision a Tessell SQL Server HA instance in the target cloud environment. The instance will be configured with its own availability group and listener by Tessell’s provisioning automation.
Migration Process: 3-Step Implementation
The migration follows three phases: provision the target, establish the DAG with data seeding, and execute the failover. The core 3-step process and T-SQL scripts from the original implementation are preserved below with expanded context.
Step 1 — Provision Tessell SQL Server and Prepare Target
Provision a Tessell SQL Server HA instance in your target cloud environment. Once provisioned, connect to the instance via SSMS using SA login or sysadmin credentials (available from Tessell Support as “Genie” credentials).
Disable Tessell server-level triggers. Tessell deploys server-level triggers that restrict direct database creation and modification outside the Tessell UI. These triggers are part of the Availability Machine and Dataflix lifecycle management system — they ensure that all database provisioning, backup, and SLA operations are tracked and managed by Tessell’s control plane. During migration, these triggers must be temporarily disabled to allow the DAG seeding process to create databases on the target.
Important: Document all disabled triggers before proceeding. You will need to re-enable them after the migration is complete to restore Tessell’s Availability Machine and Dataflix SLA coverage.
Remove any existing databases from the target availability group, then drop them. Verify the target AG is empty before proceeding:
-- Verify target AG has no databases
SELECT ag.name AS ag_name, adc.database_name
FROM sys.availability_groups ag
LEFT JOIN sys.availability_databases_cluster adc
ON ag.group_id = adc.group_id
WHERE ag.name = 'tessell_ag';
-- Result should show NULL for database_name
Step 2 — Create Distributed AG and Initiate Seeding
Run the following T-SQL on the source primary replica to create the distributed availability group. This script establishes the DAG named tessell_dag, linking the source AG to the Tessell target AG.
CREATE AVAILABILITY GROUP [tessell_dag]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'source_ag' WITH (
LISTENER_URL = 'tcp://source_listener:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'tessell_ag' WITH (
LISTENER_URL = 'tcp://tessell_listener:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
Then run the corresponding JOIN on the Tessell target primary:
ALTER AVAILABILITY GROUP [tessell_dag]
JOIN
AVAILABILITY GROUP ON
'source_ag' WITH (
LISTENER_URL = 'tcp://source_listener:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
),
'tessell_ag' WITH (
LISTENER_URL = 'tcp://tessell_listener:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC
);
Seeding Mode guidance: Use AUTOMATIC for databases under 1TB — SQL Server performs a full backup and restore automatically. Use MANUAL for databases over 1TB or where database names differ between source and target. Manual seeding requires you to take a full backup with NORECOVERY on the target before joining the DAG.
Monitor seeding progress using the following query on the target:
SELECT ag.name, ar.replica_server_name,
ar.availability_mode_desc,
ars.connected_state_desc, ars.role_desc,
ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars
ON ars.replica_id = ar.replica_id
WHERE ag.is_distributed = 1;
Time estimate: Seeding duration depends on database size and available bandwidth. As a rough formula: [Database Size in GB] / [Network Throughput in Gbps] = hours. Example: a 500GB database over a 1 Gbps link takes approximately 1–2 hours including overhead.
Wait until synchronization_health_desc shows HEALTHY and the role_desc of the target shows SECONDARY before proceeding to failover.
Step 3 — Failover to Tessell
Before initiating failover, verify that the source and target are fully synchronized by comparing the last hardened LSN on both sides:
-- Run on BOTH source and target
SELECT ag.name, ar.replica_server_name,
drs.last_hardened_lsn, drs.synchronization_state_desc
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 ag.group_id = ar.group_id
WHERE ag.is_distributed = 1;
Confirm that last_hardened_lsn matches on both sides and synchronization_state_desc shows SYNCHRONIZED. Then stop all transactions on the source primary.
Set the source AG role to SECONDARY to stop accepting writes:
-- Run on source primary
ALTER AVAILABILITY GROUP [tessell_dag]
SET (ROLE = SECONDARY);
Execute failover on the Tessell target:
-- Run on Tessell target primary
ALTER AVAILABILITY GROUP [tessell_dag]
FORCE_FAILOVER_ALLOW_DATA_LOSS;
About FORCE_FAILOVER_ALLOW_DATA_LOSS: This is the only failover method available for distributed availability groups — manual failover without the force option is not supported for DAGs. Despite the name, no data loss occurs if you have verified that the last_hardened_lsn values match and the synchronization state is SYNCHRONIZED before executing the failover.
Post-failover validation:
Verify the Tessell target now shows role_desc = PRIMARY using the monitoring query from Step 2
Test application connectivity to the Tessell HA Service DNS endpoint
Run spot-check queries to validate data integrity on critical tables
Confirm that all databases in the AG are in a SYNCHRONIZED state on the Tessell replicas
Post-Migration Tasks
After a successful failover, complete the following tasks to finalize the migration and restore full Tessell platform functionality.
Drop the Distributed Availability Group
Remove the DAG from both sides. Dropping the DAG does not affect the underlying availability groups — it only removes the distributed replication link between them.
-- Run on Tessell target (now primary)
DROP AVAILABILITY GROUP [tessell_dag];
-- Run on source (now secondary)
DROP AVAILABILITY GROUP [tessell_dag];
Re-enable Tessell Server Triggers
Re-enable all server-level triggers that were disabled in Step 1. This restores Tessell’s Availability Machine and Dataflix lifecycle management, including automated backups, SLA tracking, and database governance.
-- Validate current trigger state
SELECT name, is_disabled
FROM sys.server_triggers;
-- Re-enable each trigger
ENABLE TRIGGER [trigger_name] ON ALL SERVER;
Verify with Tessell Support that the Availability Machine is active and SLA coverage has resumed after re-enabling triggers.
Update Application Connection Strings
Switch all application connection strings from the source listener to the Tessell HA Service DNS endpoint. Tessell’s HA Service DNS provides automatic failover routing — applications connecting to this endpoint will be routed to the current primary replica without manual intervention during any future failover events.
Decommission the Source Environment
Do not decommission the source immediately. Retain the source environment for 7–30 days as a rollback path. Before decommissioning, complete the following checklist:
Verify all applications are successfully connected to Tessell and no traffic is hitting the source
Take a final backup of the source for archival purposes
Document the source configuration for reference
Schedule decommissioning with the infrastructure team after the retention period
Monitoring and Troubleshooting
Monitoring Queries
Use the following queries to monitor DAG health during and after the seeding process.
DAG synchronization status (run on either side):
SELECT ag.name, ag.is_distributed,
ar.replica_server_name,
ars.role_desc, ars.synchronization_health_desc,
ars.connected_state_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar
ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ars
ON ars.replica_id = ar.replica_id
WHERE ag.is_distributed = 1;
Log send queue and redo queue (run on source primary — indicates replication lag):
SELECT ar.replica_server_name,
drs.log_send_queue_size, drs.redo_queue_size,
drs.last_hardened_lsn,
drs.synchronization_state_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar
ON drs.replica_id = ar.replica_id;
Common Issues and Solutions
| Issue | Solution |
|---|---|
| Seeding stuck at INITIALIZING | Check network connectivity between source and target. Verify firewall allows port 5022. Confirm sufficient disk space on the target for the full database restore. |
| Error 1478: Insufficient log data | Ensure all databases are in FULL recovery model. Take a full backup on the source before creating the DAG. Verify that log truncation has not occurred since the last full backup. |
| NOT SYNCHRONIZING state | Verify listener DNS resolution from both sides. Check endpoint permissions (CONNECT grant). Review the SQL Server error log for detailed connection failure messages. |
| High log_send_queue_size | Indicates network bandwidth saturation. Throttle write-heavy workloads on the source during seeding, or upgrade the network link. Consider scheduling seeding during off-peak hours. |
| Instance name mismatch errors | Use SEEDING_MODE = MANUAL instead of AUTOMATIC. Manually restore the database with matching names on the target before joining the DAG. |
Conclusion:
Distributed availability groups are the most reliable method for migrating SQL Server databases across cloud environments with minimal downtime and near-zero data loss. By establishing continuous replication between independent availability groups on separate clusters, DAGs remove the WSFC boundary constraints that make traditional AGs impractical for cross-cloud scenarios.
The migration process follows a straightforward three-step pattern: provision the target, establish the DAG with automatic or manual seeding, and execute a controlled failover after verifying synchronization. Post-migration, re-enabling Tessell triggers restores full Availability Machine and Dataflix lifecycle management, and switching connection strings to the Tessell HA Service DNS completes the cutover.
For teams planning SQL Server migrations to the cloud, the key success factors are thorough prerequisite validation (version, edition, listeners, network connectivity, recovery model), proper seeding mode selection based on database size, and pre-failover LSN verification to guarantee zero data loss despite the FORCE_FAILOVER_ALLOW_DATA_LOSS command name.
Tessell's managed SQL Server service simplifies this process by automating target provisioning, HA configuration, and ongoing lifecycle management across AWS, Azure, and GCP. To plan your SQL Server migration using distributed availability groups, contact the Tessell team or start a free trial at tessell.com.
