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.