Grafana Dashboard + collector mssql_availability_groups

[arindamg94]

Hi,

I have a little trouble integrating the docker-setup with Grafana Dashboard. I am using this repo, and managed to get things up and running.

But I was wondering if anyone has an updated Grafana Dashboard as the one provided in the documentation example is not fitted (up to date using Prometheus). Also, does anyone know why mssql_availability_groups (the one provided in the documentation, Monitor Always On Availability Groups) is not working.. I had to use GTP to change it... Which mssql-servers are it supported for?

All help is appreciated to become better in this field :D

My whole setup is below

---

docker-compose.yml

services:
  prometheus:
    image: prom/prometheus
    container_name: prometheus-mssql
    ports: 
      - 9093:9090
    volumes:
      - /var/lib/prometheus-mssql/:/prometheus
      - /var/log/prometheus-mssql/:/var/log/prometheus/
      - /etc/prometheus/:/etc/prometheus/
      - /usr/share/zoneinfo/Europe/Oslo:/etc/localtime:ro
      - /etc/timezone:/etc/timezone:ro
    command:
      - '--config.file=/etc/prometheus/prometheus-mssql.yml'
      - '--storage.tsdb.path=/prometheus'
      - '--storage.tsdb.retention.time=1y'
      - '--web.console.libraries=/usr/share/prometheus/console_libraries'
      - '--web.console.templates=/usr/share/prometheus/consoles'
      - '--web.listen-address=0.0.0.0:9090'
      - '--web.config.file=/etc/prometheus/web.yml'
      - '--web.enable-admin-api'
      - '--web.enable-lifecycle'
    restart: unless-stopped
    networks:
      - monitoring

  mssql_exporter-1-it-sqltest02.domain.no:
    image: burningalchemist/sql_exporter:latest
    container_name: mssql-exp-1-it-sqltest02.domain.no
    depends_on:
      prometheus:
        condition: service_started
    volumes:
      - /etc/sql-exporter/burningalchemist_sql_exporter.yml:/etc/sql-exporter/sql_exporter.yml
      - /etc/sql-exporter/burningalchemist_mssql_standard.collector.yml:/etc/sql-exporter/mssql_standard.collector.yml
      - /etc/sql-exporter/burningalchemist_mssql_availability_groups.collector.yml:/etc/sql-exporter/mssql_availability_groups.collector.yml
    command: -config.file=/etc/sql-exporter/sql_exporter.yml
    networks:
      - monitoring

volumes:
  prometheus:  # Your existing prometheus volume

networks:
  monitoring:
    driver: bridge

---

/etc/prometheus/prometheus-mssql.yml

global:
  scrape_interval: 2m
  scrape_timeout: 2m

scrape_configs:
  - job_name: 'prometheus'
    scheme: https  # Force HTTPS for scraping
    static_configs:
      - targets: ['prometheus:9090']
    tls_config:
      insecure_skip_verify: true  # Only needed if your cert does not include "prometheus" as a SAN

  - job_name: 'mssql'
    static_configs:
      - targets:
        - 'mssql-exp-1-it-sqltest02.domain.no:9399'
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
        regex: 'mssql-exp-(.+):\d+'
        replacement: '$1'

---

/etc/sql-exporter/burningalchemist_mssql_availability_groups.collector.yml

collector_name: mssql_availability_groups

metrics:
  - metric_name: mssql_ag_replica_health
    type: gauge
    help: 'Always On AG replica health (1 = primary, 0 = secondary). Emits 0 when no AG exists.'
    key_labels:
      - ag_name
      - replica_server_name
      - role_desc
    values:
      - is_primary_replica
    query: |
      /* Real data – returns a row for each replica */
      SELECT
        ag.name                                 AS ag_name,
        ar.replica_server_name,
        rs.role_desc                            AS role_desc,
        CASE
          WHEN ar.replica_server_name = @@SERVERNAME
               AND rs.role_desc = 'PRIMARY' THEN 1
          ELSE 0
        END                                      AS is_primary_replica
      FROM sys.dm_hadr_availability_replica_states rs
      JOIN sys.availability_replicas ar
        ON rs.replica_id = ar.replica_id
      JOIN sys.availability_groups ag
        ON ag.group_id = ar.group_id

      UNION ALL

      /* Fallback row – only emitted when the first query returned nothing */
      SELECT
        'none'          AS ag_name,
        @@SERVERNAME    AS replica_server_name,
        'NO_AG'         AS role_desc,
        0               AS is_primary_replica
      WHERE NOT EXISTS (
        SELECT 1
        FROM sys.dm_hadr_availability_replica_states
      );

