Come on, go, that we hadn't given to Centreon for a while; today a classic, But well. If we have any Microsoft SQL Server services in our organization, We can't let it go, apart from your availability, we must know its performance!

So what I said, today it's time to break down our SQL Server servers and find out how they behave, In previous posts we have already seen how a Windows PC, Where do you look at a base (CPU, RAM, Swap, Disk usage, Net, Uptime…), and apart from other minimums such as services What it offers, harbours, Performance Counters, Loading the discs…

Today we will use again the wonderful 'Centreon-Plugins'’ that are good for almost everything, we saw In this post How to install them, So we started! At the end of the post we will be able to know:

• Backup age.
• Blocked processes.
• Blocks.
• Blocking/Waiting.
• Page Life Expectancy.
• Database Size.
• Log Size.
• Cache hit rate.
• Failed works.
• Connected Users.
• Connection Time.
• Transactions.

GOOD, What we need to get started? Well, having Centreon-Plugins installed as we have said 🙂 and then in Centreon will not be, rather than creating the commands that we need the Centreon to execute to get that data (Since “Configuration” > “Commands” > “Check” > “Add”. ¡Ah! and obviously we also need a user with read permissions in SQL, whether local or domain user. Well, nothing, I leave this sucked, we just have to copy the Commands that interest us:

• centreon_plugins.pl_sql_backup-age

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=backup-age

Note: Si necesitas indicar la instancia ya que no es la predeterminada, a cada comando deberás añadir:

--server=$HOSTADDRESS$NOMBRE_INSTANCIA

• centreon_plugins.pl_sql_blocked-processes

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=blocked-processes --warning-blocked-processes=$ARG1$ --critical-blocked-processes=$ARG2$

• centreon_plugins.pl_sql_cache-hitratio

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=cache-hitratio --warning=$ARG1$ --critical=$ARG2$

• centreon_plugins.pl_sql_connected-users

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=connected-users --warning=$ARG1$ --critical=$ARG2$

• centreon_plugins.pl_sql_connection-time

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=connection-time --warning=$ARG1$ --critical=$ARG2$

• centreon_plugins.pl_sql_databases-size

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=databases-size

• centreon_plugins.pl_sql_dead-locks

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=dead-locks --warning=$ARG 1$ --critical=$ARG 2$

• centreon_plugins.pl_sql_failed-jobs

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=failed-jobs --warning=$ARG 1$ --critical=$ARG 2$

• centreon_plugins.pl_sql_locks-waits

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=locks-waits --warning=$ARG 1$ --critical=$ARG 2$

• centreon_plugins.pl_sql_logs-size

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=logs-size

• centreon_plugins.pl_sql_page-life-expectancy

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=page-life-expectancy

• centreon_plugins.pl_sql_transactions

/usr/bin/perl /usr/lib/centreon/plugins/centreon-plugins/centreon_plugins.pl --hostname=$HOSTADDRESS$ --username='DOMINIO\\USUARIO' --password='CONTRASEÑA' --port=1433 --plugin=database::mssql::plugin --mode=transactions

Once the Commands have been created, we have to create the Services, in this case one for each Command, you already know, we create them from “Configuration” > “Services” > “Services by host”. And at the end remember to save the changes by exporting the Centreon configuration.

And nothing, after a little while that we dedicate to it, we will have our SQL Server perfectly monitored, in addition to the basic services that we mentioned at the beginning such as CPU, RAM, Swap, Disk usage, Loading the discs, Network usage, services, Processes, Certificates, harbours, SQL Server performance counters that there are some interesting ones…. And now we also know the Age of the backup of each database, any blocked Processes that may exist, Blockades, Locks/Wait, Life Expectancy Page, the size of each database, Size of transaction logs, Cache Hit Rate, Failed Jobs, Connected Users, Connection Time, and even the Transactions that our server suffers! You should know that queries could also be made and their result monitored, But we'll see that another day 😉

¡Ah! And if it's a virtual machine, don't forget to Your Virtualization Metrics!

Well, we'll leave it for now, if it has been of benefit to someone, I consider myself served. As usual, A warm hug, Thank you for sharing these things on social media, this is what we have had in the S. XXI, Like things… Anyway… Kisses!