Skip to content

Get SQL Server services with PowerShell

SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete.
But as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell!

This blog introduces a first step towards an automation process of SQL Server administration. I will retrieve all SQL Server services related to a specific instance name.
The process will always be similar by using the SMO WMI server PowerShell object.

Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!

SQL Engine

To retrieve the SQL Engine service for a specific instance name:

Get-SQLEngine_function.png

SQL Agent

To retrieve the SQL Agent service for a specific instance name:

Get-SQLAgent_function.png

SQL Full-text Filter

To retrieve the SQL Full-text Filter service for a specific instance name:

Get-SQLFullTextFilter_function.png

SQL Browser

To retrieve the SQL Browser service:

Get-SQLBrowser_function.png

SQL Analysis

To retrieve the SQL Analysis service for a specific instance name:

Get-SQLAnalysis_functionpng.png

SQL Reporting

To retrieve the SQL Reporting service for a specific instance name:

Get-SQLReporting_function.png

SQL Integration

To retrieve the SQL Integration service:

Get-SQLIntegration_function.png

Service Object

Each function returns an object with the following properties and methods:

service_properties_and_methods.png

You are able to start, restart or stop your service. But you can also retrieve specific information such as the Service Account, the Start Mode or the Service Account.

Next Step

If you do not want to proceed just for a specific instance, but for all instances, you can list all instance names in that way:

Get-SQLInstances_function.png

Then, with your list of instance names, you loop by calling each function. Do not forget to test if the service returned exists (by testing if it is null).

To finish my article, all these functions are part of our Database Management Kit (DMK) developed by our team. We use it to access faster to common and standards information, but also to automate processes.

For example, the DMK is able (in just one command!) to make a security audit of your SQL Server environment, by following the best practices from Microsoft and from our experts. A report is generated at the end of the audit to list all the security points to review.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Nathan Courtine

Nathan Courtine

Nathan Courtine has more than nine years of experience in Microsoft solutions, specializing in SQL Server installation, migration, performance analysis, and automation. He holds Microsoft Certified Solutions Expert (MCSE), Microsoft Certified Azure Administrator Associate, and AWS Certified Solutions Architect Associate credentials. He holds a Computer Science degree from ENSISA Mulhouse. His industry experience covers the public sector, pharma, IT, and banking/financial services.View Author posts

40 posts since December 2013

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *