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:
SQL Agent
To retrieve the SQL Agent service for a specific instance name:
SQL Full-text Filter
To retrieve the SQL Full-text Filter service for a specific instance name:
SQL Browser
To retrieve the SQL Browser service:
SQL Analysis
To retrieve the SQL Analysis service for a specific instance name:
SQL Reporting
To retrieve the SQL Reporting service for a specific instance name:
SQL Integration
To retrieve the SQL Integration service:
Service Object
Each function returns an object with the following properties and methods:
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:
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.