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.
No, it is nothing to be worried about - the Agent isn't considered a clustered service to SQL Server simply because it can't fail over on its own.
You should test a failover and be confident that SQL Server Agent will start on the other node and that jobs will run etc. (sometimes there can be local dependencies that won't exist on the other node(s)), but the DMV itself should not be a concern. I checked several clusters in our environment and they all say Y for the SQL Server service and N for the other services:
I can assure you that these clusters fail over correctly and that SQL Server Agent works on all nodes.
Best Answer
SQL--> Powershell script i got from this article. Maybe something like this? http://hubpages.com/technology/Using-xp_cmdshell-to-Query-the-Windows-File-System.
I ran this on my desktop's SSMS using a Registered Server connection to a server in my network.
**Originally made for IPCONFIG testing, but you can just adapt it for net start.