Skip to content
BranchesTags

Repository files navigation

mssql_exporter

MSSQL Exporter for Prometheus

GitHub Actions GitHub Docker Hub
Build Dockerfile check GitHub release Docker Hub

Quickstart docker-compose

docker-compose up

docker-compose.yml

version: '3'
services:
  mssql_exporter:
    image: "danieloliver/mssql_exporter:latest"
    ports:
      - "80:80"
    depends_on:
      - sqlserver.dev
    environment:
      - PROMETHEUS_MSSQL_DataSource=Server=tcp:sqlserver.dev,1433;Initial Catalog=master;Persist Security Info=False;User ID=sa;Password=yourStrong(!)Password;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=10;
      - PROMETHEUS_MSSQL_ConfigFile=metrics.json
      - PROMETHEUS_MSSQL_ServerPath=metrics
      - PROMETHEUS_MSSQL_ServerPort=80
      - PROMETHEUS_MSSQL_AddExporterMetrics=false
      - PROMETHEUS_MSSQL_Serilog__MinimumLevel=Information
      - |
        PROMETHEUS_MSSQL_ConfigText=
        {
          "Queries": [
            {
              "Name": "mssql_process_status",
              "Query": "SELECT status, COUNT(*) count FROM sys.sysprocesses GROUP BY status",
              "Description": "Counts the number of processes per status",
              "Usage": "GaugesWithLabels",
              "Columns": [
                {
                  "Name": "status",
                  "Label": "status",
                  "Usage": "GaugeLabel",
                  "Order": 0
                },
                {
                  "Name": "count",
                  "Label": "count",
                  "Usage": "Gauge"
                }
              ]
            },
            {
              "Name": "mssql_process_connections",
              "Query": "SELECT ISNULL(DB_NAME(dbid), 'other') as dbname, COUNT(dbid) as connections FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid",
              "Description": "Counts the number of connections per db",
              "Usage": "GaugesWithLabels",
              "Columns": [
                {
                  "Name": "dbname",
                  "Label": "dbname",
                  "Usage": "GaugeLabel",
                  "Order": 0
                },
                {
                  "Name": "connections",
                  "Label": "count",
                  "Usage": "Gauge"
                }
              ]
            },
            {
              "Name": "mssql_deadlocks",
              "Query": "SELECT cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'",
              "Description": "Number of lock requests per second that resulted in a deadlock since last restart",
              "Columns": [
                {
                  "Name": "cntr_value",
                  "Label": "mssql_deadlocks",
                  "Usage": "Gauge",
                  "DefaultValue": 0
                }
              ]
            }
          ],
          "MillisecondTimeout": 4000
        }
  sqlserver.dev:
    image: "mcr.microsoft.com/mssql/server:2017-latest"
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=yourStrong(!)Password

QuickStart binary

  1. Download system of your choice from latest release.

  2. Create a file "metrics.json" and put this in it:

{
  "Queries": [
    {
      "Name": "mssql_process_status",
      "Query": "SELECT status, COUNT(*) count FROM sys.sysprocesses GROUP BY status",
      "Description": "Counts the number of processes per status",
      "Usage": "GaugesWithLabels",
      "Columns": [
        {
          "Name": "status",
          "Label": "status",
          "Usage": "GaugeLabel",
          "Order": 0
        },
        {
          "Name": "count",
          "Label": "count",
          "Usage": "Gauge"
        }
      ]
    },
    {
      "Name": "mssql_process_connections",
      "Query": "SELECT ISNULL(DB_NAME(dbid), 'other') as dbname, COUNT(dbid) as connections FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid",
      "Description": "Counts the number of connections per db",
      "Usage": "GaugesWithLabels",
      "Columns": [
        {
          "Name": "dbname",
          "Label": "dbname",
          "Usage": "GaugeLabel",
          "Order": 0
        },
        {
          "Name": "connections",
          "Label": "count",
          "Usage": "Gauge"
        }
      ]
    },
    {
      "Name": "mssql_deadlocks",
      "Query": "SELECT cntr_value FROM sys.dm_os_performance_counters where counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'",
      "Description": "Number of lock requests per second that resulted in a deadlock since last restart",
      "Columns": [
        {
          "Name": "cntr_value",
          "Label": "mssql_deadlocks",
          "Usage": "Gauge",
          "DefaultValue": 0
        }
      ]
    }
  ],
  "MillisecondTimeout": 4000
}
  1. Run mssql_exporter
./mssql_exporter serve -ConfigFile "metrics.json" -DataSource "Server=tcp:{ YOUR DATABASE HERE },1433;Initial Catalog={ YOUR INITIAL CATALOG HERE };Persist Security Info=False;User ID={ USER ID HERE };Password={ PASSWORD HERE };MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=8;"

or

