Planned failover of a Distributed Availability Group without data loss, following the documented procedure: checks that all databases are ready, switches both availability groups to SYNCHRONOUS_COMMIT, waits for SYNCHRONIZED, sets the old primary to SECONDARY and runs FORCE_FAILOVER_ALLOW_DATA_LOSS on the target (safe, because both sides are synchronized). If the synchronization does not complete, the mode is set back to ASYNCHRONOUS_COMMIT and nothing is switched. Use -Rollback to fail back.
Execution Flow
Synopsis
The failover of a Distributed AG is not ALTER AVAILABILITY GROUP … FAILOVER. The function runs the documented sequence: both sides to SYNCHRONOUS_COMMIT, wait until all databases are SYNCHRONIZED (-SyncTimeoutSeconds), SET (ROLE = SECONDARY) on the old primary, then FORCE_FAILOVER_ALLOW_DATA_LOSS on the target. The name of the last command is misleading but the step is safe here, because both sides are synchronized. Before anything changes, the replication state of every database is checked (SYNCHRONIZING or SYNCHRONIZED, not suspended, send and redo queue below 1024 KB); otherwise the function stops with a message that lists the reasons.
-SqlInstance is the current global primary, -TargetInstance the instance that hosts the forwarder (the future primary). For -Rollback swap the two: -SqlInstance is the new system, -TargetInstance the old one. The readiness check is skipped for a rollback.
Critical operation. After the failover both sides stay on SYNCHRONOUS_COMMIT, which is the prerequisite for a rollback. If the step after the old primary was set to SECONDARY fails, the error text contains the rescue command. Test the complete procedure on a test system first.
Syntax
Invoke-sqmDistributedFailover
[-SqlInstance <String>] # current global primary, default: $env:COMPUTERNAME-TargetInstance <String> # forwarder = future primary-AvailabilityGroupName <String> # name of the Distributed AG, required
[-SqlCredential <PSCredential>]
[-SyncTimeoutSeconds <Int32>] # 30..86400, default 600
[-OutputPath <String>]
[-Force] # skip confirmation
[-Rollback] # fail back, skip readiness check
[-EnableException]
[-WhatIf] [-Confirm]
Parameters
Parameter
Type
Default
Description
-SqlInstance
String
$env:COMPUTERNAME
SQL Server instance that hosts the current global primary of the Distributed AG. Accepts pipeline input.
-TargetInstance
String
,
Instance that hosts the forwarder (primary of the other AG), the future primary. Needed for the procedure; without it the function stops with a clear message.
-AvailabilityGroupName
String
,
Required. Name of the Distributed Availability Group.
-SqlCredential
PSCredential
,
Optional SQL authentication credentials.
-SyncTimeoutSeconds
Int32
600
Maximum wait for SYNCHRONIZED after switching to synchronous commit (30 to 86400). On timeout the mode is set back to asynchronous and nothing is switched.
-OutputPath
String
C:\System\WinSrvLog\MSSQL
Directory for the failover report. Created if missing.
-Force
Switch
$false
Skip the confirmation prompt. -WhatIf still only simulates.
-Rollback
Switch
$false
Fail back to the original primary. Same procedure in the opposite direction; swap -SqlInstance and -TargetInstance. Skips the readiness check.
-EnableException
Switch
$false
Throw exceptions immediately.
Return Value
Property
Description
Status
SUCCESS, WHATIF, CANCELLED or FAILED.
Direction
FAILOVER or ROLLBACK.
PrimaryAg
Name of the Distributed AG.
OldPrimaryAg / NewPrimaryAg
The two member AGs: the one that was primary before and the one that is primary now.
PostFailoverStatus
Replica roles and sync health on the target after the failover.
Steps
The executed steps with status.
ReportFile
Path to the TXT report.
Examples
Example 1, Dry run (shows the plan, changes nothing)