· Uwe Janke
Commands / Invoke-sqmDistributedFailover

Invoke-sqmDistributedFailover Always On

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

START Find both AGs of the Distributed AG (sys.availability_replicas) localAg = is_local=1, remoteAg = the other one; needs -TargetInstance Readiness (skipped with -Rollback): Get-sqmDagSyncSnapshot every DB SYNCHRONIZING/SYNCHRONIZED, not suspended, queues under 1 MB -Force or ShouldProcess? (-WhatIf: Status=WHATIF, nothing changed) Declined: Status=CANCELLED; return ALTER AVAILABILITY GROUP [DAG] MODIFY ... SYNCHRONOUS_COMMIT (both sides) executed on the old primary and on the target Wait until every database is SYNCHRONIZED (-SyncTimeoutSeconds, default 600) Timeout: back to ASYNCHRONOUS_COMMIT, nothing switched, error Old primary: ALTER AVAILABILITY GROUP [DAG] SET (ROLE = SECONDARY) point of no return: from here the rescue command is part of the error text Target: ALTER AVAILABILITY GROUP [DAG] FORCE_FAILOVER_ALLOW_DATA_LOSS no data loss, because both sides were SYNCHRONIZED beforehand Verify roles on the target, write TXT report to -OutputPath Distributed-AG-FAILOVER-{name}-{timestamp}.txt Return { Status, Direction, OldPrimaryAg, NewPrimaryAg, PostFailoverStatus, Steps, ReportFile } DONE

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

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance that hosts the current global primary of the Distributed AG. Accepts pipeline input.
-TargetInstanceString, 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.
-AvailabilityGroupNameString, Required. Name of the Distributed Availability Group.
-SqlCredentialPSCredential, Optional SQL authentication credentials.
-SyncTimeoutSecondsInt32600Maximum wait for SYNCHRONIZED after switching to synchronous commit (30 to 86400). On timeout the mode is set back to asynchronous and nothing is switched.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for the failover report. Created if missing.
-ForceSwitch$falseSkip the confirmation prompt. -WhatIf still only simulates.
-RollbackSwitch$falseFail back to the original primary. Same procedure in the opposite direction; swap -SqlInstance and -TargetInstance. Skips the readiness check.
-EnableExceptionSwitch$falseThrow exceptions immediately.

Return Value

PropertyDescription
StatusSUCCESS, WHATIF, CANCELLED or FAILED.
DirectionFAILOVER or ROLLBACK.
PrimaryAgName of the Distributed AG.
OldPrimaryAg / NewPrimaryAgThe two member AGs: the one that was primary before and the one that is primary now.
PostFailoverStatusReplica roles and sync health on the target after the failover.
StepsThe executed steps with status.
ReportFilePath to the TXT report.

Examples

Example 1, Dry run (shows the plan, changes nothing)

Invoke-sqmDistributedFailover -SqlInstance "SQL01" -TargetInstance "NEW01" `
    -AvailabilityGroupName "MyDAG" -WhatIf

Example 2, Failover with confirmation prompt

Invoke-sqmDistributedFailover -SqlInstance "SQL01" -TargetInstance "NEW01" `
    -AvailabilityGroupName "MyDAG"

Example 3, Failover without prompt (automated)

Invoke-sqmDistributedFailover -SqlInstance "SQL01" -TargetInstance "NEW01" `
    -AvailabilityGroupName "MyDAG" -Force

Example 4, Rollback: the new system is now the primary

Invoke-sqmDistributedFailover -SqlInstance "NEW01" -TargetInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" -Rollback -Force

Example 5, Longer synchronization window and result check

$r = Invoke-sqmDistributedFailover -SqlInstance "SQL01" -TargetInstance "NEW01" `
    -AvailabilityGroupName "MyDAG" -SyncTimeoutSeconds 1800 -Force
$r.Steps | Format-Table
notepad $r.ReportFile