MSSQL Exporter for Prometheus
| GitHub Actions | GitHub | Docker Hub |
|---|---|---|
 |
 |
 |
docker-compose updocker-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-
Download system of your choice from latest release.
-
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
}- 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;"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 1Note
- 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.
- 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']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.
You can install the exporter as windows service with the following command
sc create mssql_exporter binpath="%full_path_to_mssql_exporter.exe%"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.
- 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- 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
- Docker-compose!
docker-compose up
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent