PDF

SQL Server Monitoring via Telegraf in NetCrunch

This topic explains how to configure Telegraf to collect Microsoft SQL Server metrics and forward them to a NetCrunch Telemetry Node endpoint using JSON-based telemetry data. It covers SQL Server login setup, connection strings, Telegraf input configuration, and supported metric types.

Overview

Telegraf collects SQL Server metrics through the SQL Server input plugin. This plugin uses Dynamic Management Views provided by SQL Server to gather performance metrics. Collected metrics are sent to a NetCrunch Telemetry Node using the HTTP output plugin.

Supported environments include:

  • SQL Server on premises
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure SQL Elastic Pool
  • Multiple SQL Server instances on one host

How NetCrunch Supports SQL Server Telemetry

NetCrunch receives incoming SQL Server metrics through a Telemetry Node endpoint. Telemetry Nodes accept JSON data and anchor incoming values as counters or alert statuses.

The endpoint, its URL shape and how it is authorized are described once in Monitoring with Telegraf. Everything below assumes a Telemetry Node already exists — see Telemetry Node.

Data Flow

  1. SQL Server is queried through Dynamic Management Views (DMVs).
  2. Telegraf collects returned metrics.
  3. Telegraf sends JSON data to the NetCrunch Telemetry Node endpoint using HTTP POST.
  4. NetCrunch stores values as counters or status objects.

SQL Server Configuration

Create Monitoring Login

Create a dedicated login with permissions required for Telegraf.

SQL Server on premises


USE master;
GO
CREATE LOGIN [telegraf] WITH PASSWORD = N'StrongPassword123!';
GO
GRANT VIEW SERVER STATE TO [telegraf];
GO
GRANT VIEW ANY DEFINITION TO [telegraf];
GO

Azure SQL Database


CREATE USER [telegraf] WITH PASSWORD = N'StrongPassword123!';
GO
GRANT VIEW DATABASE STATE TO [telegraf];
GO

Windows Authentication on premises

First, enable service SID:


sc.exe sidtype "telegraf" unrestricted

Then create a login:


USE master;
GO
CREATE LOGIN [NT SERVICE\telegraf] FROM WINDOWS;
GO
GRANT VIEW SERVER STATE TO [NT SERVICE\telegraf];
GO
GRANT VIEW ANY DEFINITION TO [NT SERVICE\telegraf];
GO

Telegraf Configuration

Telegraf configuration file:

  • Linux: /etc/telegraf/telegraf.conf
  • Windows: C:\Program Files\Telegraf\telegraf.conf

Basic Configuration


[agent]
  interval = "30s"
  flush_interval = "30s"
  metric_buffer_limit = 10000
  debug = false
  quiet = false


[[inputs.sqlserver]]
  servers = [
    "Server=127.0.0.1;Port=1433;User Id=telegraf;Password=StrongPassword123!;app name=telegraf;"
  ]
  database_type = "SQLServer"


[[outputs.http]]
  url = "https://gw.netcrunch.io/tm/v1/SRV-001@sensor01@node100/update"
  method = "POST"
  data_format = "json"
  content_encoding = "identity"
  [outputs.http.headers]
    Content-Type = "application/json"

Configuration Parameters

Agent Section

  • interval controls how frequently metrics are collected
  • flush_interval controls how often payloads are submitted to outputs
  • metric_buffer_limit defines the limit for stored unsent data
  • debug enables verbose logging
  • quiet suppresses non-error output

SQL Server Input

  • servers contains connection strings
  • database_type selects SQL environment type
  • query_timeout controls query execution timeout
  • include_query limits query set to selected names
  • exclude_query skips selected queries

HTTP Output

  • url points to the Telemetry Node endpoint
  • method must be POST
  • data_format must be json
  • headers define HTTP content headers

Monitoring Multiple Instances

To monitor multiple SQL Server instances, configure multiple connection strings.


[agent]
  interval = "30s"
  flush_interval = "30s"
  debug = false


[[inputs.sqlserver]]
  servers = [
    "Server=127.0.0.1,1433;User Id=telegraf;Password=StrongPassword123!;",
    "Server=127.0.0.1,1434;User Id=telegraf;Password=StrongPassword123!;"
  ]
  database_type = "SQLServer"