.\mssql_exporter.exe serve -ConfigFile "metrics.json" -DataSource "Server=tcp:{ YOUR DATABASE HERE },1433;Initial Catalog={ YOUR INITIAL CATALOG HERE };Persist Security Info=False;User ID={ USER ID HERE };Password={ PASSWORD HERE };MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=8;"
  1. Open http://localhost/metrics

Content should look like

# HELP mssql_process_status Counts the number of processes per status
# TYPE mssql_process_status gauge
mssql_process_status{status="runnable"} 2
mssql_process_status{status="sleeping"} 19
mssql_process_status{status="background"} 24
# HELP mssql_process_connections Counts the number of connections per db
# TYPE mssql_process_connections gauge
mssql_process_connections{dbname="master"} 29
mssql_process_connections{dbname="tempdb"} 1
# HELP mssql_timeouts Number of queries timing out.
# TYPE mssql_timeouts gauge
mssql_timeouts 0
# HELP mssql_exceptions Number of queries throwing exceptions.
# TYPE mssql_exceptions gauge
mssql_exceptions 0
# HELP mssql_deadlocks mssql_deadlocks
# TYPE mssql_deadlocks gauge
mssql_deadlocks 0
# HELP mssql_up mssql_up
# TYPE mssql_up gauge
mssql_up 1

Note

  • mssql_up gauge is "1" if the database is reachable. "0" if connection to the database fails.
  • mssql_exceptions gauge is "0" if all queries run successfully. Else, this is the number of queries that throw exceptions.
  • mssql_timeouts is "0" if all queries are running with the configured timeout. Else, this is the number of queries that are not completing within the configured timeout.
  1. Add Prometheus scrape target (assuming same machine).
global:
  scrape_interval:     15s # Set the scrape interval to every 15 seconds. Default is every 1 minute.
  evaluation_interval: 15s # Evaluate rules every 15 seconds. The default is every 1 minute.
  
scrape_configs:
  - job_name: 'netcore-prometheus'
    # metrics_path defaults to '/metrics'
    static_configs:
    - targets: ['localhost']

Command Line Options

Commands
   help
   serve
      -DataSource (Connection String)
      -ConfigFile (metrics.json)
      -ServerPath (/metrics)
      -ServerPort (80)
      -AddExporterMetrics (false)
      -ConfigText ()

Or environment variables:
      PROMETHEUS_MSSQL_DataSource
      PROMETHEUS_MSSQL_ConfigFile
      PROMETHEUS_MSSQL_ServerPath
      PROMETHEUS_MSSQL_ServerPort
      PROMETHEUS_MSSQL_AddExporterMetrics
      PROMETHEUS_MSSQL_ConfigText
      PROMETHEUS_MSSQL_Serilog__MinimumLevel
  • DataSource
    • Default: empty
    • SQL Server .NET connection String
  • ConfigFile
    • Default: "metrics.json"
    • The path to the configuration file as shown in "metrics.json" above.
  • ServerPath
    • Default: "metrics"
    • specifies the path for prometheus to answer requests on
  • ServerPort
    • Default: 80
  • AddExporterMetrics
    • Default: false
    • Options:
      • true
      • false
  • ConfigText
    • Default: empty
    • Optionally fill in this with the contents of the ConfigFile to ignore and not read from the ConfigFile.

Run as windows service

You can install the exporter as windows service with the following command

sc create mssql_exporter binpath="%full_path_to_mssql_exporter.exe%"

Changing logging configuration

Logging is configured using Serilog Settings Configuration

Editing "config.json" allows for changing aspects of logging. Console is default, and "Serilog.Sinks.File" is also installed. Further sinks would have to be installed into the project file's dependencies.

Debug Run and Docker

  1. Run Docker
docker run -e 'ACCEPT_EULA=Y' -e "SA_PASSWORD=yourStrong(!)Password" --net=host -p 1433:1433 -d --rm --name sqlserverdev mcr.microsoft.com/mssql/server:2017-latest
  1. Run exporter from "src/server" directory.
dotnet run -- serve -ConfigFile "../../metrics.json" -DataSource "Server=tcp:localhost,1433;Initial Catalog=master;Persist Security Info=False;User ID=sa;Password=yourStrong(!)Password;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=8;"
dotnet run -- serve -ConfigFile "../../metrics.json" -DataSource 'Server=tcp:localhost,1433;Initial Catalog=master;Persist Security Info=False;User ID=sa;Password=yourStrong(!)Password;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=True;Connection Timeout=8;'

OR

  1. Docker-compose!
docker-compose up

About

MSSQL Exporter for Prometheus

Topics

mssqlprometheus-exporter

Resources

Readme
MIT license
Activity

Stars

53 stars

Watchers

5 watching

Forks

17 forks
Report repository

Releases

Packages

Used by

Contributors

Languages