influxdata / influxdata/telegraf

Updating SQL Server HADR queries for improving Dashboard capabilities

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

Description

## Feature Request

### Proposal:

Telegraf 1.17 provides new HADR queries and after experienced them with Prometheus and Grafana here some suggestions to make things easier

### Current behavior and Desired behavior:

**sqlServerAvailabilityReplicaStates + sqlServerDatabaseReplicaStates**

sql_instance is using **server:instance** whereas replica_server_name format is **server\instance**. This makes things harder with Prometheus / Grafana if you want to focus only on one replica or SQL Server instance regarding the metric (no way to apply regex replacement as far as I know).

Let's say we want to get operational state of on specific replica. We can use the following promQL query:

`sqlserver_hadr_replica_states_connected_state{sql_instance="server:instance"}`

But according to replica role (PRIMARY or SECONDARY), we will have one or two lines from the result as follows:

```
sqlserver_hadr_replica_states_connected_state{CLUSTER="A",ENVIRONMENT="INT",OS="Win32NT",SERVICE="sql",TYPE="vm",ag_synchronization_health_desc="NOT_HEALTHY",availability_mode_desc="SYNCHRONOUS_COMMIT",connected_state_desc="CONNECTED",failover_mode_desc="MANUAL",group_id="C99B7657-1608-428F-B5B3-EACCD8026F14",group_name="ag-name",host="server",instance="server.domain:9273",job="hosts",measurement_db_type="SQLServer",operational_state_desc="ONLINE",primary_role_allow_connections_desc="ALL",recovery_health_desc="ONLINE",replica_id="0648CA4A-236D-4DE5-972C-2AFF96663117",replica_server_name="server\instance",replica_synchronization_health_desc="HEALTHY",role_desc="PRIMARY",secondary_role_allow_connections_desc="NO",seeding_mode_desc="AUTOMATIC",sql_instance="server:instance"}
sqlserver_hadr_replica_states_connected_state{CLUSTER="A",ENVIRONMENT="INT",OS="Win32NT",SERVICE="sql",TYPE="vm",ag_synchronization_health_desc="NOT_HEALTHY",availability_mode_desc="SYNCHRONOUS_COMMIT",connected_state_desc="DISCONNECTED",failover_mode_desc="MANUAL",group_id="C99B7657-1608-428F-B5B3-EACCD8026F14",group_name="ag-name",host="server",instance="server.domain:9273",job="hosts",last_connect_error_description="xxxx'.",measurement_db_type="SQLServer",primary_role_allow_connections_desc="ALL",replica_id="B1FD5F43-E3DE-46D8-ABD1-CB9B6B092210",replica_server_name="server\instance",replica_synchronization_health_desc="NOT_HEALTHY",role_desc="SECONDARY",secondary_role_allow_connections_desc="NO",seeding_mode_desc="AUTOMATIC",sql_instance="server:instance"}
```

For some reports and panels having two values may be undesirable and we should be able to apply filtering with the same parameter value format on either sql_instance and replica_server_name . In this case, my suggestion is to keep a consistent format for both sql_instance and replica_server_name column values like **server:instance**.

Referring to the SQL Server query it means using:

`REPLACE(ar.replica_server_name,'\',':') AS [replica_server_name] `

**sqlServerAvailabilityReplicaStates**

This query provides helpful metrics about different health status except for the availability group itself. What I mean is we currently get the following metrics ...
* sqlserver_hadr_replica_states_connected_state
* sqlserver_hadr_replica_states_operational_state
* sqlserver_hadr_replica_states_recovery_health
* sqlserver_hadr_replica_states_synchronization_health

... and I believe it could be good to add the following one:

* sqlserver_hadr_replica_states_ag_synchronization_health

Referring to the current code I think adding corresponding column with tinyint value should address the point

`hags.synchronization_health `

### Use case:

Grafana Dashboard for mssql

Contributor guide

Open the contributing guide

Research direction

Start by locating the sqlServerAvailabilityReplicaStates and sqlServerDatabaseReplicaStates query entry points. Check how replica_server_name and the existing replica health fields become metrics, then verify that the naming is consistent and the availability-group synchronization health metric is exposed. Done means both requested query changes are represented in the resulting SQL Server metrics and dashboard filtering can use the consistent instance format.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.