New Azure Data Explorer output plugin for Telegraf enables SQL monitoring at huge scale

Let me share what instigated the need for this plugin before we dive into details. I work with some of the largest enterprise customers of Microsoft with massive SQL environments ranging from thousands to up to a million databases. So you can imagine how painful it would be to monitor such a large fleet of SQL Servers (IaaS and PaaS offerings). Creating a cost-effective solution for the collection of meaningful data to gain the near real-time insights needed to take timely actions before business is impacted, was a daunting task.

Telegraf has a SQL Server input plugin that collects metrics from SQL Server instances using DMVs to collect data. This is by far one of the best way to collect rich, comprehensive, and meaningful data to monitor SQL Servers. The Microsoft SQL Server engineering team is one of the major contributors to this plugin. Customers wanted to combine the power of the plugin along with the platform that is purpose-built for such scenarios as a destination – Azure Data Explorer. Hence, we got multiple requests from large SQL customers to develop the Telegraf output plugin for Azure Data Explorer that enables direct ingestion of SQL metrics. 

What is Azure Data Explorer

Azure Data Explorer is purpose-built for log analytics and IoT analytics scenarios. It is a low latency, high throughput, distributed, columnar store with native capabilities for time series analytics, parsing, full text search, pattern recognition, forecasting, anomaly detection, pre-aggregations using materialized views, automatic indexing and partitioning, predictive autoscale, and many more features. 

What is Telegraf

Telegraf is an opensource, lightweight, minimal memory foot print agent for collecting, processing and writing telemetry data (including logs, metrics, and IoT data). Telegraf supports hundreds of input and output plugins. It is widely used and very well supported by the open source community. 

Brief on Azure Data Explorer plugin

We are very excited to introduce the new Telegraf output plugin for Azure Data Explorer (GA) and Azure Synapse Data Explorer (public preview). This plugin is completely configuration-based and very easy to deploy and monitor. It can be deployed on containerized environments, VMs, or servers that need to be monitored. Just to give you an idea of the complexity that existed before and the simplicity introduced by this new plugin, see the following before/after architecture diagram.

Benefits of the plugin

• Simplifies the SQL monitoring solution - plugin simplifies the overall monitoring of different flavors of SQL Servers including Azure SQL Databases, elastic pools, SQL MI, and SQL Server on VM/on-premises.
• Reduces cost, complexity, and maintenance overheads - provides a direct ingestion path to Azure Data Explorer and hence removes the need for additional components resulting in a reduction of cost and complexity. 
• Low latency ingestion to gain near real-time insights - supports low latency ingestion of ~10 seconds.
• Richness of data – collects much more meaningful data compared to the alternatives.
• Makes the SQL monitoring solution affordable - one of the biggest advantages of Azure Data Explorer is cost effectiveness. Check it out yourself using our pricing calculator and compare it with any of the big data analytics alternatives.

Use cases for the plugin

In this blog post I have mainly focused on the SQL monitoring use case as this is one of the most popular scenarios. However, it can be leveraged for different scenarios such as the collection of telemetry generated by docker containers, kubernetes environments, windows event logs, syslogs, or any of the supported input plugins to ingest logs, metrics, IoT data into Azure Data Explorer and extract insights by building near real-time analytics dashboards.

The following dashboard is an example of insights that can be achieved with this plugin. It's built using Azure Data Explorer dashboards and the Kusko Query Language queries to build it are attached.

To get started, you just need two things

1. Binary - Telegraf is written in Go and compiled into a single binary that can be downloaded from here. 
2. Config - It uses a single config file for all plugins. Attached is a sample Telegraf.conf file with the SQL Server input plugin and the Azure Data Explorer output plugin. You can add any other input and output plugins in the same way.

As this is an open source solution, we welcome contributions from the community. Try it out and do share your feedback.