Find SQL Instances – Powershell

DBAs working in data centers would often want to find all the SQL Server instances in the network. How this can be done using few simple powershell scripts?

There are multiple ways you can achieve this using powershell.

First we just trust the SQL browser services and assume that it is running on all the machines in the network. This case is very simple one liner

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

The other option is by looking in to active directory. First we will get the list of computers from the active directory, then query the computers using WMI.

Here is the sample code

Clear-Host
$strCategory = "Computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults = $objSearcher.FindAll()
foreach ($objResult in $colResults){
$objComputer = $objResult.Properties; 

$SQLServices = gwmi -ComputerName $objComputer.name -query "select * from 
	win32_service where Name LIKE 'MSSQL%' and Description LIKE '%transaction%'"
	forEach ($SQLService in $SQLServices) {
		write-host  $SQLService.Name on $SQLService.Host  is $SQLService.State
	}
}