Getting SQL Services Information with Powershell

Hopefully you already know everything about your SQL estate, including what services are installed and what’s running them, either because it’s so small you just know, or, preferably, you have some kind of CMDB. But what if you don’t? Powershell comes to the rescue, of course.

This post details a script to capture basic information – service name, account, start mode and status – about the database engine, Agent and any SSRS/SSAS/SSRS installs. It requires remote WMI connectivity and, for SSIS, SMB access to enabled administrative shares on the drives where the service is installed.

(Note – if all you’re after is the Database Engine and/or SQL Agent info, and you’re running SQL 2008 R2 SP1 or higher, no need to carry on reading – just use the sys.dm_server_services DMV instead, it’s easy.)

To begin we need some kind of data source containing both the hostnames and instance names (MSSQLSERVER if default instance) of the servers in the estate – as we’ll be connecting to the hostname but making checks using the instance name. Either stored in a table;

$instancesquery = "SELECT Hostname, InstanceName
                    FROM dbo.Instances"

$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery

or from another source, say, a csv file – in this case an example of a single instance, which I’ll use as an example source for the rest of the post;

$instances = Import-Csv -Path "C:\temp\servers.txt"

Then we can loop over the servers and use the Get-WmiObject Win32_Service cmdlet. For the database engine, Agent, SSRS and SSAS this is straightforward – we’re filtering on the service name looking for a reference to the instance, then disregarding any CEIP services and pulling out a few pieces of information. For example, a snippet of the script checking for a SSAS install;

$SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode
Write-Output $SSAS

SSIS is a bit trickier. You may have noticed that, unlike for the Database Engine, SSRS or SSAS, the instance name is not part of the service name;

So we can’t just filter on the service name to check which instance the service is installed as part of. Instead we need to find the MsDtsSrvr.ini.xml file associated with the service (by using the service PathName), open it up as XML (this is where the requirement for SMB and administrative share access arises) and look at the DtsServiceConfiguration.TopLevelFolders.Folder.ServerName element;

$ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''

$ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)

[xml]$ConfigXML = Get-Content -Path $ConfigXMLPath

if($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")
{
    Write-Output $SSIS
}
elseif(($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))
{
    Write-Output $SSIS
}

An example output from the whole script, ran on a single server;

This shows I have the database engine and agent running from MSAs, and two extra services installed – SSAS running as a local service and SSIS running as an MSA, all set to auto-start and only SSIS not currently running.

The entire skeleton script is below (github link), using the CSV file example for retrieving host/instance names as a base and simply writing the output to console – it’s easily modifiable to change the source and/or save the output to a file or table.

<#       
                
$instancesquery = "SELECT Hostname, InstanceName       
                
                   FROM dbo.Instances"       
                
       
                
$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery       
                
#>       
      
$instances = Import-Csv -Path "C:\temp\servers.txt"       
                          
foreach ($instance in $instances)                     
{       
    Write-Output "Connecting to $($instance.Hostname)\$($instance.InstanceName).."       
                         
    try       
    {               
        $test = Get-WmiObject Win32_Service -ComputerName $instance.Hostname -ErrorAction Stop                 
    }       
    catch       
    {       
        Write-Output "Error connecting remotely to WMI"       
                
        continue       
    }       
                  
    # Database Engine                
    $DatabaseEngine = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $DatabaseEngine       
                

    # SQL Agent               
    $SQLAgent = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Agent ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $SQLAgent       
                  
    # SSAS       
    $SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       

    Write-Output $SSAS       

    # SSRS       
    $SSRS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Reporting Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       
                
    Write-Output $SSRS       

    #SSIS       
    $SSISinstances = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -Like "SQL Server Integration Services*" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, PathName, StartName, State, StartMode       
                

    try       
    {       
        foreach($SSIS in $SSISinstances)       
        {          
            $ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''       

            $ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)       

            [xml]$ConfigXML = Get-Content -Path $ConfigXMLPath              

            if ($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
            elseif (($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
        }       
    }       
    catch       
    {       
        Write-Output "SSIS Error: " + $error[0]       
    }       
}