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
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 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
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 the SystemName
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.
The return value of -1
makes sense when you consider what ExecuteNonQuery()
does. It returns the number of rows affected or -1
in case of set nocount on
or non-updating queries.
As per documentation,
For UPDATE, INSERT, and DELETE statements, the return value is the
number of rows affected by the command. ... For
all other types of statements, the return value is -1. If a rollback
occurs, the return value is also -1.
Try .ExecuteReader()
instead.
Best Answer
The easiest way to do this (read: least amount of additional code you need to write) is to use
Invoke-DbaQuery
from thedbatools
PowerShell module.Remove the
foreach
loop - this function will handle it for you. Pass the list of instances intoInvoke-DbaQuery
and include the-AppendServerInstance
switch, and all the instances will be queried and the name of each included in the output - just like when you do a multi-instance query in SSMS. You'll get one object back which is a collection of the results of the query against each instance.Since you don't mention script performance/elapsed time being an issue, I intentionally did not mention running multiple queries in parallel and then merging the results.