LLI Wiki — My Digital Knowledge Base

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

  1. Opens a remote session to the target node and lists its availability groups.
  2. If the node is already primary for a group, nothing is done for that group.
  3. If the node is SynchronousCommit and Synchronized, it calls Switch-SqlAvailabilityGroup with no data loss.
  4. If the node is synchronous but not synchronized, or asynchronous, the failover only happens with -Force and after an interactive confirmation (Read-Host); it uses Switch-SqlAvailabilityGroup -AllowDataLoss -Force.
  5. With -Force -ForceDR (asynchronous node): after confirmation, it stops the node in the cluster (Stop-ClusterNode), restarts it with Start-ClusterNode -FixQuorum, sets the node's vote weight to 1 and the other nodes' to 0, then fails over with data loss allowed.
  6. After each switch, it resumes data movement on the other replicas with Resume-SqlAvailabilityDatabase for each database in the group.

Notes

Mastodon LLIMastodon SocialFosstodon