Custom Sql Metrics Gathering With Telegraf (Part 1)

I love using telegraf and the sql server plugin that Tracy and Mark have championed. It’s an integral part of the Collecting Performance Metrics presentation that Tracy has given to about a billion people so far. It does have a few unfortunate limitations though since it doesn’t support SQL Server 2005. Which is sad because I still have a few instances which I cannot yet retire.

So let’s fix that.

One of the nice things about telegraf is it’s plugin architecture. There’s usually a plugin for anything you want. In this case we’re going to take advantage of the exec plugin to gather additional metrics to load into our influx database.

The configuration of the plugin is rather simple. Here’s what it looks like with line breaks added for readability

[[inputs.exec]]
commands = [
'"C:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD.EXE"
-H localhost -U telegrafuser -P telegrafpassword -d DBATools -h -1 -y 0
-Q "SET DEADLOCK_PRIORITY LOW;
SET NOCOUNT ON;
exec [influx].[BackupMetrics]"',
]
data_format="influx"
interval="3000s"

The first part [commands] is an array of commands to run. In this case it’s going to run SQLCMD and SQLCMD will execute a stored procedure in our maintenance database.

The second part [data_format] tells telegraf was format the data will come back as. In this case it’ll be influx line protocol format.

The last part [interval] is the frequency to run the command. This example is backup metrics. It can be a little taxing if you don’t properly prune your backup history so we only run this every 5 minutes. That’s frequent enough since log backups occur at 10 minute intervals.

That’s all that’s required to configure custom metrics. In the next part we’ll get into the details of that stored procedure since it’s the true heart of this design.