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 than Get-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.
function Get-ServiceStatus ([string[]]$server)
{
foreach ($s in $server)
{
if(Test-Connection $s -Count 2 -Quiet)
{
Get-WmiObject win32_Service -Computer $s |
where {$_.DisplayName -match "SQL Server"} |
select SystemName, DisplayName, Name, State, Status, StartMode, StartName
}
}
}
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 to ping myserver in a DOS prompt and the -Quiet flag simply just has it return true or false. 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:
Get-ServiceStatus -server (Get-Content C:\temp\MyServerList.txt)
or
$servers = 'MyServer1','MyServer2','MyServer3'
Get-ServiceStatus -server $servers
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 above
Example:
Import-Module ActiveDirectory
$sList = $ipList | Select -ExpandProperty IP
$results = foreach ($i in $sList) {
Get-ADComputer -Filter 'IPv4Address -eq $i' -Properties * | Select Name}
Get-ServiceStatus -server $results
Utilizing Write-Verbose and a try/catch block may be useful, and in most cases a good 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 have added the SystemName property to the output to include the actual server name returning information, in case this is used this for more than one server at a time.
