SQL Server: manual availability group failover with Set-AGClusterFailover
How the Set-AGClusterFailover PowerShell function works and how to use it (synchronous, forced and DR failover of an Always On group).
Memo on the PowerShell function Set-AGClusterFailover, written by Martin Pugh (2014), which triggers a manual failover of an Always On availability group to a given node. The code is not reproduced here; this describes how it works and how to call it. It relies on the SQL Server module (SQLSERVER:\ provider), the FailoverClusters module and a helper function New-AGSession (not included in the note) that opens a remote PowerShell session to the node.
Syntax
Import-Module \\fileserver\scripts\functions\SQL.Automation.psm1 -Force
# failover to a synchronous node (no action if that node is already primary)
Set-AGClusterFailover -ComputerName sqlnode01
# failover to an asynchronous node (data loss possible)
Set-AGClusterFailover -ComputerName sqlnode02 -Force
# DR: quorum lost, force quorum on the asynchronous node
Set-AGClusterFailover -ComputerName sqlnode02 -Force -ForceDR
| Parameter | Purpose |
|---|---|
-ComputerName |
Cluster node to fail over to |
-Force |
Required for an asynchronous node or a synchronous node that is not synchronized; confirms data loss is accepted |
-ForceDR |
With -Force, only for disaster recovery after losing quorum |
How it works
- Opens a remote session to the target node and lists its availability groups.
- If the node is already primary for a group, nothing is done for that group.
- If the node is
SynchronousCommitandSynchronized, it callsSwitch-SqlAvailabilityGroupwith no data loss. - If the node is synchronous but not synchronized, or asynchronous, the failover only happens with
-Forceand after an interactive confirmation (Read-Host); it usesSwitch-SqlAvailabilityGroup -AllowDataLoss -Force. - With
-Force -ForceDR(asynchronous node): after confirmation, it stops the node in the cluster (Stop-ClusterNode), restarts it withStart-ClusterNode -FixQuorum, sets the node's vote weight to 1 and the other nodes' to 0, then fails over with data loss allowed. - After each switch, it resumes data movement on the other replicas with
Resume-SqlAvailabilityDatabasefor each database in the group.
Notes
-Forceand-ForceDRmust never be used in normal operations: they risk data loss and change quorum settings.- Without a script, the native equivalent is the
Switch-SqlAvailabilityGroupcmdlet (with-AllowDataLossfor a forced failover). - The original code is by Martin Pugh: search for "Set-AGClusterFailover" with his name to get it, and respect its license.