influxdata / influxdata/telegraf
Updating SQL Server HADR queries for improving Dashboard capabilities
- 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
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