Using PowerShell to Pull SQL Server Services Configuration Information

By: Shawn Melton   |   Updated: 2011-04-14   |   Comments (1)   |   Related: > PowerShell

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:

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

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

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.

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 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

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

Next Steps

• I do hope you continue on going through the other classes and see what information you can grab. PowerShell is a wonderful tool to learn and can help you find a heap of information pertaining to your SQL Server instance. Check out the PowerShell categoryto see additional tips that show you how PowerShell can save you a little time.
• MSDN Link: WMI Provider for Configuration Management Classes
• Related Tip: Monitor, Start, and Stop SQL Server services using xp_servicecontrol

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips