1. Preface
The old plugin (mssql.vbs) for monitoring Microsoft SQL Server has been replaced.
If your SQL Server network configuration allows access via TCP/IP, we recommend using the new plugin.
We explain how to set up and use the new plugin in the article Monitoring Microsoft SQL Server
mssql.vbs will be removed with version 2.4.0 of Checkmk (see work #15844).
2. Introduction
Checkmk allows you to comprehensively monitor Microsoft SQL Server. A complete list of the monitoring options can be found in our in our Catalog of check plug-ins. Among other things, Checkmk can monitor the following values:
To be able to monitor the databases, you only need the agent plug-in on the database server in addition to the Checkmk agent. No additional software is required on either the Checkmk or the database server.
The setup for Windows hosts is described below. Further below we will go into setting up via the Agent Bakery.
3. Setting up monitoring
3.1. Using the system user
If you can use the system user (NT AUTHORITY\SYSTEM) for monitoring, be aware that as a minimum this login requires the following Securables authorizations:
Connect any database
View Server State
Connect SQL
Depending on which of your instance’s databases you want to monitor and also depending on which server roles and mappings the system user (NT AUTHORITY\SYSTEM) already has, it may also be necessary to grant authorization for Connect Any Database.
3.2. Digression: Setting up a new user
If it is absolutely necessary that the system user is not used in your organization, you must create a suitable database monitoring user on the instances to be monitored. The basic requirement in MSSQL is that the Server authentication is set to SQL Server and Windows Authentication mode. If you cannot use the Windows system user, this requirement should have already been met, but it should not go unmentioned.
Now log in to MSSQL with a user with sufficient access authorizations in MSSQL, and create a new database user via the Microsoft SQL Server Management Studio:
This newly created user requires the above-mentioned authorization. Go to the Object Explorer, open Security > Logins and then the properties for the login that you have configured for the agent plug-in. Under Securables you will find the Connect any database explicit authorization, which you must grant.
Alternatively, you can also create the user and the authorizations via the command line.
In the following example, this has been done for the user checkmk:
mssql> USE master;
CREATE USER checkmk WITH PASSWORD = 'MyPassword123';
GOand then:
mssql> USE master;
GRANT CONNECT ANY DATABASE TO checkmk;
GOCreating a configuration file
On the Windows host, create a file in the directory specified below named mssql.ini:
[client]
user=checkmk
password=MyPassword123If you are running multiple MSSQL instances with different user names and passwords on the host, simply create an ini file for each instance according to the naming scheme mssql_instance-ID.ini.
3.3. Installing the agent plug-in
The Windows agent is supplied with a whole range of plug-ins as standard. After installing the agent you will therefore also find the plug-in for monitoring MSSQL on your host. To use this, simply copy it to the correct directory:
PS> copy "c:\Program Files (x86)\checkmk\service\plugins\mssql.vbs" c:\ProgramData\checkmk\agent\plugins\3.4. Setting up services
Now that you have installed and configured the 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:
4. Extended configuration
4.1. 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 run the plug-in asynchronously under Windows, customize the agent’s configuration and add the following entry to the execution section under plugins with the following entry:
plugins:
execution:
- pattern: mssql.vbs
cache_age: 300
async: yesDetailed instructions on the asynchronous execution of plug-ins on a Windows host can be found in the article on the Windows agent.
4.2. 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 appropriate threshold values in no time at all using a rule from the MSSQL Connections rule set.
Monitoring file sizes
You can also define threshold values for the size of individual files in MSSQL. To do this, use the MSSQL datafile sizes rule set.
5. Configuration using the Agent Bakery
Setup has been greatly simplified in the commercial editions through the use of the Agent Bakery.
This significantly simplifies the setup process, as syntax errors in the configuration files are avoided and adaptations to changing environments can be easily made.
The main difference to a manual installation is that you only have 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 setup the database user on the MSSQL host and 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:
In the new rule in the Microsoft SQL Server (Windows) section, ideally select the System Authentication option for a simple and problem-free connection.
If you instead opt for the Database User Credentials option, you must also enter the User ID and Password for the desired database user (for example, the user that you created before).
6. Diagnostic options
If, for example, unexpected behaviors or problems occur when setting up the monitoring, it is advisable to check directly on an affected host. As the plug-in for monitoring MSSQL is a shell or Visual Basic script, it can easily be executed without the agent. The shell or command line only needs to be made aware of the relevant configuration directory beforehand.
To be able to run the plug-in manually, first open a command line with admin rights,
and set the environment variable MK_CONFDIR in this command line.
This is required so that the plug-in can find the configuration files.
PS> set MK_CONFDIR=c:\ProgramData\checkmk\agent\configNote: Here too, the environment variable is not set permanently, but is only valid for as long as this command line is open.
During the actual execution of the plug-in, it is recommended to redirect the output to the command line.
Windows provides the cscript tool for this purpose.
PS> cscript c:\ProgramData\checkmk\agent\plugins\mssql.vbs6.1. Possible errors and error messages
Failed to connect to database
There are three different drivers with whose help Checkmk alternatively attempts to connect to the MSSQL database: msoledbsql, sqloledb and sqlncli11.
By default, all three drivers are usually available and are tested one after the other when establishing a connection. Under some circumstances, this successive processing may lead to error messages. You may then have to remove one or even two of the named drivers from the syntax of the call.
mssql.vbs: "no backup found"
If a plug-in reports "no backup found", you should first manually check whether a backup is available. If a backup exists and the problem still persists, this may be due to the naming conventions on the MSSQL side.
Checkmk always interprets host names in lower case letters, but if these are transmitted by the host in upper case letters during the backups, for example, communication problems will arise.
You can then check the value of the serverproperty('collation') on the MSSQL side.
If, for example, SQL_Latin1_General_CP1_CS_AS is set here, then CS stands for 'Case Sensitive'.
The plug-in cannot then convert the host name and problems may occur due to the spelling.
Changing to SQL_Latin1_General_CP1_CI_AS, i.e. to CI for 'Case Insensitive' should solve this problem.
Alternatively, you could also change the spelling of the original name of the MSSQL server. This is however simply not possible in every company and for every environment.
7. Files and directories
7.1. On the MSSQL host
| File path | Content |
|---|---|
|
Plug-in directory |
|
Configuration directory |
7.2. On the Checkmk server
| File path | Content |
|---|---|
|
The plug-in that fetches the data from the MSSQL host. |