What is the most effective way to discover all running instances of SQL Server using PowerShell?
15
votes
5
answers
48586
views
I've been tasked with discovering all the instances of SQL Server that are running within our domain. In several cases there are multiple instances per server. I've seen two different PowerShell methods of finding these instances, but neither seem to find all the instances.
1) Use WMI
$srvr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $computerName
$instances = $srvr | ForEach-Object {$_.ServerInstances} | Select @{Name="fullName";Expression={$computerName +"\"+ $_.Name}}
return $instances
2) Use remote registry (as with Get-SQLInstance[1] )
The biggest problem I'm running into is that not all of the servers that I know about are running with the SQL Server WMI provider nor are all of them allowing remote registry. Is there a third method? I can use Remote Desktop to access all the servers but I'm looking at approximately 30 machines and would like to avoid manual steps if possible. This only needs to work for SQL Server 2008 and higher and while it would be nice to know about the other SQL Server services (SSIS/SSAS/SSRS) my main focus is on SQL Server itself.
Asked by Elsimer
(365 rep)
Oct 15, 2014, 03:09 PM
Last activity: Aug 3, 2024, 02:38 PM
Last activity: Aug 3, 2024, 02:38 PM