---

/etc/sql-exporter/burningalchemist_mssql_standard.collector.yml

(As provided in the GitHub repo – copy & pasted)

---

/etc/sql-exporter/burningalchemist_sql_exporter.yml

# Global settings and defaults.
global:
  scrape_timeout: 10s
  scrape_timeout_offset: 500ms
  scrape_error_drop_interval: 0s
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3

# The target to monitor and the collectors to execute on it.
target:
  # Target name (optional). Setting this field enables extra metrics e.g. `up` and `scrape_duration` with the `target`
  # label that are always returned on a scrape. If set, sql_exporter always returns HTTP 200 with these metrics populated
  name: mssql_database
  # Data source name always has a URI schema that matches the driver name. In some cases (e.g. MySQL)
  # the schema gets dropped or replaced to match the driver expected DSN format.
  data_source_name: 'sqlserver://sql_exporter:***@it-sqltest02.domain.no:1433?database=master'

  # Collectors (referenced by name) to execute on the target.
  collectors: [mssql_standard, mssql_availability_groups]

  # In case you need to connect to a backend that only responds to a limited set of commands (e.g. pgbouncer) or
  # a data warehouse you don't want to keep online all the time (due to the extra cost), you might want to disable `ping`
  enable_ping: true

# Collector files specifies a list of globs. One collector definition per file.
collector_files:
  - "/etc/sql-exporter/mssql_standard.collector.yml"
  - "/etc/sql-exporter/mssql_availability_groups.collector.yml"

[burningalchemist]

Hey @arindamg94, All Grafana dashboards have been contributed by the users. I'm happy to assist in debugging it on the sql_exporter side but I'm not affiliated with Grafana.

The best way to debug it usually:

• you check for the errors in the sql_exporter logs (in case of invalid queries or unmapped fields);
• you may want to check specifically Grafana configuration if you use their collector (Alloy).

Please share more context (but thanks for the configs! 😃), I'm happy to help.

[arindamg94]

Hi @burningalchemist

Thank you for making this awesome repo. I have done plenty of works with sql-exporters for mysql and postgres, but was lacking something for mssql, so I am trying to use this repo as much as possible.

Okay, let's start with the mssql_availability_groups collector. The query from this repo is:

          SELECT 
            ag.name as ag_name,
            ar.replica_server_name,
            rs.synchronization_state_desc as synchronization_state,
            CASE WHEN ar.replica_server_name = @@SERVERNAME 
              AND rs.role_desc = 'PRIMARY' THEN 1 ELSE 0 END as is_primary_replica
          FROM sys.dm_hadr_availability_replica_states rs
          JOIN sys.availability_replicas ar ON rs.replica_id = ar.replica_id
          JOIN sys.availability_groups ag ON ag.group_id = ar.group_id;

It gives the following error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'synchronization_state_desc'.

I am running

Microsoft SQL Server 2022 (RTM-CU25-GDR) (KB5101347) - 16.0.4262.2 (X64) Jun 18 2026 14:30:08 Copyright (C) 2022 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

What are the SQL-versons this repo's query support? :)

[burningalchemist]

hey @arindamg94 I guess the best way to get the answer would be to summon @zofer1zohargo who contributed the example. 👍 This might be simply a typo/mistake that sneaked into and needs a PR. 😃

Have you tried to:

• replace rs.synchronization_state_desc with rs.synchronization_health_desc;
• or use FROM sys.dm_hadr_database_replica_states rs instead of sys.dm_hadr_availability_replica_states rs.