Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using PowerShell to Pull SQL Server Services Configuration Information


By:   |   Read Comments (1)   |   Related Tips: More > PowerShell

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem

Is there a way to pull information about the SQL Server services running on a server, without using SQL Server Configuration Manager?

Solution

Logging into a server and being able to get a quick run down of how SQL Server is configured can be valuable information when troubleshooting issues. Finding out what instances are running (if more than one), what services are running for each instance, and then what account each service is using are usually things I look at first. However there may be other settings that you are concerned with.

The Old Way

Short of using the GUI, the old fashioned way was writing a VBScript to query WMI (Windows Management Instrumentation). This was a very powerful tool (at the time) and very useful for getting information quickly. Microsoft provides an example of doing this in Books Online. If you follow this link to the MSDN article you will find the script, along with a bit more information about it. (I have included the script from the MSDN article below for reference.):

'Source: MSDN Article: http://msdn.microsoft.com/en-us/library/ms186353(v=sql.100).aspx
set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement10")
for each prop in wmi.ExecQuery("select * from SqlServiceAdvancedProperty & _
 where SQLServiceType = 1 AND PropertyName = 'VERSION'")
WScript.Echo prop.ServiceName & " " & prop.PropertyName & ": " & prop.PropertyStrValue
next

The New (and much cooler) Way

Have you heard of the awesomeness of PowerShell? PowerShell offers a way to interact with WMI, similar to VBScript, but in a much more efficient and easier way. PowerShell is more interactive compared to VBScript that requires you to (1) type out your commands in notepad, (2) save it with a "vbs" extension, (3) then open a command prompt and call the script as something like "cscript myscript.vbs". Compare that to PowerShell where I can type out the command in the PowerShell prompt and instantly get back output from a command.

For our particular task the cmdlet we need to work with is Get-WmiObject, which does exactly what it sounds like: Gets a WMI Object. The syntax works the same way as VBScript in that you have to pass the namespace and the class that you want to "get" information from. [If you want to get more information about the Get-WMIObject you can type the following in a PowerShell prompt: Get-Help Get-WmiObject -full]

The namespace we want to use is the GetObject portion of the VBScript, except all we need is "root\Microsoft\SqlServer\ComputerManagement10". With PowerShell we don't have to include all that "WINMGMTS:\\.\" on it. "ComputerManagement10" is for SQL Server 2008, if you are running SQL Server 2005 it is just "ComputerManagement". The VBScript ExecQuery selects from the class "SqlServiceAdvancedProperty". We will call the same class with PowerShell. The following PowerShell script will return the exact information that the VBScript does. (NOTE: The backward apostrophe key is used as a line break in PowerShell, and you can also break the line at the pipe (|) character as well. This was used strictly for formating so it displays correctly on this page. You can type this command out on one line if desired.)

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 `
 -Class SqlServiceAdvancedProperty | 
 Where-Object {$_.PropertyName -eq 'VERSION'} | 
 Format-Table ServiceName, PropertyName, PropertyStrValue -AutoSize

The output from the lab server I have running returns this when I run the VBScript:

pulling information about the SQL Server services running on a server without using sscm

Then using the PowerShell script to pull the same information it outputs as this:

using the powershell script

Now take note in order to run the VBScript I first had to save it in Notepad as myScript.vbs and then open a command prompt to run it. Compare that to PowerShell, where I just opened up PowerShell and typed out my command, then ran it. Which do you think took less time?

Going a step further

There are a number of other classes we can use to get information with in WMI for SQL Server. If you want to see what other classes are available you can type out this command at a PowerShell prompt:

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 -List

there are a number of other classes we can use to get information with in WMI for SQL Server

You will get a list of objects, around 70, but we are only interested in the ones without the double underscore ("__") in front of the name. Those are the classes that can be used under this Namespace. I provided the link to the MSDN article that will show the documentation for each of these classes at the end of this tip.

One of the best things that PowerShell offers is the ease in finding out what information can be returned from an object. If you want to see what properties are up under a certain class, like the one we just worked with, you pipe that information to the cmdlet Get-Member.

one of the best things that PowerShell offers is the ease in finding out what information can be returned from an object

This will return 3 columns of information: Name, MemberType, and Definition. The properties of interest to me are ServiceName, PropertyName, PropertyNumValue, and PropertyStrValue. I know what properties I want, now I want to see a rough list of what information can be returned from those properties. The following script will give you a table of the properties under the SqlServiceAdvancedProperty class:

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 `
 -Class SqlServiceAdvancedProperty | 
 Format-Table ServiceName, PropertyName, PropertyNumValue, PropertyStrValue -AutoSize

this script will give you a table of the properties under the SqlServiceAdvancedProperty class

This screenshot shows a portion of the output I received on my lab server. As you can see from what I highlighted, this is how I found the PropertyName(s) of interest to me.

So following this route I checked out a few of the other classes to seek out the information I wanted. I want to know the instance name(s), SQL services, the startup mode of those services, current state of each service, and then what account the service runs as.
I found the SqlService class contained everything I needed. After piping it through Get-Member I determined what properties I needed to look at. The following script gives me that information, with a comment section on what some of the values returned stand for:

Get-WmiObject -Namespace root\Microsoft\SqlServer\ComputerManagement10 `
 -Class SqlService | 
 Format-Table ServiceName, DisplayName, StartMode, StartName, State -AutoSize

the SqlService class contained everything I needed

With the instance I get that from the display name of the service.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Shawn Melton Shawn Melton started his IT career in 2002 and has experience working with SQL Server 2000, 2005, 2008, and 2008 R2.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, May 17, 2013 - 5:05:11 PM - Andrew Back To Top

Your article was much appreciated, saved me a bucket load of time documenting services, network protocols, client protocols and aliases for a migration project I am doing of lots of servers.

No going to configuration manager, just collect remotely

 


Learn more about SQL Server tools