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.

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).

# 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.

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.