Find inactive nodes in a SQL Server availability group cluster
PowerShell script listing failover cluster nodes that own no availability group.
In a Windows failover cluster hosting SQL Server Availability Groups, it is useful to spot the nodes that own no availability group, in other words the "inactive" nodes. This PowerShell script compares the list of cluster nodes with the nodes that own availability group resources.
Prerequisites
- The
FailoverClustersmodule (Failover Clustering RSAT tools) must be available. - The account used must be allowed to query the clusters.
Script for several clusters
$clusters = @("sqlcluster01", "sqlcluster02", "sqlcluster03")
foreach ($cluster in $clusters) {
Write-Host $cluster "--------"
$clu = Get-Cluster $cluster
$l1 = (Get-ClusterResource -Cluster $clu | Where-Object {$_.ResourceType -eq "SQL Server Availability Group"} | Group-Object -Property OwnerNode)
$l2 = Get-ClusterNode -Cluster $clu | Group-Object -Property Name
Compare-Object $l2 $l1 -Property Name
}
How it works
$l1groups theSQL Server Availability Groupresources by owner node (OwnerNode).$l2groups the cluster nodes by name.Compare-Object ... -Property Nameshows the differences: a node present in$l2but missing from$l1(side indicator<=) owns no availability group.
Single-cluster version
$clu = Get-Cluster sqlcluster02
$l1 = (Get-ClusterResource -Cluster $clu | Where-Object {$_.ResourceType -eq "SQL Server Availability Group"} | Group-Object -Property OwnerNode)
$l2 = Get-ClusterNode -Cluster $clu | Group-Object -Property Name
Compare-Object $l2 $l1 -Property Name
Notes
- Cluster names are examples to adapt.
- If the output is empty, every node owns at least one availability group.