influxdata / influxdata/telegraf

SQL Server Input Plugin - Add availability group configuration and state metrics

Open
#6,834 2 comments 0 reactions 0 assignees View on GitHub
area/sqlserver feature request platform/windows
Dominant language
Go
Stars
17.8k
Forks
5.8k
Avg merge
1d 20h
Merged PRs (30d)
161

Description

## Feature Request

To monitor the health / configuration of SQL Server availability group. It would be good to add TSQL some queries where results would be collected by the SQL Server Input Plugin.

### Proposal:

#### Underlying Windows Failover Cluster info

`SELECT
member_name,
member_type_desc AS member_type,
member_state_desc AS member_state,
number_of_quorum_votes
FROM sys.dm_hadr_cluster_members;`

#### AG level info => Recovery health + synchronization state

`SELECT
g.name as ag_name,
rgs.primary_replica,
rgs.primary_recovery_health_desc AS [primary_recovery_health],
rgs.synchronization_health_desc AS [synchronization_health]
FROM sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g ON rgs.group_id = g.group_id;`

#### AG replica level info => synchronization state + operational state + recovery health

`SELECT
g.name as ag_name,
LEFT(r.replica_server_name, CHARINDEX('\', r.replica_server_name) - 1) AS replica_cluster_name,
r.replica_server_name,
rs.is_local,
role_desc AS [role],
rs.operational_state_desc AS [operational_state],
rs.connected_state_desc AS [connected_state],
rs.recovery_health_desc AS [recovery_health],
rs.synchronization_health_desc AS [synchronization_health]
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = r.group_id`

#### AG database level info => synchronization state + operational state + database state recovery health

`SELECT
g.name as ag_name,
LEFT(r.replica_server_name, CHARINDEX('\', r.replica_server_name) - 1) AS replica_cluster_name,
r.replica_server_name,
DB_NAME(drs.database_id) AS [database_name],
drs.is_local,
drs.is_primary_replica,
drs.synchronization_health_desc AS [synchronization_health],
drs.synchronization_state_desc AS [synchronization_state],
drs.database_state_desc AS [database_state],
drs.is_suspended,
drs.suspend_reason_desc AS [suspend_reason],
drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS r
ON r.replica_id = drs.replica_id
JOIN sys.availability_groups AS g
ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC, drs.database_id `

### Current behavior:

Currently, no metrics about AG config , state or health

### Desired behavior:

Having these metrics added

### Use case:

Currently, availability group topology and configuration / status can't be included via telegraf and other tools or extra work is required. Having these metrics available may help to get a better picture of availability groups and their states coupling to existing performance counters available with query_version = 2

Contributor guide

Open the contributing guide

Research direction

Start by locating the SQL Server Input Plugin and reviewing how its existing query_version = 2 metrics are collected and named. Use the four proposed availability-group queries as the scope, then verify that cluster, AG, replica, and database state metrics are collected and exposed consistently.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases, observability
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.