LLI Wiki — My Digital Knowledge Base

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

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

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

Mastodon LLIMastodon SocialFosstodon