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.

[zofer1zohargo]

Sorry, cannot help with this. Only working with postgres. I don't recall changing this example

[burningalchemist]

@zofer1zohargo indeed, apologies for the false assumption. 😅

@arindamg94 this means that the example comes from the original repo (from 2019 or earlier), so it might in fact contain old queries that might need an update.

I also don't work with MSSQL on the daily basis, so it's not easy for me to verify.

Either way, if anyone of the suggestions above works, please let me know or open a PR.

[arindamg94]

@burningalchemist

yeah, I just did a workaround instead (not to consume more of your time regarding SQL-queries itself, GPT helped out a bit too)

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: |
     /* All replicas from AG metadata, with role derived from runtime DMVs */
     SELECT
       ag.name                                 AS ag_name,
       ar.replica_server_name,
       COALESCE(
         rs.role_desc,
         CASE
           WHEN ar.replica_server_name = ags.primary_replica THEN 'PRIMARY'
           ELSE 'SECONDARY'
         END
       )                                       AS role_desc,
       CASE
         WHEN COALESCE(
                rs.role_desc,
                CASE WHEN ar.replica_server_name = ags.primary_replica THEN 'PRIMARY' ELSE 'SECONDARY' END
              ) = 'PRIMARY' THEN 1
         ELSE 0
       END                                      AS is_primary_replica
     FROM sys.availability_groups ag
     JOIN sys.availability_replicas ar
       ON ar.group_id = ag.group_id
     LEFT JOIN sys.dm_hadr_availability_replica_states rs
       ON rs.replica_id = ar.replica_id
     LEFT JOIN sys.dm_hadr_availability_group_states ags
       ON ags.group_id = ag.group_id

     UNION ALL

     /* Fallback row – only emitted when no AG is configured on this instance */
     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.availability_groups
     );

This gives me the lovely respons I can use with Grafana's State timeline (it is pretty cool to see when failover kicks in during patching etc).

[burningalchemist]

This is pretty cool! 👏 Thanks for sharing the solution! 👍

[arindamg94]

@burningalchemist

I will use this chance to ask for another thing also.

I don't see it, but does this repo have a "sql collector success" similar as seen in other repos

• https://github.com/prometheus-community/postgres_exporter has "pg_scrape_collector_success"
• https://github.com/prometheus/mysqld_exporter has "mysql_exporter_collector_success"

That would be a nice small feature to have :)

[burningalchemist]

Hey @arindamg94,

Aforementioned collectors typically (or used to) collect data on their own schedule (unrelated to Prometheus scrape configuration), so collector_success metric makes total sense, since we don't know when the collection was performed and was it successful.

sql_exporter behaves differently as it's bound to Prometheus scrape schedule (there is a small section at the bottom of the README about the idea behind), so collector_success doesn't bring any additional value to the already available automatic metrics:

- up{};
- scrape_errors{collector=*,job=*,target=*,query=*};
- scrape_duration_seconds{collector=*,job=*,target=*,query=*};

These two are available if the configured target has name field populated. For jobs it works out of the box since job targets are named automatically. This also helps with avoiding unnecessary information on a large scale, the error metric also indirectly points at this which level (collector/target/query) the error has occurred (in addition to the logs).

Also, there is global.enable_query_metrics setting that enables two extra metrics:

- query_duration_seconds{query=*};
- query_rows_returned{query=*};

Altogether this brings quite descriptive and yet flexible way to observe the operations in my opinion.

The lack of proper documentation is definitely an issue here, but I'm slowly coming to the v1.0 release that should feature some additional features (e.g. parameterised metrics and histograms, and there are some other ideas), and of course the overhauled documentation source. That's on my list.

---

Also, just in case you find it useful, this exporter supports NULL values. It's useful for situations, when the result set depends on some conditions, so it may be empty. This might help in optimising the fallback queries. 👍

[arindamg94]

Thanks for clarifying. Stupid me, yes, I can use the "up" - metric, haha!

Much appreciated 😄