Finding the Active SQL Node

Ever needed to know what node of your SQL cluster is the alive one.  Wonder no more.

SQL:-

Select ServerProperty('ComputerNamePhysicalNetBIOS')

Will show all nodes and there current state.

Or in Powershell:-

# Set cluster name 
$cluster_name = "ClusterName";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
 Write-Host "The current active node of $cluster_name is $owner_node.";
}
else
{
 Write-Host "$cluster_name is not a clustered instance of SQL Server.";
}