SQL Server - How to Stop, Start, and List Windows Services Remotely

Views: 430
Reading Time: 7 minutes

Hello people,
Good afternoon!

In this post I will demonstrate how to manage Windows Services from the SQL Server database using SQLCLR to perform these operations. This can be especially useful when creating monitoring for certain services.

Currently where I work, I have created a service monitoring configuration table, where I determine the data of the services I want to monitor, such as server name, service name, if failing need to send sms and if failing the routine itself should take charge to start the service automatically. The email with the list of services that have changed situation is always sent. Once configured, job collections are performed every X minutes of the status of these services and this data is stored in a history table.

This enables you to perform service availability meters and using Reporting Services 2016 to create reports by reading from the history table, you can retrieve current data from that table and track the status of various services on multiple servers from anywhere. : Whether through the Browser (accessing the Reporting Services path) or mobile (using the PowerBI application), without having to have a computer nearby, connect to the company's VPN or buy a monitoring system, which in addition to having high costs does not always offer this flexibility.

To perform these operations with the CLR, I will list two different ways to obtain this information, and after reading, you will understand why. In the source code you will read below, I used the Return class to manage warnings and error messages that I send to the database and it is a prerequisite for using Service Management Stored Backgrounds. You can find the source code of the Return class in the post. SQL Server - How to send warnings and error messages to the bank through CLR (C #).

I will demonstrate two distinct ways of doing the same thing. The first one uses native binaries of the operating system itself and, mainly in the service listing part, there are manual manipulations of information, which in the case of the other class I will present, this is already encapsulated in the .NET framework. What motivates me most to use this first alternative is that it has no DLL dependency outside of the standard DLLs supported and recommended by Microsoft. The System.ServiceProcess library, on the other hand, requires several other unsupported DLLs that have dependencies.

SQL Server - CLR Object Dependencies System.ServiceProcess ServiceController Class

When your bank has a problem and you send the error DUMP to Microsoft, the first thing the consultant will point out as a problem is CLR DLLs not supported by Microsoft, such as System.ServiceProcess, so I prefer to avoid. These unsupported DLLs are DLLs that can be registered manually, but not in SAFE mode. Introduction to SQL Common Language Runtime (CLR) in SQL Server.

Using the Process class from the System.Diagnostics library
One of the ways to accomplish these activities through CLR is by using the Process class, which allows us to execute Prompt-DOS commands within C #. With this, you can use the sc.exe binary (learn more about it clicking this link) to stop and start services, and binary wmic.exe (learn more about it) clicking this link) to list the services.

How to list the services of a server:

SQL Server - SC binary CLR list windows services Windows Service Manager

SQL Server - SC binary CLR list windows services

How to start a service:

SQL Server - SC binary CLR start windows services2

How to stop a service:

SQL Server - SC binary CLR stop windows services

Using the ServiceController Class from the System.ServiceProcess Library

How to list the services of a server:

SQL Server - SC binary CLR list windows services Windows Service Manager

SQL Server - SC ServiceController Class CLR

How to start a service:

SQL Server - SC ServiceController CLR start windows services

How to stop a service:

SQL Server - SC CL ServiceController CLR stop windows services

That's it folks!
I hope you enjoyed the post. It's great to create different things using the database.

Any questions, leave it here in the comments.
Hug!

sql server clr sqlclr service controller sc wmic windows services services windows list start stop list view start stop

sql server clr sqlclr service controller sc wmic windows services services windows list start stop list view start stop