Checkmk
DeutschEspañolFrançaisItaliano日本語
Cloud (SaaS) master2.3.02.2.02.1.02.0.01.6.0
to checkmk.com

Monitoring Windows Monitoring agents Catalog of Check Plug-ins
Tip

This article is currently under construction and is being expanded on a regular basis.

1. Introduction

Checkmk enables you to comprehensively monitor Microsoft SQL Server (abbreviated to 'MSSQL' in this article). You can find a complete list of all available check plug-ins in our Catalog of Check Plug-ins.

In order to be able to monitor the databases, you just need the agent plug-in mk-sql in addition to the Checkmk agent. As of Checkmk 2.3.0, this agent plug-in is also able to monitor databases on remote systems. No additional software is required either on the Checkmk server or on the database server.

The only mandatory requirement for the use of mk-sql is that the TCP/IP protocol is activated in the SQL server network configuration. If TCP/IP is not permitted or allowed in your MSSQL server environment, until further notice you will have to fall back to the mssql.vbs legacy plug-in.

2. Setting up a login for the monitoring

The agent plug-in requires access to your MSSQL instances. As a rule, it will be clear — long before the monitoring team arrives — what such access can or may look like. In the case of MSSQL, there are two options. Either you exclusively use the Windows Authentication mode or the mixed mode SQL Server and Windows Authentication mode. In the following two sections, we explain what to look out for in these respective procedures.

2.1. Using the system user (Windows only)

The Checkmk agent is executed by the system user (NT AUTHORITY\SYSTEM) on Windows systems. If you can also use this system user for monitoring MSSQL, you only need to make sure that this login has at least permissions for the following securable elements (Securables):

  • View server state

  • Connect SQL

Depending on which of your instances' databases you want to monitor and depending on which server roles and mappings the system user (NT AUTHORITY\SYSTEM) already has, it may also be necessary to grant permission for Connect any database.

2.2. Using a dedicated account for the monitoring

There are also good reasons for not running MSSQL monitoring via the Windows system user, the most banal reason being that there is no Windows system user on Linux systems. Further reasons can include security requirements within the company or simply the desire to have dedicated and clearly named logins, the meanings and functions of which can be readily recognized from their names.

The agent plug-in of course also works with such logins.

The basic requirement in MSSQL is that the Server authentication is set to SQL Server and Windows Authentication mode. If you do not want to or cannot use the Windows system user, this requirement should already have been met, but this point should not go unmentioned.

If there is no corresponding user on your MSSQL server or in your instance, you can create one using the Microsoft SQL Server Management Studio:

Selecting the 'New Login...' option in MSSQL.

This login requires the following permissions:

  • View server state

  • Connect SQL

  • Connect any database

Grant these permissions in the Object Explorer via Security > Logins. Open the properties of the login and click on Securables. Under Explicit you will find the three entries mentioned above. Set the corresponding check marks in the Grant column and confirm with OK.

Grant 'Connect any database' permission.

Alternatively, you can also create the user and the permissions via the command line. In the following example, this is done for the user checkmk:

mssql> USE master;
      CREATE USER checkmk WITH PASSWORD = 'MyPassword123';
      GO

and then:

mssql> USE master;
      GRANT CONNECT SQL TO checkmk;
      GRANT CONNECT ANY DATABASE TO checkmk;
      GRANT VIEW ANY DATABASE TO checkmk;
      GO

3. Setting up the connection manually

If you use CRE Checkmk Raw or do not want to use the Agent Bakery of the commercial editions, set up the communication manually.

For the rest of the description, we will assume that the agent for Windows monitoring has already been installed on the host.

3.1. Creating the configuration file

On the Windows host, create a mk-sql.yml file in the directory specified below:

If you are using the system user, this will be sufficient:

C:\ProgramData\checkmk\agent\config\mk-sql.yml
---
mssql:
  main:
    authentication:
      username: ''
      type: integrated

If instead you work with the dedicated account for monitoring, you will need the following content:

C:\ProgramData\checkmk\agent\config\mk-sql.yml
---
mssql:
  main:
    authentication:
      username: checkmk
      password: MyPassword123
      type: sql_server

If you are running multiple MSSQL instances with individual user names and passwords on the host, add the details of these instances to the yml file accordingly. Your yml file could then look like this, for example:

C:\ProgramData\checkmk\agent\config\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

3.2. Installing the agent plug-in

Under Setup > Agents > Windows files you will find the file mk-sql.exe in the Windows Agent section.

Selection of the Checkmk agent plug-in.

Download the file and save this on the Windows host in the directory C:\ProgramData\checkmk\agent\plugins\. Then execute the file once.

3.3. Configuring services

