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

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

Parameter	Type	Default	Description
-SqlInstance	String	$env:COMPUTERNAME	Primary SQL Server instance of the Distributed AG. Accepts pipeline input.
-AvailabilityGroupName	String	—	Required. Name of the Distributed Availability Group.
-SqlCredential	PSCredential	—	Optional SQL authentication credentials.
-OutputPath	String	C:\System\WinSrvLog\MSSQL	Directory for the failover report. Created if missing.
-Force	Switch	$false	Skip the `ShouldProcess` confirmation prompt.
-Rollback	Switch	$false	Fail back to the original primary. Skips the readiness check.
-EnableException	Switch	$false	Throw exceptions immediately.

Return Value

Property	Description
Status	`SUCCESS`, `CANCELLED`, or `FAILED`.
Direction	`FAILOVER` or `ROLLBACK`.
PrimaryAg	Name of the Distributed AG that was failed over.
SecondaryAg	Secondary AG name discovered from the cluster.
SecondaryServer	SQL instance that was the secondary before the failover.
PostFailoverStatus	Query result showing replica roles and sync health after the failover.
ReportFile	Path to the TXT failover report.

Examples

Example 1 — Failover with confirmation prompt

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

Example 2 — Failover without prompt (automated/scripted)

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

Example 3 — Dry run (shows plan without executing)

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

Example 4 — Rollback to original primary after a failed migration

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

Example 5 — Check result

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

⬆ Index

All Commands