sqmSQLTool › Commands › Get-sqmAlwaysOnHealthReport

Get-sqmAlwaysOnHealthReport Always On

Creates a detailed AlwaysOn AG health report per SQL Server instance: replica sync health, LSN lag (redo/send queue), database status, connection state, listener config, and running AutoSeed operations. Outputs TXT, CSV, and interactive HTML. Pipeline-capable.

Execution Flow

START dbatools available? throw ERROR foreach $instance in $SqlInstance [pipeline] Get-DbaAvailabilityGroup → $ags Detect SQL version (ProductMajorVersion) for AutoSeed query DMV queries via Invoke-DbaQuery ag/ar/ars/adbrs: role, sync state, redo/send queue, suspend AutoSeed (≥SQL 2016): dm_hadr_automatic_seeding WHERE completion_time IS NULL Graceful handling for SQL 2016 vs 2019+ column differences OverallStatus: Critical / Warning / OK per DB/replica row ShouldProcess → create output dir + write test file Write TXT (per-AG table) + CSV if $OutputHtml → _GenerateAlwaysOnHealthHtml → interactive HTML Build instance result: HealthRows, Status, file paths Return $allInstanceResults[] DONE

Synopsis

Queries all AGs on the given SQL Server instance via dbatools and DMVs. For each AG, all replica/database rows are evaluated: redo queue, send queue, synchronization state, connection state, and suspend status determine an OverallStatus (OK / Warning / Critical). AutoSeed progress is shown for SQL Server 2016+. Reports are saved as TXT, CSV, and a filterable/sortable interactive HTML dashboard.

Requires dbatools and VIEW SERVER STATE on the target instance. The SQL Server major version is auto-detected to select the correct AutoSeed DMV query (SQL 2019+ adds group_database_id and size columns).

Syntax

Get-sqmAlwaysOnHealthReport
    [-SqlInstance <String[]>]    # pipeline
    [-SqlCredential <PSCredential>]
    [-MaxRedoQueueMB <Int>]
    [-MaxSendQueueMB <Int>]
    [-OutputPath <String>]
    [-OutputHtml]
    [-ContinueOnError]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMESQL Server instance(s) to query. Pipeline-capable.
-SqlCredentialPSCredential—Credential for the SQL connection.
-MaxRedoQueueMBInt100Redo queue warning threshold in MB (secondary replicas).
-MaxSendQueueMBInt50Send queue warning threshold in MB.
-OutputPathStringC:\System\WinSrvLog\MSSQLDirectory for TXT, CSV, and HTML report files.
-OutputHtmlSwitch$trueGenerate an interactive HTML dashboard in addition to TXT/CSV.
-ContinueOnErrorSwitchfalseContinue with the next instance on error.
-EnableExceptionSwitchfalseThrow terminating exceptions.

OverallStatus Logic

StatusCondition
CriticalDbSyncState not in (SYNCHRONIZED/SYNCHRONIZING) OR ConnectionState ≠ CONNECTED OR IsSuspended = true.
WarningRedoQueueMB > MaxRedoQueueMB (secondary) OR SendQueueMB > MaxSendQueueMB. Also: any AutoSeed in progress.
OKAll checks passed.

Examples

# Health report for local instance
Get-sqmAlwaysOnHealthReport

# Custom thresholds and output path
Get-sqmAlwaysOnHealthReport -SqlInstance "SQL01" -MaxRedoQueueMB 200 -OutputPath "D:\Reports"

# Multiple instances via pipeline
"SQL01","SQL02" | Get-sqmAlwaysOnHealthReport -ContinueOnError