sqmSQLTool › Commands › Get-sqmAlwaysOnFailoverHistory

Get-sqmAlwaysOnFailoverHistory Always On

Evaluates the Windows Application Event Log for AlwaysOn AG role-change events (EventID 1480) and forced-failover indicators (EventID 19407). Optionally adds WSFC Operational Log events and SQL role_start_time data. Pipeline-capable, supports multiple computers.

Execution Flow

START foreach $computer in $ComputerName [pipeline] Source 1: Application Log (always) Get-WinEvent LogName=Application Id=1480,19407 Since=$Since Collect lease-expired times (EventID 19407) for Forced detection foreach EventID 1480: _ParseAGName / _ParseRole / _GetFailoverType FailoverType: Forced(19407≤5min) / Planned(user/manual) / Automatic / Unknown Apply $AvailabilityGroup filter if set Source 2: WSFC Operational Log (if -IncludeClusterLog) Get-WinEvent Microsoft-Windows-FailoverClustering/Operational Id=1641 Add events as Source='ClusterLog', NewRole='PRIMARY' Source 3: SQL role_start_time (if -SqlInstance) Invoke-DbaQuery: dm_hadr_availability_replica_states current_configuration_commit_start_time_utc (approximation) Sort results by FailoverTime DESC ShouldProcess → write TXT + CSV to $OutputPath Return $allResults[] DONE

Synopsis

Parses the Windows Application Event Log for AG role-transition events (EventID 1480) since a configurable lookback date. Detects forced failovers via EventID 19407 (lease expiration) within 5 minutes of a role-change event. Optionally supplements with WSFC Operational Log (EventID 1641) and SQL DMV dm_hadr_availability_replica_states role timestamps.

Requires read access to the Windows Event Log of the target computer. SQL connection is only needed when -SqlInstance is specified. No dbatools needed for event log queries alone.

Syntax

Get-sqmAlwaysOnFailoverHistory
    [-ComputerName <String[]>]    # pipeline
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-AvailabilityGroup <String>]
    [-Since <DateTime>]
    [-IncludeClusterLog]
    [-OutputPath <String>]
    [-ContinueOnError]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-ComputerNameString[]$env:COMPUTERNAMETarget computer(s). Remote Event Log queries via Get-WinEvent -ComputerName. Pipeline-capable.
-SqlInstanceString—SQL Server instance for supplementary role_start_time data (optional).
-SqlCredentialPSCredential—Credential for the SQL connection.
-AvailabilityGroupStringall AGsFilter results to a specific AG name.
-SinceDateTime30 days agoEarliest event timestamp to include.
-IncludeClusterLogSwitchfalseAlso query the WSFC Operational Log (EventID 1641). Only available on WSFC nodes.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for TXT and CSV report files.
-ContinueOnErrorSwitchfalseContinue with the next computer on error.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value

PropertyDescription
ComputerNameSource computer for this event.
AvailabilityGroupAG name parsed from the event message.
FailoverTimeEvent timestamp (local time).
NewRolePRIMARY / SECONDARY / RESOLVING / UNKNOWN.
FailoverTypePlanned / Automatic / Forced / Unknown.
EventIdWindows Event ID (1480 / 19407 / 1641 / null).
SourceApplicationLog / ClusterLog / RoleStartTime.
MessageFirst 200 characters of the event message.

FailoverType Detection Logic

TypeDetection Rule
ForcedEventID 19407 (lease expiration) occurred ≤ 5 minutes before the EventID 1480 role-change.
PlannedEventID 1480 message contains "user", "manual", or "manuell".
AutomaticEventID 1480 message contains "automatic", "WSFC", or "automatisch".
UnknownNone of the above patterns matched.

Examples

# Check last 30 days on local computer
Get-sqmAlwaysOnFailoverHistory

# Check last 90 days on a specific server
Get-sqmAlwaysOnFailoverHistory -ComputerName "SQL01" -Since (Get-Date).AddDays(-90)

# Full check with cluster log and SQL DMV
Get-sqmAlwaysOnFailoverHistory -ComputerName "SQL01" `
    -SqlInstance "SQL01" -AvailabilityGroup "AG_Prod" -IncludeClusterLog

# Multiple servers via pipeline
"SQL01","SQL02" | Get-sqmAlwaysOnFailoverHistory -Since (Get-Date).AddDays(-7)