sqmSQLTool › Commands › Invoke-sqmDistributedFailover

Invoke-sqmDistributedFailover Always On

Performs a controlled failover of a Distributed Availability Group. Validates readiness (score ≥ 75/100 via Test-sqmDistributedAgReadiness), discovers the secondary AG from sys.availability_replicas, issues ALTER AVAILABILITY GROUP … FAILOVER, verifies the new primary role, and exports a detailed TXT report. Use -Rollback to fail back after a failed migration without the readiness check.

Execution Flow

START -Rollback mode? No ↓ Test-sqmDistributedAgReadiness → ReadinessScore Score < 75 → throw "Failover NICHT empfohlen (score/100)" | Status=FAILED Rollback: skip readiness check (WARNING logged) sys.availability_groups → sys.availability_replicas → dm_hadr_availability_replica_states WHERE ag.name = $AgName AND is_distributed=1 AND role_desc='SECONDARY' → $secondaryServer / $secondaryAgName -Force or ShouldProcess? Status=CANCELLED; return Invoke-DbaQuery: ALTER AVAILABILITY GROUP [$AgName] FAILOVER Start-Sleep 3 | Query sys.availability_replicas → role_desc per replica $postFailoverStatus collected for report Write TXT report → $OutputPath\Distributed-AG-Failover-{AgName}-{timestamp}.txt Report: Primary/Secondary AG names, target server, replica roles post-failover Return PSCustomObject { Status, Direction, PrimaryAg, SecondaryAg, SecondaryServer, PostFailoverStatus, ReportFile } DONE

Synopsis

For a normal failover, Test-sqmDistributedAgReadiness is called first; a ReadinessScore below 75 aborts the operation. The secondary AG and its server are discovered via a T-SQL query against sys.availability_groups (SQL 2016 compatible — no dm_hadr_distributed_ag_replica_member_status). Without -Force, a ShouldProcess prompt is shown. The failover uses ALTER AVAILABILITY GROUP … FAILOVER (no FORCE_FAILOVER_ALLOW_DATA_LOSS). After a 3-second pause, replica roles are verified and a TXT report is written to -OutputPath.

-Rollback skips the readiness check and is intended for fail-back after a failed migration. Use with caution — it performs the same FAILOVER SQL statement.
Critical operation. Always test in a DR environment first. The readiness check only validates synchronization health — it does not guarantee zero data loss. Always verify the report after the operation.

Syntax

Invoke-sqmDistributedFailover
    [-SqlInstance <String>]            # default: $env:COMPUTERNAME
    -AvailabilityGroupName <String>    # required
    [-SqlCredential <PSCredential>]
    [-OutputPath <String>]             # default: C:\System\WinSrvLog\MSSQL
    [-Force]                            # skip confirmation
    [-Rollback]                         # fail back, skip readiness check
    [-EnableException]
    [-WhatIf] [-Confirm]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMEPrimary SQL Server instance of the Distributed AG. Accepts pipeline input.
-AvailabilityGroupNameString—Required. Name of the Distributed Availability Group.
-SqlCredentialPSCredential—Optional SQL authentication credentials.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for the failover report. Created if missing.
-ForceSwitch$falseSkip the ShouldProcess confirmation prompt.
-RollbackSwitch$falseFail back to the original primary. Skips the readiness check.
-EnableExceptionSwitch$falseThrow exceptions immediately.

Return Value

PropertyDescription
StatusSUCCESS, CANCELLED, or FAILED.
DirectionFAILOVER or ROLLBACK.
PrimaryAgName of the Distributed AG that was failed over.
SecondaryAgSecondary AG name discovered from the cluster.
SecondaryServerSQL instance that was the secondary before the failover.
PostFailoverStatusQuery result showing replica roles and sync health after the failover.
ReportFilePath to the TXT failover report.

Examples

# Failover with confirmation prompt
Invoke-sqmDistributedFailover -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG"

# Failover without prompt (automated/scripted)
Invoke-sqmDistributedFailover -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" -Force

# Dry run (shows plan without executing)
Invoke-sqmDistributedFailover -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" -WhatIf

# Rollback to original primary after a failed migration
Invoke-sqmDistributedFailover -SqlInstance "SQL01" `
    -AvailabilityGroupName "MyDAG" -Rollback -Force

# Check result
$r = Invoke-sqmDistributedFailover -SqlInstance "SQL01" -AvailabilityGroupName "MyDAG" -Force
$r.PostFailoverStatus | Format-Table
notepad $r.ReportFile