Now that you have installed and configured the agent plug-in, you can perform a service discovery for this host. The following screenshot shows only a selection of the services that can be found:

Extract from the service discovery.

4. Extended configuration

4.1. Asynchronous execution of the agent plug-in

The agent plug-in for monitoring MSSQL can — like so many others — be executed asynchronously, to take account of longer runtimes for large MSSQL instances for example.

To run the agent plug-in asynchronously under Windows, modify the configuration of the agent and add the following entry to the execution section under plugins:

C:\ProgramData\checkmk\agent\check_mk.user.yml
plugins:
    execution:
        - pattern: mssql.vbs
          cache_age: 300
          async: yes

You can find detailed instructions explaining the asynchronous execution of agent plug-ins on a Windows host in the article on the Windows agent.

4.2. Configuring thresholds

Monitoring connections

You can specify your own thresholds for the utilization of the maximum number of simultaneous connections specified by MSSQL, as these are much more dependent on the structure of your MSSQL instance than with other services. You can set up the corresponding thresholds in no time at all using a rule from the MSSQL Connections rule set.

Setting the upper thresholds for active connections.

Monitoring file sizes

You can also define thresholds for the sizes of individual files in MSSQL. To do this, use the rule set MSSQL datafile sizes.

Setting the file sizes used.

5. Configuration using the Agent Bakery

CEE Setup is greatly simplified in the commercial editions using the Agent Bakery, as syntax errors in the configuration files are avoided and adjustments to changing environments can be made easily. The main difference compared to a manual installation is that you will only need to work on the MSSQL host on the command line if you want to make special MSSQL-specific configurations.

For the initial setup, it is sufficient to create a corresponding rule in the agent bakery. You can find the rule set under Setup > Agents > Windows, Linux, Solaris, AIX > Agent rules. You can then search for mssql in the search field:

The rule 'Microsoft SQL Server (Linux, Windows)' in the agent rules.

Depending on how the agent plugin is allowed to log in to your SQL server (see Setting up a login for monitoring), select the appropriate option under Authentication. If you use the system user (NT AUTHORITY\SYSTEM), leave the selection at Local Integrated Authentication (Windows).

Possible settings for the MSSQL server in the agent bakery.

Instead, use the SQL Server Authentication, select the SQL Database User Credentials option, also enter the Login name in the User field and add the corresponding Password.

If you are running multiple MSSQL instances with different user names and passwords on the host, select the Custom instances option. Use Add new element to access the details for the first — and then each additional — instance to be monitored. Enter the access data for each instance here, as described above.

Multiple MSSQL instances in the Bakery rule.

5.1. Further options

With the settings you have just made, you first create a simple agent for monitoring your MSSQL instance, which is located directly on the host. All available data is evaluated directly in Checkmk and this should be sufficient for a standard setup. However, if you have a more complex MSSQL world in your organization, there are further options, both for monitoring the database on the same host and for monitoring it on a remote host. These are basically the same for both connection types.

Option Function

Connection

If you need more specific connection data for the general connection to the MS SQL server, you can enter it here.

Data to collect (Sections)

Here you can restrict section by section which data should be collected or whether it should be collected synchronously or asynchronously.

Cache age for asynchronous checks

You can change the caching period in seconds for the previously defined asynchronous checks.

Map data to specific host (Piggyback)

Piggyback data can be assigned to a host regardless of the actual source. For example, the SQL information can be separated from the data of the underlying Windows server.

Discovery mode of databases

Settings for the instance search.

Custom instances

In addition to the general settings for accessing your MS SQL Server, you can make your own settings for specific instances here.

Options

The maximum number of parallel SQL Server connections can be set here.

6. Diagnostic options

6.1. Possible errors and error messages

Error message: Failed to gather SQL Server instances

Check whether the TCP/IP connection is configured and working.

7. Files and directories

7.1. On the MSSQL host (Windows)

File path Content

C:\ProgramData\checkmk\agent\plugins\

Directory for storing the agent plug-ins.

C:\ProgramData\checkmk\agent\config\mk-sql.yml

Configuration file for the agent plug-in.

7.2. On the MSSQL host (Linux)

File path Content

/usr/lib/check_mk_agent/plugins/

Directory for storing the agent plug-ins.

/etc/check_mk/mk-sql.yml

Configuration file for the agent plug-in.

7.3. On the Checkmk server

File path Content

~/share/check_mk/agents/windowss/mk-sql.exe

The agent plug-in you have to copy to your Windows hosts, in order to monitor MS SQL there.

~/share/check_mk/agents/linux/mk-sql

The agent plug-in you have to copy to your Linux hosts, in order to monitor MS SQL there.
On this page