Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallYou can aim for a no-data-loss failover of a SQL Server distributed availability group (AG), but the failover command itself does not guarantee that result: Microsoft documents FORCE_FAILOVER_ALLOW_DATA_LOSS as the supported manual failover type. Before using it, establish the SQL Server versions and replica roles, enable the synchronization protections required by the version-specific procedure, and verify that the global primary and forwarder have matching per-database last_hardened_lsn values. If synchronization cannot be verified, do not describe the failover as lossless.
Understand what is failing over
A distributed AG links two separate availability groups, often on different clusters. The primary replica of the second AG is the forwarder: it receives transactions from the global primary and forwards them to its local secondary replicas. A distributed AG failover is manual, not an automatic response that by itself preserves every transaction. Microsoft describes the supported failover type as a manual user-initiated FORCE_FAILOVER_ALLOW_DATA_LOSS. The synchronization preparation is therefore essential when the objective is no data loss. See Microsoft’s distributed AG guidance for SQL Server 2016.
Distributed AGs were introduced in SQL Server 2016 and are used for disaster recovery across clusters and for migration scenarios. The two AGs can run different SQL Server versions in some migration paths, so identify the version on each side rather than applying a procedure based only on the new or old site. Microsoft’s business continuity and database recovery overview describes these use cases.
Check the version and topology before starting
Record which AG contains the current global primary, which replica is the forwarder, and which site is intended to become primary. Confirm the SQL Server version on each AG and whether you are planning a controlled site transition or responding to an outage. Microsoft’s no-data-loss instructions differ between SQL Server 2022 and later and SQL Server 2019 and earlier.
#1 Best Overall
| SQL Server version family | What to use | Important distinction |
|---|---|---|
| SQL Server 2022 and later | Use the version-specific no-data-loss sequence in Microsoft’s distributed AG configuration guidance. | Distributed AGs support REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT; the prescribed value of 1 makes commits wait for the secondary and may affect performance. |
| SQL Server 2019 and earlier | Use the instructions for the deployed version in Microsoft’s version-specific configuration guidance. | Do not assume the SQL Server 2022-and-later setting or sequence applies to these versions. |
Before proceeding, confirm that the replicas and databases required by the applicable procedure are online and healthy, that the distributed AG reports synchronized, and that the relevant replicas use the required synchronous-commit configuration. If a site or replica is unavailable and these checks cannot be completed, the available evidence does not establish a lossless failover.
SQL Server 2022 and later: follow the synchronized failover sequence
Use this branch only for SQL Server 2022 and later, and follow the Microsoft procedure for the exact topology. The sequence below explains its decision points; it is not a substitute for the version-specific instructions or a topology-specific runbook.
- Establish synchronous protection. Configure synchronous commit between the relevant primaries and across the distributed AG as the procedure requires. On the global primary, set
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMITto 1 using Microsoft’s documented syntax and scope. - Wait for synchronization and check health. Do not proceed just because the setting was changed. Verify the replicas are healthy and the distributed AG is synchronized.
- Compare hardened log positions. For each database covered by the procedure, compare
last_hardened_lsnon the global primary and forwarder. Matching values are the documented readiness check. If the values differ, stop and use Microsoft’s applicable retry or failback branch; the state is not proven lossless. - Change the distributed AG role and initiate failover. Microsoft instructs changing the global primary’s distributed AG role to
SECONDARY, then initiating failover from the intended forwarder withFORCE_FAILOVER_ALLOW_DATA_LOSS. Run the documented T-SQL for the correct replica and distributed AG name; do not run the command on an assumed instance. - Apply the documented post-failover setting. Reset
REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMITon the new secondary as Microsoft’s procedure directs. If geographic latency warrants it, the guidance allows asynchronous commit to be restored after failover.
The exact sequence and T-SQL are in Microsoft’s SQL Server 2022-and-later distributed AG procedure. A value of 1 for REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT makes commits wait for the secondary; this protects synchronization but can reduce performance, particularly across geographically distant sites.
SQL Server 2019 and earlier: do not reuse the newer sequence
For SQL Server 2019 and earlier, use Microsoft’s no-data-loss instructions for the version you actually run. The SQL Server 2022-and-later procedure relies on a distributed AG setting that the documentation identifies as supported in SQL Server 2022 and later. Do not substitute a similar-looking setting, skip a step, or assume that a successful forced failover proves all log records were hardened on the target.
Free tools Windows power users keep installed
One-click scans. No signup required.
Rank #3
If the documented checks for your version do not establish synchronization, treat the outcome as uncertain rather than promising zero data loss. Use the source’s relevant retry or failback path where applicable, and involve a DBA familiar with the particular two-cluster topology before taking irreversible action.
Do not confuse forwarder initialization with failover
Manual seeding prepares a database on the forwarder; it is not the failover itself and does not alone guarantee a lossless transition. Microsoft’s documented manual initialization uses a full backup and a transaction log backup taken on the global primary, restored on the forwarder with NORECOVERY, followed by joining the database to the distributed AG. Follow the complete version-specific instructions for backup order, restore, and join operations in the configuration guide.
Rank #4
If this is an emergency and synchronization cannot be proved
A forced failover may be appropriate when the former global primary is unavailable and the business accepts possible data loss. That is a different decision from a controlled no-data-loss failover. Do not infer safety from command success, replica role changes, or a database becoming accessible; if hardened LSNs cannot be checked or do not match, the documented readiness evidence for a lossless outcome is absent.
After a forced failover with data loss, account for the former primary before reconnecting it. Microsoft’s standard AG forced-failover guidance warns that the old primary may later assume the primary role; where that guidance matches the incident topology, remove it from the availability group to prevent replicas from entering inconsistent states. See Microsoft’s manual failover guidance.
Recommended Free Tools
Best Value
Choose the recovery method that matches the failure
- Planned site transition: Use the version-matched distributed AG no-data-loss procedure, and proceed only when its synchronization and hardened-LSN checks pass.
- Site outage with synchronization evidence available: Evaluate the same checks against the surviving forwarder and Microsoft’s applicable retry or failback path before initiating failover.
- Site outage without synchronization evidence: Decide whether potential data loss is acceptable; the forced command cannot turn an unverified state into a proven lossless one.
- Protection from accidental changes: Log shipping is a separate disaster-recovery design option, not an equivalent distributed AG failover procedure. Microsoft notes that a configurable delay can help account for human error, and that log shipping can be combined with AGs. See the business continuity overview.
Where the topology, SQL Server versions, or LSN state are unclear, pause before executing a forced failover and get qualified SQL Server disaster-recovery assistance. No hardware purchase or generic recovery utility can substitute for verifying the AG’s synchronization and backup state.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
