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-SQLInstance1)
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.
Best Answer
If you want something that will be useful for the future I would probably steer clear of trying to search the registry. The hives for SQL Server have changed a bit over the years and it can be troublesome to keep up with.
The method with the
SqlDataSourceEnumerator
is flaky at times and although I will use it, not concrete evidence that instances are on the network. I believe it depends on SQL Browser Service as well, which most of the time I find disabled.I will utilize the WMI class
win32_Service
. I use this because it offers up more information about the service thanGet-Service
cmdlet does.I write everything as functions generally because you can use this to actually just do daily check or verification of the service for troubleshooting.
This is a bit more than what I usually use but in case someone else comes across and wants to use it. The
Test-Connection
equates toping myserver
in a DOS prompt and the-Quiet
flag simply just has it returntrue
orfalse
. This will default to 4 pings so setting-Count 2
just makes it do it twice instead.The variable
[string[]]$server
is a method used to state that$server
will accept an array of server names. So an example call of this function could look something like:or
EDIT
A noted comment is the above does depend on a list of servers being provided. In cases where I am not provided that list you do have a few other options.
If I am in an Active Directory environment I can use the ActiveDirectory module in PowerShell to pull a list of all the servers on the domain with
Get-ADComputer
cmdlet. A word of warning though make sure you use a good-Filter
on large domains.I have also simply done an IP scan (with approval) of a network that gives me the IP addresses where port 1433 was found open. I will take that IP list and utilize
Get-ADComputer
to find the domain computer names, then pass that into the function aboveExample:
EDIT
The suggested edit to utilize
Write-Verbose
and also add in try/catch block, while that may be useful, and in most cases a code practice, I will leave that up to the person that wants to use this function to add that additional code or functionality. Just trying to provide a basic example to go on. I did add theSystemName
property to the output to include the actual server name returning information, do this on other functions just generally don't use this for more than one server at a time so it slipped my mind.