Monitoring Microsoft SQL Server

1. Introduction

Checkmk enables you to comprehensively monitor Microsoft SQL Servers (abbreviated to 'MSSQL' in the following). A complete list of all available check plug-ins can be found in our Catalog of check plug-ins.

To be able to monitor the databases, you will also need the mk-sql agent plug-in 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 on either the Checkmk or the database server.

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

2. Setting up an account for monitoring (Windows)

The agent plug-in requires access to your MSSQL instances. As a rule, it is 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 use only the Windows Authentication mode or the mixed mode SQL Server and Windows Authentication mode.

2.1. Using the LocalSystem account

The Checkmk agent is executed on Windows systems under the LocalSystem account (NT AUTHORITY\SYSTEM). If you can also use this LocalSystem account for monitoring MSSQL, you will only need to ensure that this account (login) has as a minimum the permissions for the following securable elements (Securables):

View server state
Connect SQL
Connect any database (optional)

The permission Connect any database (optional) is optional, but is required in almost all MSSQL monitoring scenarios.

2.2. Using a dedicated account

There are good reasons for not running MSSQL monitoring via the Windows LocalSystem account. Potential reasons include security requirements in the organization or simply the desire to have dedicated and clearly named accounts, the purpose of which is already clear from the name.

The plug-in of course also works with such accounts (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 LocalSystem account, this requirement should have already been met, but it should not go unmentioned here.

If there is no corresponding user on your MSSQL server or in your instance, you can create one on your Windows system via the Microsoft SQL Server Management Studio (with any username):

“Selecting the ‘New Login...’ option in MSSQL.”

This login requires the following permissions:

Connect SQL
Connect any database
View server state

Grant these permissions in the Object Explorer via Security > Logins. Open the properties of the account (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 after that:

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

2.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 this description, we assume that the agent for Windows monitoring has already been installed on the host.

Creating a configuration file

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

When using the LocalSystem account, this is all that is required:

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

---
mssql:
  main:
    authentication:
      username: ''
      type: integrated

If you use a dedicated account for monitoring, in its place, 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 different usernames and passwords on the host, add the details of the 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

Installing the agent plug-in

The mk-sql.exe plug-in can be found in Checkmk Raw under Setup > Agents > Windows and in the commercial editions under Setup > Agents > Windows, Linux, Solaris, AIX > Related > Windows files:

Selection of the agent plug-in in Checkmk.

Download the mk-sql.exe file and save it on the Windows host in the directory C:\ProgramData\checkmk\agent\plugins\.

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:

2.4. Extended configuration

Asynchronous execution of the agent plug-in

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

To run the agent plug-in asynchronously under Windows, edit the configuration of the plug-in and add a cache_age line to the main section:

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

mssql:
  main:
    authentication:
      username: myuser
      password: mypassword
      type: sql_server
    cache_age: 300

For detailed instructions on the asynchronous execution of agent plug-ins on a Windows host, see the article that explains the Windows agent.

3. Setting up an account for monitoring (Linux)

3.1. Creating a user for the monitoring

You will need a user who can connect to your MSSQL instance. This user requires at least the following permissions:

Connect SQL
Connect any database
View server state

Apart from these permissions, it is up to you or your organization how such a user can or must be created.

You can use a local user for testing purposes. If there is no corresponding user on your MSSQL server or in your instance, you can create one on your Linux system via the command line:

In the following example, this is being done for the user checkmk:

user@host:~$ sudo adduser --system checkmk --ingroup sudo

Follow the prompts to assign a password, etc.

Then:

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

3.2. Manual setup of the connection

If you are using 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 this description, we will assume that the agent for Linux monitoring has already been installed on the host.

Create a configuration file

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

/etc/check_mk/mk-sql.yml

---
mssql:
  main:
    authentication:
      username: checkmk
      password: checkmkPW
      type: sql_server

/etc/check_mk/mk-sql.yml

---
mssql:
  main:
    authentication:
      username: checkmk
      password: checkmkPW
      type: sql_server
    instances:
    - sid: myInstance1
      auth:
        username: myUser1
        password: 123456
        type: sql_server
    - sid: myInstance2
      auth:
        username: myUser2
        password: 987654
        type: sql_server

Installing the agent plug-in

The mk-sql plug-in can be found in Checkmk Raw under Setup > Agents > Linux and in the commercial editions under Setup > Agents > Windows, Linux, Solaris, AIX > Related > Linux, Solaris, AIX files:

Download the mk-sql file and place it on the Linux host in the /usr/lib/check_mk_agent/plugins/ directory.

Setting up 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:

3.3. Extended configuration

Asynchronous execution of the agent plug-in

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

To execute the agent plug-in asynchronously under Linux, edit the configuration of the plug-in and add a cache_age line to the main section:

/etc/check_mk/mk-sql.yml

mssql:
  main:
    authentication:
      username: myuser
      password: mypassword
      type: sql_server
    cache_age: 300

For detailed instructions on how to run agent plug-ins asynchronously on a Linux host, see the article on the Linux agent.

4. Further configuration options in Checkmk

4.1. Configuring threshold values

Monitoring connections

You can specify your own threshold values 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 threshold values in no time at all using a rule from the MSSQL Connections rule set.

“Setting the upper threshold values for active connections.”

Monitoring file sizes

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

5. Configuration via the Agent Bakery

The setup has been very simplified in the commercial editions using the Agent Bakery, as syntax errors in the configuration files are avoided and customizations to suit changing environments can be made easily. The main difference to a manual installation is that you only have to work on the MSSQL host via 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. There search for mssql in the search field:

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

5.1. Setting up the agent rule (Windows)

Depending on how the agent plug-in is allowed to log in to your MSSQL server (see Setting up an account for monitoring), select the appropriate option under Authentication. If you are using the LocalSystem account (NT AUTHORITY\SYSTEM), leave the selection at Local Integrated Authentication (Windows).

“Possible settings for the MSSQL server in the Agent Bakery.”

If you use SQL Server Authentication instead, select the SQL database user credentials option, and also enter the Login name (see Using a dedicated account) in the User field and add the corresponding Password.

If you are running multiple MSSQL instances with different usernames 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 Agent Bakery rule.”

5.2. Setting up the agent rule (Linux)

Under Authentication select the SQL database user credentials option.

Then enter the Login name in the User field and add the corresponding Password. For the Connection, add the Host name.

5.3. 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 typical case scenario. However, if your organization has a more complex MSSQL environment, there are further options, both for monitoring the database on the same host and for monitoring it on a remote host. These options are basically the same for both connection types.

Option	Description
Connection	If you need more specific connection data for the general connection to the MSSQL server, you can specify this here.
Data to collect (Sections)	Here you can restrict by section which data should be collected and whether it should be collected synchronously or asynchronously.
Cache age for asynchronous checks	You can change the cache 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 database instances	Settings for the search in the instance.
Custom instances	In addition to the general settings for accessing your MSSQL server, you can also define your own settings for specific instances here.
Options	The maximum number of parallel SQL server connections can be set here.

Option

Description

Connection

If you need more specific connection data for the general connection to the MSSQL server, you can specify this here.

Data to collect (Sections)

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

Cache age for asynchronous checks

You can change the cache 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 database instances

Settings for the search in the instance.

Custom instances

In addition to the general settings for accessing your MSSQL server, you can also define 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 has been configured and is working properly.

7. Files and directories

7.1. On the MSSQL host (Windows)

File path Description

File path	Description
`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.

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 Description

File path	Description
`/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.

/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 Description

File path	Description
`~/share/check_mk/agents/windows/mk-sql.exe`	The agent plug-in to be copied to your Windows hosts in order to monitor MSSQL there.
`~/share/check_mk/agents/linux/mk-sql`	The agent plug-in to be copied to your Linux hosts to monitor MSSQL there.

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

The agent plug-in to be copied to your Windows hosts in order to monitor MSSQL there.

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

The agent plug-in to be copied to your Linux hosts to monitor MSSQL there.

On this page

1. Introduction
2. Setting up an account for monitoring (Windows)
3. Setting up an account for monitoring (Linux)
4. Further configuration options in Checkmk
- 4.1. Configuring threshold values
  - Monitoring connections
  - Monitoring file sizes
5. Configuration via the Agent Bakery
6. Diagnostic options
- 6.1. Possible errors and error messages
  - Error message: Failed to gather SQL Server instances
7. Files and directories