Execution Flow
Synopsis
Queries sys.availability_groups WHERE is_distributed = 1 joined with replica and database state DMVs. The DAG sync state is derived by comparing last_hardened_lsn with last_sent_lsn. Each primary AG's replicas and databases are written as a structured TXT report and a flat CSV for further analysis.
Requires dbatools and SQL Server 2016 SP1 or later (Distributed AGs are not supported on earlier versions). The SQL Server major version is verified before querying.
Syntax
Get-sqmDistributedAgHealth [-SqlInstance <String[]>] # pipeline [-SqlCredential <PSCredential>] [-OutputPath <String>] [-ContinueOnError] [-EnableException]
Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
| -SqlInstance | String[] | $env:COMPUTERNAME | SQL Server instance(s) to query. Pipeline-capable. |
| -SqlCredential | PSCredential | — | Credential for the SQL connection. |
| -OutputPath | String | C:\System\WinSrvLog\MSSQL | Directory for TXT and CSV report files. |
| -ContinueOnError | Switch | false | Continue with the next instance on error. |
| -EnableException | Switch | false | Throw terminating exceptions. |
Return Value
| Property | Description |
|---|---|
| SqlInstance | The queried SQL Server instance. |
| DistributedAgCount | Number of Distributed AGs found. |
| ReplicaCount | Unique replica count across all DAGs. |
| DatabaseCount | Unique database count across all DAGs. |
| Status | OK / Error. |
| TxtFile / CsvFile | Written report file paths. |
| Details | Raw DMV result rows for further processing. |
Examples
# Health report for local instance Get-sqmDistributedAgHealth # Multiple instances Get-sqmDistributedAgHealth -SqlInstance "SQL01","SQL02" -OutputPath "D:\Reports" # Pipeline "SQL01" | Get-sqmDistributedAgHealth -ContinueOnError