Commands / Invoke-sqmFailover
Invoke-sqmFailover
Always OnsqmSQLTool v1.8.2+ · Operations⚠ High Impact  ✓ Pre/Post Checks
Performs a controlled (non-forced) Always On AG failover with comprehensive pre- and post-checks.

Pre-checks: Synchronization status, redo queue size (-MaxRedoQueueMB, default 50 MB).

Failover: Executes ALTER AVAILABILITY GROUP ... FAILOVER on the target secondary replica (automatically selects the first SYNCHRONIZED secondary if not specified).

Post-checks: New primary reachability, all databases reach SYNCHRONIZED state.

Parameters

ParameterTypeRequiredDefaultNotes
-SqlInstancestringRequired—Current PRIMARY instance.
-SqlCredentialPSCredentialOptional—Credentials for the connection.
-AvailabilityGroupstringRequired—Name of the availability group.
-TargetReplicastringOptionalAuto-select first SYNCHRONIZEDInstance name of the target secondary. If not specified, the first SYNCHRONIZED secondary is chosen automatically.
-MaxRedoQueueMBintOptional50Maximum redo queue size in MB. Failover is aborted if exceeded.
-WaitAfterFailoverSecondsintOptional30Wait time (5-300 seconds) after failover before post-checks run.
-ContinueOnErrorswitchSwitch$falseReturn result object instead of throwing errors.
-EnableExceptionswitchSwitch$falseThrow exceptions immediately.
-WhatIf / -ConfirmswitchOptional—ShouldProcess (ConfirmImpact: High) guards failover execution.

Execution Flow

START dbatools available? NO throw: dbatools not found YES Connect to primary replica (-SqlInstance) Verify primary is reachable and holds AG Get AG info: replicas, sync state, redo queue Select target replica (auto-select first SYNCHRONIZED if not specified) Read redo_queue_size per database PRE-CHECKS • All DBs have status = SYNCHRONIZED on target? • Redo queue on target < -MaxRedoQueueMB? If FAIL: return error and abort Pre-checks passed? NO return: pre-check failed status YES ShouldProcess? (ConfirmImpact: High) NO WhatIf: failover plan continue YES ALTER AVAILABILITY GROUP (target) FAILOVER Execute on target secondary replica Wait -WaitAfterFailoverSeconds (default 30s) Allow new primary to come online POST-CHECKS • New primary (was target) is reachable? • All databases have status = SYNCHRONIZED on new primary? If FAIL: log warning Return [PSCustomObject] failover result AvailabilityGroup, OldPrimary, NewPrimary, Status, Duration DONE

Examples

Test failover with -WhatIf
Invoke-sqmFailover -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -WhatIf
Failover to specific replica with custom redo queue threshold
Invoke-sqmFailover -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -TargetReplica "SQL02" -MaxRedoQueueMB 10
← Back
Command Index