Skip to main content

Microsoft SQL Server

Pack Assets​

Templates​

The Centreon Monitoring Connector Microsoft SQL Server brings a host template:

  • App-DB-MSSQL-custom

It brings the following service templates:

Service AliasService TemplateService DescriptionDefault
Backup-AgeApp-DB-MSSQL-Backup-AgeCheck database backups of the server
Blocked-ProcessesApp-DB-MSSQL-Blocked-ProcessesCheck blocked processes on the server.X
Cache-HitratioApp-DB-MSSQL-Cache-HitratioCheck the "Data Buffer Cache Hit Ratio" of the server. No alerts by default
Connected-UsersApp-DB-MSSQL-Connected-UsersCheck number of connected users on the databaseX
Connection-TimeApp-DB-MSSQL-Connection-TimeCheck the connection time to the server. This time is given in secondsX
Databases-SizeApp-DB-MSSQL-Databases-SizeCheck database data and logs filesX
DeadlocksApp-DB-MSSQL-DeadlocksCheck deadlocks per second of the serverX
Failed-JobsApp-DB-MSSQL-Failed-JobsCheck MSSQL failed jobsX
Locks-WaitsApp-DB-MSSQL-Locks-WaitsCheck locks-waits per second of the server
Page-Life-ExpectancyApp-DB-MSSQL-Page-Life-ExpectancyCheck the "Page Life Expectancy" of the server. No alerts by default
Sql-StatementApp-DB-MSSQL-Sql-StatementCheck allowing to execute a custom SQL query returning a number
Sql-Statement-StringApp-DB-MSSQL-Sql-Statement-StringCheck allowing to execute a custom SQL query returning a string
TransactionsApp-DB-MSSQL-TransactionsCheck transactions per second of the server. No alerts by defaultX

Collected metrics & status​

Metric NameUnit
last-duration
last-execution

Prerequisites​

Monitoring user​

To use this probe, the configuration of a monitoring user with specific privileges is required. The serveradmin role can be used for testing purposes but should never be used in production because of the security risk involved.

Birk Bohne, who is a valuable contributor of the check_mssql_health probe, wrote a script to assign a less-privileged user. His script can be found under the Preparation of the database section of the following documentation.

We recommend you use a domain user to better manage its properties and privileges.

Dependencies​

These packages are required: freetds perl-DBD-Sybase unixODBC

Freetds configuration​

The default version used by freetds is 4.2. To guarantee optimal operations and security, edit the freetds.conf file, uncomment this line version = 4.2and replace 4.2 with 8.0. The 8.0 version is the minimal supported version.

The configuration file is located under different paths depending on your operating system:

  • RedHat-like: /etc/freetds.conf
  • Debian 11: /etc/freetds/freetds.conf

Setup​

Monitoring Pack​

If the platform uses an online license, you can skip the package installation instruction below as it is not required to have the pack displayed within the Configuration > Monitoring Connectors Manager menu. If the platform uses an offline license, install the package on the central server with the command corresponding to the operating system's package manager:

dnf install centreon-pack-applications-databases-mssql

Whatever the license type (online or offline), install the Microsoft SQL Server Pack through the Configuration > Monitoring Connectors Manager menu.

Plugin​

Since Centreon 22.04, you can benefit from the 'Automatic plugin installation' feature. When this feature is enabled, you can skip the installation part below.

You still have to manually install the plugin on the poller(s) when:

  • Automatic plugin installation is turned off
  • You want to run a discovery job from a poller that doesn't monitor any resource of this kind yet

More information in the Installing the plugin section.

Use the commands below according to your operating system's package manager:

dnf install centreon-plugin-Applications-Databases-Mssql

Configuration​

Host​

  • Log into Centreon and add a new host through Configuration > Hosts.
  • Fill the Name, Alias & IP Address/DNS fields according to your SQL Server database server settings.
  • Apply the App-DB-MSSQL-custom template to the host.
  • Once the template is applied, fill in the corresponding macros. Some macros are mandatory.
MandatoryMacroDescription
EXTRAOPTIONSAny extra option you may want to add to every command line (eg. a --verbose flag)
XMSSQLPASSWORDMonitoring user password (Default: 'PASSWORD')
XMSSQLPORTMSSQL instance listening port (Default: '1433')
XMSSQLUSERNAMEMonitoring user login string (Default: 'USERNAME')

How to check in the CLI that the configuration is OK and what are the main options for?​

Once the plugin is installed, log into your Centreon poller's CLI using the centreon-engine user account (su - centreon-engine) and test the plugin by running the following command:

/usr/lib/centreon/plugins//centreon_mssql.pl \
    --plugin database::mssql::plugin \
    --hostname 10.0.0.1 \
    --port 1433 \
    --username 'USERNAME' \
    --password 'PASSWORD'  \
    --mode=connected-users \
    --warning-connected-user='' \
    --critical-connected-user='' \
    --verbose \
    --use-new-perfdata

The expected command output is shown below:

OK: 20 connected user(s) | 'mssql.users.connected.count'=20;;;0;

All available options for a given mode can be displayed by adding the --help parameter to the command:

/usr/lib/centreon/plugins//centreon_mssql.pl \
    --plugin database::mssql::plugin \
    --hostname 10.0.0.1 \
    --help

All available modes can be displayed by adding the --list-mode parameter to the command:

/usr/lib/centreon/plugins//centreon_mssql.pl \
    --plugin database::mssql::plugin \
    --list-mode

Troubleshooting​

Please find the troubleshooting documentation for Centreon Plugins typical issues.