Zabbix MS SQL server monitoring : 2012/2014 language-independent

Here at Syloé, our services are focused around Open Source software (like Jmeter, Zabbix , Ansible, ..) and as such, we spend most of our time building and managing Linux-based platforms for Zabbix MS SQL server monitoring.

Zabbix MS SQL server monitoring : powerful and highly adaptable monitoring application

Because many customers have mixed environments, we find ourselves confronted with the need to manage other platforms, including MS Windows Server. In this scenario, we are running a performance test (Apache Jmeter) on a Tomcat application with Nginx front-end and MS SQL Server 2014 in the back-office.
We use the Zabbix application as our preferred monitoring tool as it is highly flexible and powerful enough to deal with medium-sized networks. In this scenario it’s just a matter of application of some Zabbix templates  (Nginx, Linux, Java, Tomcat and some templates we built for specific Linux resource monitoring) and we’re off to a quick start.
However, how to deal with the ugly duckling – MS SQL Server 2014? Well, there is a Zabbix template you can download at https://share.zabbix.com/databases/microsoft-sql-server/template-ms-sql-2012, and this works great for MS SQL Server 2012/2014 English or Russian. Thanks to Anton Golubkin for making this publicly available. However, our customer only has the French language pack installed and configured as the system language.

If the system language of the server to be monitored is not English or Russian, then there are a few modifications required to the template, before importing it into Zabbix. If you were to import the template into Zabbix without modifications, and were to subsequently apply it to the target server, you would run into the problem that the specified keys would not be supported by the target Zabbix client. In order to verify whether or not you need modifications, you could run something like this command on the first key encountered in the template:

$ /opt/zabbix/bin/zabbix_get -p 10050 -s <HOST> -k “perf_counter[&quot;\SQLServer:Access Methods\Forwarded Records/sec&quot;,30]”

If you get a proper response back then you’re set. Otherwise, the challenge is to convert the template into something that works for the target system language. The template makes use of MS performance counters, a system that is generally used for all performance counters, including MS SQL Server. The available performance counters (when enabled) can be displayed using the command « typeperf -qx » on the MS CLI. For the subset of Zabbix MS SQL server monitoring, there will be entries such as this one, if the language is French:

“\SQLServer : Méthodes d’accès\Enregistrements transmis/s”

Note that performance counters have to be enabled on the monitored system. Otherwise you might have just ran into an error message (8310) that says: “SQL Server performance counters are disabled.” A solution for that problem can be found by searching on Technet for the string “Enable / Disable Performance Counter Logging”.

Options for Zabbix MS SQL server monitoring

So then, if we want to monitor a foreign-language MS SQL server, the following options seem to be available:

1. Switch system language to English or Russian. This would have an impact far beyond just the zabbix client configuration and would then need to be repeated for all MS SQL servers monitored… Not a great option.
2. Convert the template to the target language. Sounds good, different template for each system language monitored seems manageable.
3. Conversion at the client. Leave the template generic independent of language, map the English template key to the French value at the client with corresponding user parameters.
4. Cry a little. Not generally accepted on the workfloor as a solution.

At this time it is important to understand that the Zabbix agent does not like special characters in its configuration file, so options 2 and 3 would require conversion of the special characters to unicode first. For example, if I introduce a generic counter “MSSQL_Access_Methods_Forwarded_Records” by editing the zabbix_agent.conf file on the target system to include lines such as this:

“PerfCounter = MSSQL_Access_Methods_Forwarded_Records, »\SQLServer : Méthodes d’accès\Enregistrements transmis/s »,60”

I will run into problems – the zabbix agent does not restart if there are special characters present in the key value. After conversion to unicode this problem goes away as zabbix does support unicode. However, there is still the requirement of translation from the source language (English) to the target language (French in my case).
Fortunately, the MS Performance Counters can also be addressed by a numeric value. The benefits of numeric representation is that

1. this eliminates the need for translation English to French w/unicode. Even with Google Translate it can be a bit of a guessing game as this is not popular language translation.
2. there is no need to do anything on the client side
3. I can use one template for all system languages

The numeric values corresponding to the template key values can be identified using the perflib values in the registry. You can find these values using the regedit key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009”, where 009 indicates the English table. If you copy this table and search for the term “SQLServer:Access Methods”, you will find the number “2846” just above it, designating the numeric value for this particular key item. A search for “Forwarded Records/sec” will show the number “2896” associated with it (number directly above the search term).
So then the template value:

<key>perf_counter[&quot;\SQLServer:Access Methods\Forwarded Records/sec&quot;,30]</key>

translates to the following numeric value in the converted template.

<key>perf_counter[&quot;\2846\2896&quot;,30]</key>

Apparently these values do not change between releases of Zabbix MS SQL server monitoring; they have not changed over the last few years at least. Now, instead of having to translate the template values, you just need to perform some creative grep/awk/sed -ing in order to perform the needed replacements in the template and arrive at a template that is language-independent.
I hope this article was helpful to you.
Bob Noorduin

• Nos offres de monitoring
• Tester le sur notre serveur de démonstration
• Notre offre de formation Linux
• Notre offre de formation Administration et supervision avec Zabbix
• zabbix_sender : zabbix_sender