1. Options
| Option | RPO | RTO | Needs | Best for |
|---|---|---|---|---|
| Async AG replica in the DR site (same WSFC) | Seconds | Minutes | A stretched cluster with quorum design, Enterprise edition | One AG across sites |
| Distributed AG (AG of AGs) | Seconds | Minutes | Two separate clusters, Enterprise edition | Separate clusters per site, migrations, cross-domain setups |
| Log shipping | Log backup interval (5–15 min) | 15–60 min | Backups + a file share; any edition | Simple, cheap, many databases, Standard edition |
| Backup to cloud + restore | Hours | Hours | Backups in object storage | Tier 3 |
2. Async replica in the existing AG
ALTER AVAILABILITY GROUP AG_Sales ADD REPLICA ON 'SQL-DR-01'
WITH (ENDPOINT_URL = 'TCP://sql-dr-01.corp.local:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL,
SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = NO));
Set the DR node's cluster vote to 0 ((Get-ClusterNode SQL-DR-01).NodeWeight = 0) so a WAN failure doesn't affect quorum in the primary site. Add a listener IP in the DR subnet (multi-subnet listener).
3. Distributed AG
Each site has its own WSFC and AG. The distributed AG links the primaries:
-- on the primary AG's primary replica
CREATE AVAILABILITY GROUP DAG_Sales WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
'AG_Sales_DC1' WITH (LISTENER_URL = 'TCP://sql-sales-dc1.corp.local:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC),
'AG_Sales_DC2' WITH (LISTENER_URL = 'TCP://sql-sales-dc2.corp.local:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC);
-- on the DR AG's primary replica
ALTER AVAILABILITY GROUP DAG_Sales JOIN AVAILABILITY GROUP ON
'AG_Sales_DC1' WITH (LISTENER_URL = 'TCP://sql-sales-dc1.corp.local:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC),
'AG_Sales_DC2' WITH (LISTENER_URL = 'TCP://sql-sales-dc2.corp.local:5022',
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC);
Note that LISTENER_URL uses the endpoint port (5022), not 1433.
4. Log shipping
Invoke-DbaDbLogShipping -SourceSqlInstance sql-prd-01 -DestinationSqlInstance sql-dr-01 `
-Database Sales -SharedPath \\backup01\logship -LocalPath B:\LogShip `
-BackupScheduleFrequencySubdayInterval 5 -CopyScheduleFrequencySubdayInterval 5 `
-RestoreScheduleFrequencySubdayInterval 5 -GenerateFullBackup -Standby -Force
Monitor with sp_help_log_shipping_monitor, and alert when the last restore is older than the RPO.
5. Forced failover runbook (primary site lost)
Async AG replica
- Confirm the primary site is unavailable, and that the decision to fail over has been made (possible data loss).
- On the DR node, force quorum if needed:
Start-ClusterNode -Name SQL-DR-01 -FixQuorum. ALTER AVAILABILITY GROUP AG_Sales FORCE_FAILOVER_ALLOW_DATA_LOSS;- Check the databases are online. Resume data movement on other replicas later.
- Repoint DNS or the listener. Apps using
MultiSubnetFailover=Truereconnect.
Distributed AG: run ALTER AVAILABILITY GROUP DAG_Sales FORCE_FAILOVER_ALLOW_DATA_LOSS; on the primary replica of the DR AG.
Log shipping: apply any remaining log backups, then run RESTORE DATABASE Sales WITH RECOVERY;. Then fix orphaned users, sync jobs and logins.
6. Failback
Resume or reseed the old primary as a secondary, let it synchronise, then make a planned failover back in a maintenance window.
Warning
Logins, SQL Agent jobs, linked servers and certificates (TDE) don't travel with databases. Restore the TDE certificate to DR servers before you need it, or encrypted databases can't be restored there.