Discover SQL Servers with Powershell via the registry

Chuck Boyce Jr (blog | twitter) recently commented on a limitation of the script from my post Discover SQL Servers with Powershell. The script does require that the SQLBrowser service is running for discovery to occur which may be a major issue for some. Here’s an alternative method that does not have this limitation. All SQL Server instances should have their name registered in the following registry key HKLM\Software\Microsoft\Microsoft SQL Server\InstalledInstances which we can access remotely with Powershell.

registry installedinstances thumb Discover SQL Servers with Powershell via the registry

All this script requires is that you place a text file containing computer names in your user profile folder (C:\Users\Rhys on my laptop).

?View Code POWERSHELL
# Text file containing computers to search for sql instances
$computers = Get-Content "$env:USERPROFILE\computers.txt";
 
# Check each computer for installed SQL Server
# instances by searching the registry
foreach($computer in $computers)
{
	try
	{
		$sql = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$computer).OpenSubKey('SOFTWARE\Microsoft\Microsoft SQL Server').GetValue('InstalledInstances');
		foreach($sqlserver in $sql)
		{
			if($sqlserver -eq "MSSQLSERVER") # Default instance
			{
				Write-Host -ForegroundColor Green "$computer (Default instance)";
			}
			else # Named instance
			{
				Write-Host -ForegroundColor Green "$computer\$sqlserver (Named instance)";
			}
		}
	}
	catch [System.Exception]
	{
		Write-Host -ForegroundColor Red "Error accessing $computer. " $_.Exception.Message;
	}
}

When you execute the script it will search each computer and list the SQL Server instances it discovered in the registry.

discovered sql servers thumb Discover SQL Servers with Powershell via the registry

If you receive the error "You cannot call a method on a null-valued expression" then that computer does not have the registry key we are searching for. In other words; no SQL Server instances are on that computer. Hopefully this method should allow you to get a more complete view of the SQL Servers in your organisation.


Leave a Reply

Current ye@r *