Select Page

Since I have written a process that checks all of our SQL Services I have been researching other methods and might have found a quicker way using Get-Service but would require an addition step to pass the arguments accordingly on a server by server basis. Potentially I could setup a nightly process to query all of the sql related services for all our environments and save the results to a table. Then loop through the records by server type (Production, Development, Test, Staging, etc…) for any service with a startup -eq “Auto” with a state -ne “running”. If configured properly this could potentially report a service outage for a given server.

This option is great for getting information about a single service but one thing I read was that you can pass multiple arguments for the service name; however, this method doesn’t format it well.

function GetServiceInfo ($server, $service) {
Get-Service -Name $service -ComputerName $server
}
GetServiceInfo Server01 SQLWriter

SingleHashTable

This option loads the results into an array and then formats the hash table nicely especially when sending multiple arguments for the service name.

function GetServiceInfo ($server, $service) {
$svcs = @(Get-Service -Name $service -ComputerName $server)
$svcs | ft -AutoSize
}
GetServiceInfo Server01 SQLWriter, SQLServerAgent, SQLBrowser, ReportServer, MSSQLServerOlapService, MSSQLServer, MSDTSServer110

HashTable

The only problem you will have with this particular method is with the error message. It will not accurately report a “permissions issue” as Get-WmiObject would. It will falsely report that it cannot find any service with the service name: ‘service name would go here’ instead of reporting that it encountered a permissions issue against the said server. That’s only if you supply the service name. If you do a blanket query without specifying the service name then it will reveal the permissions issue and you do that like so…

function GetServiceInfo ($server) {
$services = @()
$services = @(Get-Service -ComputerName $server | Where-Object {$_.name -like '*sql*' -or $_.name -like '*MsDTS*' -or $_.name -like '*ReportServer*'})
$services | ft -AutoSize
}
GetServiceInfo Server01

If you have the permissions against the target server then you will see this…

GetService

If you don’t have the proper permissions then you will see something like this…

GetServiceError

Be sure to keep that in mind that the target servers have the correct service account with the appropriate access.

Share This