List of SQL Server instances and version on Cluster Nodes

With a large windows cluster with many installed SQL Server instances it can get tricky to quickly check which instances are on which nodes, and a what patch level they’re running at. It’s also a good idea to check what revision level the instances think they’re on after a patching session.

In the grand tradition of lazyefficient DBAs I don’t want to be manually checking each node and instance for this information. So I use the following Powershell script:

Import-Module FailoverClusters
$Cluster=$args[0]
$clustnodes = Get-ClusterNode -Cluster $Cluster

foreach($node in $clustnodes){
echo "$node`n------------------"
	$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $node)
	$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL")
	foreach($val in ($regkey.GetValueNames() | sort)){
		$regKey2= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\$val\\MSSQLServer\\CurrentVersion" )
		echo "$val `t`t $($regkey2.GetValue("CurrentVersion"))"
	}
echo "`n`n"
}

This can then be run as:

.\clust-version.ps1 my-clust

The returned results are in plain text, and need to be pairedup. But unless you’re a *LOT* of instances that’s the easy bit.

sql-01
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-02
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1

sql-03
------------------
INSTANCE1		10.50.1600.1
INSTAMCE2		10.0.2531.0
INSTANCE3		10.0.2531.0
INSTANCE4		10.50.1600.1
Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

No comments yet.

Leave a Reply