[[outputs.http]]
  url = "https://gw.netcrunch.io/tm/v1/SRV-001@sensor01@node100/update"
  method = "POST"
  data_format = "json"
  content_encoding = "identity"
  [outputs.http.headers]
    Content-Type = "application/json"

This configuration monitors two instances on different ports.

Connection String Format

Basic

Server=<host>;Port=<port>;User Id=<user>;Password=<password>;app name=telegraf;

Named Instance

Server=<host>\<instance>;User Id=<user>;Password=<password>;app name=telegraf;

Windows Authentication

Server=<host>;Port=<port>;app name=telegraf;

Connection Timeout

Server=<host>;Port=<port>;User Id=<user>;Password=<password>;app name=telegraf;dial timeout=30;

TLS or SSL Connection

Server=<host>;Port=<port>;User Id=<user>;Password=<password>;encrypt=true;certificate=<cert>;hostNameInCertificate=<fqdn>;

Collected Metrics

The SQL Server plugin collects performance and status metrics from DMVs, depending on the database_type.

SQLServer On Premises

Collected metrics include:

  • Performance counters such as transactions per second, buffer cache hit ratio, log metrics, and user connections
  • Wait statistics including wait time, waiting tasks, and resource wait details
  • Database I/O latency and throughput
  • Memory clerk usage
  • Scheduler statistics
  • Server properties such as CPU count, memory, uptime, version, and database states
  • Volume space metrics
  • CPU usage metrics
  • Last backup details

AzureSQLDB

Collected metrics include:

  • Resource utilization
  • Governance limits
  • Database I O statistics
  • Wait statistics
  • Memory clerks
  • Performance counters

AzureSQLManagedInstance

Collected metrics include:

  • Instance resource statistics
  • Resource governance settings
  • Database I O
  • Wait statistics
  • Memory clerks
  • Performance counters

AzureSQLPool (Elastic Pool)

Collected metrics include:

  • Elastic pool resource usage
  • Database I O per database
  • Wait statistics
  • Memory clerks
  • Performance counters

Advanced Configuration

Selective Query Collection

Include specific queries:


[[inputs.sqlserver]]
  servers = ["Server=127.0.0.1;User Id=telegraf;Password=pass;"]
  database_type = "SQLServer"
  include_query = [
    "SQLServerPerformanceCounters",
    "SQLServerDatabaseIO",
    "SQLServerWaitStatsCategorized"
  ]

Exclude specific queries:


[[inputs.sqlserver]]
  servers = ["Server=127.0.0.1;User Id=telegraf;Password=pass;"]
  database_type = "SQLServer"
  exclude_query = [
    "SQLServerAvailabilityReplicaStates",
    "SQLServerDatabaseReplicaStates"
  ]

Azure Active Directory Authentication


[[inputs.sqlserver]]
  servers = [
    "Server=myserver.database.windows.net;Database=mydb;app name=telegraf;"
  ]
  database_type = "AzureSQLDB"
  auth_method = "AAD"
  client_id = "<managed-identity-client-id>"

Health Metric


[[inputs.sqlserver]]
  servers = ["Server=127.0.0.1;User Id=telegraf;Password=pass;"]
  database_type = "SQLServer"
  health_metric = true

Use Cases

SQL Server on Premises

Monitor classic SQL Server instances without requiring SNMP or WMI.

Multiple Instance Servers

Collect metrics from several SQL Server instances using individual connection strings.

Azure SQL Monitoring

Track performance metrics for Azure SQL Database and Azure SQL Managed Instance.

Hybrid SQL Environments

Monitor mixed environments with on-premises and Azure SQL resources in one configuration.

High Availability Monitoring

Monitor availability groups and replica states.

Summary

Telegraf supports SQL Server performance monitoring through DMVs and forwards metrics to NetCrunch Telemetry Nodes. It supports on-premises SQL Server, Azure SQL, managed instances, and elastic pools. Telegraf can monitor multiple instances, supports both SQL and Windows authentication, allows selective query collection, and supports authentication via Azure Active Directory.

azure sqldatabasedmvelastic poolmanaged instancemssqlpushsql servertelegraftelemetry node