Monitor Microsoft SQL service

#1

Wondering if it’s possible to monitor the SQL service on Windows servers. The documentation on the admin guide references a deprecated service for it.

I tried creating a foreign source definition for TCP Port 1433, and while that service shows up on my database servers, it is in Not Monitored state. Am I on the right path, or is there a different/better way to do this?

Thanks!

0 Likes

#2

There are a couple components at play. The Foreign Source defines “detectors” to specify the names of services. There has to be a matching serivce name in the pollerd and/or collectd config for the system to act on it. Pollerd services check for up/down status while Collectd gathers metrics to store performance info.

In the case of SQL, there are a couple options. You can create a pollerd service to monitor the TCP port, run a SQL query against the server, or check service state via wsman/snmp.

0 Likes

#3

Might be a dumb question, but how do you add a service to pollerd?
Or is there a better way to monitor it?

0 Likes

#4

The documentation for creating pollerd services is at https://docs.opennms.org/opennms/releases/latest/guide-admin/guide-admin.html#ga-service-monitors. I would start by looking at TcpMonitor.

0 Likes

#5

OK I found that there is already an entry for SQServer in the poller config, and it is both enabled and monitored:

image

But on my SQL server, it is not showing up.

All I have is that Microsoft SQL service, but I added that with the foreign source.

Am I missing something? Sorry if I need an “explain it like i’m 5”.

0 Likes

#6

Try updating your Foreign Source definition so the detector is named SQLServer and resync your requisition. That should discover this default service and monitor it.

0 Likes

#7

Edited the foreign source definition but can’t update the requisition, because we don’t have one. We added our interfaces manually as we grew the use of OpenNMS.

Should we create a requisition? What is the purpose of it now and how do we fill it out? Or am I missing the purpose of it?

Sorry for all the questions. :slight_smile:

0 Likes

#8

Nevermind I think I got it, I forgot to add the parameter to the foreign source.

Is there any way to get rid of that other service that I made previously, the Microsoft SQL one? I deleted the foreign source for it and rescanned, but it’s still showing up on the asset.

0 Likes

#9

Also the JdbcMonitor could be an option. Never tried it with MS SQL but with Postgresql. The benefit is, that you also can check if a login works.
In case of Postgresql the TcpMonitor produces login failure log entries in Postgresql server log. Our database admins didn’t like it.

0 Likes

#10

There are several ways to “monitor the SQL service”. It would be interesting what is your monitoring goal? Is it enough for you to know the TCP port from the SQL service is available over the network, then the generic TCP monitor is a good choice. Basically it is the same as you would try to use telnet against the port and see if you can establish a connection.

If you want to be more accurate, you can use the JDBC Monitor. It will try to establish not just a TCP session, it will also establish a database connection against a specific database with credentials.

If you want to go much deeper you can use the JDBC Query Monitor which allows you to run specific queries and evaluates the query result from that output.

You have to weigh the choices. The deeper you go with the monitoring it gets more expensive in terms of resource usage from the monitoring system as well as the monitored server. Additionally maintenance gets more expensive, cause the deeper you monitor the application you leak application specific configurations into your monitoring system, e.g. SQL queries and credentials can change and need to be changed in the monitoring system as well. In very generic terms, if you run the JDBC Query Monitor you won’t need the JDBC Monitor or the TCP Monitor cause it will monitor those cases anyways.

If your “monitor SQL service” requirements also contain performance metrics you need to look into the JDBC Collector which allows you to persist metrics from SQL queries as time series data which can also be used for thresholding.

The more you go into detail with monitoring applications, it becomes more complicated to “detect” a service during provisioning, cause if you have more than 1 applications you need to deal with credentials and queries can be specific to applications.

From my very personal experience maintaining monitoring systems in a production environment, I would not use “Service Detectors” during provisioning at all. You need confidence and control in your monitoring environment which you don’t have when you use “Service Detectors”, but I think this will exceed the topic of this thread.

0 Likes

#11

JDBC does work with MS Sql, but you do need to install the jdbc driver from Microsoft.

2 Likes