Next generation MS SQL monitoring with Checkmk

The next generation MS SQL plug-in, which was released with Checkmk 2.3, provides a series of features and improvements that were next to unthinkable to implement in the old version. The need to upgrade the existing plug-in has grown in recent years for several reasons, which we will address in the description of the implemented changes. The codebase was completely upgraded without sacrificing existing capabilities. This next generation plug-in is one of the most important new features of 2.3.

The old plug-in worked very well for years, but it was not developed for the vast cloud and hybrid infrastructure of today when it was first introduced 12 years ago. A complete overhaul was due to improve its performance, including support for complex cloud-based setups and, due to the deprecation of VBScript from Microsoft, the language the plug-in was written in.

The new plug-in is a vast improvement in many areas. Detecting database instances on-premises is as good as before, but now there’s also support for detection in cloud environments. The plug-in currently supports monitoring of Windows-based database instances from Windows or Linux systems, provided that the Windows and database authentication methods are supported. The detection of databases is automatic, you only need to perform a service discovery.

The monitoring output can optionally be encapsulated across multiple databases to enable different cache times per database. This goes well with the newly introduced parallel execution of calls: It is now only necessary to wait as long as the slowest database. With the old plug-in, you were forced to wait for the sum of all calls, which limited the advantage of fast databases over slower ones. SQL calls execution has been improved and is now faster, making database monitoring a quicker experience.

We also improved support for MS SQL databases, including Azure databases. The previous plug-in covered only Windows databases that were on the same server as the monitoring agent. The new one adds support for monitoring cloud databases and LAN/WAN databases. Azure SQL monitoring is the main use case here, and its monitoring is already as in-depth as necessary for most cases. The plug-in can monitor any database that is compatible with MS SQL queries.

The new plug-in is also not limited to monitoring from a Windows system only, The MS SQL databases can be monitored from a Linux machine as well, using the same codebase, and the same features. This is a welcome improvement for many.

Speaking of codebase, moving on from VBScript was forced due to its deprecation, and the plug-in was decided to be rewritten in Rust. This brought in a series of benefits. Firstly, the plug-in has never been faster, since Rust code can be orders of magnitude more performing than VBScript. Secondly, the plug-in can be cross-compiled to multiple platforms, speeding up testing and development. And lastly, as it is a single executable file, no dependencies need to be pre-installed on the monitoring system. All the dependencies are bundled into the executable. It is way easier for system administrators to deploy the plug-in now.

Moving on from VBScript, we also worked on overhauling the configuration of the plug-in. The old one used ini files, which are common on Windows systems. The plug-in now has new features that are more portable to other database monitoring plug-ins, as well as a configuration format that is well-supported across all platforms. The choice fell on YAML as it is commonly understood by most administrators and developers, and already used in Checkmk elsewhere. YAML proved to be highly beneficial thanks to its hierarchical configuration structure, splitting individual database options from standard ones with ease. It is a well-known format, and its adoption should pose no difficulties to system administrators and developers.

In order to monitor MS SQL databases, you first need to have the right accesses. There are two supported authentication methods in the new plug-in: Windows, and mixed Windows and SQL.

The Checkmk agent runs on Windows as the system user. If this user has enough privileges, which in our case with MS SQL database are view server state, connect SQL, and, optionally, connect any database, then it will work out of the box.

If using the normal Windows system user is undesired or impossible, for security reasons or due to internal policies for instance, it is possible to use a specific user that has access to the databases we want to monitor. This user needs the same privileges as described above.

Installing and configuring the plug-in is greatly streamlined thanks to the Agent Bakery in the commercial Checkmk editions. It also doesn’t differ from the old version. It suffices to add a rule for the MS SQL plug-in. Look under Setup and use the search field to find Microsoft SQL Server (Linux, Windows). The configuration of the plug-in can now be done from the Checkmk interface.

There are quite a few options that can be activated or selected, but only a handful are important for the most common use cases. Make sure to select the right Authentication value: leave the default Local Integrated Authentication (Windows) if you use the system user under Windows to connect to the database.

Otherwise, switch to SQL Database User Credentials and add the required login name and password.

For multiple MS SQL database instances running on the same host, select the Custom instances option. Use Add new element to insert as many database instances as are running on the monitored host, and enter their login credentials as described above.

In case you need specific credentials to connect to the database's host server, you can insert them in the Connection fields.

It is also possible to selectively choose what to collect and which checks should be performed synchronously or asynchronously in the Data to collect (Sections). If you want to ignore a field, you can also do it here.

For asynchronous checks, you can set up the cache age in seconds using the Cache age for asynchronous checks option directly below.

Next, you can choose to map (Piggyback) data to another host in Checkmk.

The next option, Discovery mode of database instances, allows you to select which database instances to monitor or to exclude among those found on the host.

The installation and configuration of the new MS SQL plug-in can also be carried out manually, which is the case with the Checkmk Raw; or if you want to quickly change a variable in the configuration, using the command line. Remember that if you are editing the configuration by hand but run one of the commercial editions, the Agent Bakery will update the agent and your edits will be lost along with the update.

On Checkmk Raw >=2.3.0 you can find the plug-in mk-sql.exe under Setup > Agents > Windows files. Copy the file to C:\ProgramData\checkmk\agent\plugins\ and run it once. That's all you need to do for the actual installation of the MS SQL plug-in.

On the configuration side, the new plug-in uses YAML, and its configuration file is C:\ProgramData\checkmk\agent\config\mk-sql.yml. A good starting skeleton for it is:

mk-sql.yml---
mssql:
  main:
    authentication:
      username: ""
      type: integrated
    instances:
      - sid: myInstance 1
        auth:
          username: myUser1
          password: "123456"
          type: sql_server
      - sid: myInstance 2
        auth:
          username: myUser2
          password: "987654"
          type: sql_server

If you are using a normal Windows user for the databases, you don't need any of the username/password pairs and can use an empty string ('') as their value. If you are running a single MS SQL database instance, you can also remove instances and everything listed below.

With the installation of the plug-in and the configuration file creation both done, it is time to execute a service discovery on the database's host. Checkmk should pick up the database instance(s) and include the right metrics in your monitoring. For the simple use cases, that's all there is to do.

After setting up the plug-in, you can now fine-tune a few parameters that influence how the agent is monitoring your MS SQL databases.

One option is to define thresholds for active database connections. You can change them by selecting the rule MSSQL Connections and changing the value.

Thresholds can also be set for individual files in the databases. Select the rule MSSQL datafile sizes and change the thresholds for used, used part of allocation, and allocated files.

Both these rules can be found under Setup > Services > Service monitoring rules.

The next generation MS SQL plug-in is only the first step into improving database monitoring with Checkmk. More will follow. We are striving towards a simple and robust database monitoring experience that does not only include MS SQL.

We are also working on enlarging authentication. In addition to the Windows and database authentications already supported, we want to add token-based authentication.

Improvements to the auto-discovery of database instances are already in process. Some were partially implemented already, while more will be coming at a later point. This should streamline the initial efforts to monitor new databases.

We are working on reducing the efforts for installing and configuring the new plug-in on very simple setups, where not many options are necessary, including databases like Microsoft SQL Server Express.