Current SQL Server services configuration

I know I can use SQL Server Configuration Manager to determine 1) Which services are currently running and 2) which services are set to auto start. However, I have several dozen servers I manage. I’d like to run a quarterly check to make sure all services are still set to auto start and to see which services are currently on. Is there a table or procedure that will give me this information?

I recommend using @Mike’s awesome Powershell script. However as an alternative if your SQL Servers are 2008R2 SP1 or latest and if you have list of servers registered under “Local Server Groups” or “Central Management Servers” you can run use the following query to get service status info:

SELECT   servicename ,
        startup_type ,
        startup_type_desc ,
        status ,
        status_desc ,
        process_id ,
        last_startup_time ,
        service_account ,
        filename ,
        is_clustered ,
        cluster_nodename
FROM    sys.dm_server_services

You can also use this to see status of SQL services in an instance with out actually RDPing and opening configuration manager. You can read more about sys.dm_server_services DMV here

I would use Powershell for this task. Here’s a function that leverages Get-WMIObject, WQL, and some syntax to take a list of servers to analyze.

function Get-SQLServiceInfo{
    param([string[]] $servers)
    $return = @()
    $wql = "SELECT * FROM Win32_Service WHERE name like '%SQL%' and (startmode != 'Auto' or state != 'Running')"

    foreach($server in $servers){
        $return += Get-WmiObject -ComputerName $server -query $wql | select name,startmode,state
    }

    $return
}

Get-SQLServiceInfo 'localhost'

Now, there are several ways to approach the matter of scanning all your SQL instances. I like to use Central Management Server for this task. The code I would use looks something like this(with the call to the above function):

$CMS=’CMSSERVER’
$servers=@((dir "SQLSERVER:SQLRegistrationCentral Management Server Group$CMS").Name)

$servers+=$cms

Get-SQLServiceInfo $cms

So you could run this report manually, or implement it in some additional code to build and email reports out to you. And with it automated, I’d actually say you could have this run once a day instead of quarterly.