danieloliver/mssql_exporter

mssql_exporter

Prometheus的MSSQL Exporter，用于从Microsoft SQL Server收集指标并提供给Prometheus监控系统。

镜像概述和主要用途

mssql_exporter是一个Prometheus导出器，专门用于从Microsoft SQL Server数据库收集性能指标。它通过执行自定义SQL查询获取数据库状态信息（如连接数、死锁数量、进程状态等），并将这些指标转换为Prometheus兼容的格式，以便集成到监控系统中。

核心功能和特性

• 自定义指标查询：支持通过JSON配置文件定义SQL查询，灵活收集所需指标
• 多配置方式：支持环境变量、配置文件或命令行参数进行配置
• 健康检查指标：内置mssql_up（数据库连接状态）、mssql_exceptions（查询异常数）、mssql_timeouts（查询超时数）等监控指标
• Prometheus兼容：输出符合Prometheus规范的指标格式，可直接被Prometheus抓取
• 轻量级部署：支持Docker容器化部署和二进制文件直接运行

使用场景和适用范围

适用于需要监控Microsoft SQL Server性能的场景，包括：

• 数据库连接数监控
• 死锁数量跟踪
• 进程状态分析
• 自定义业务指标收集
• 集成到Prometheus+Grafana监控体系中

使用方法和配置说明

Docker Compose快速启动

1. 创建docker-compose.yml文件：

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": "按状态统计进程数量",
              "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": "按数据库统计连接数量",
              "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": "自上次重启以来每秒导致死锁的锁请求数量",
              "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

2. 启动服务：

powershell
docker-compose up

二进制文件快速启动

1. 从https://github.com/DanielOliver/mssql_exporter/releases/latest%E4%B8%8B%E8%BD%BD%E5%AF%B9%E5%BA%94%E7%B3%BB%E7%BB%9F%E7%9A%84%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%96%87%E4%BB%B6

2. 创建metrics.json配置文件：

json
{
  "Queries": [
    {
      "Name": "mssql_process_status",
      "Query": "SELECT status, COUNT(*) count FROM sys.sysprocesses GROUP BY status",
      "Description": "按状态统计进程数量",
      "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": "按数据库统计连接数量",
      "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": "自上次重启以来每秒导致死锁的锁请求数量",
      "Columns": [
        {
          "Name": "cntr_value",
          "Label": "mssql_deadlocks",
          "Usage": "Gauge",
          "DefaultValue": 0
        }
      ]
    }
  ],
  "MillisecondTimeout": 4000
}

3. 运行exporter：

Linux/macOS：

bash
./mssql_exporter serve -ConfigFile "metrics.json" -DataSource "Server=tcp:{你的数据库地址},1433;Initial Catalog={初始数据库};Persist Security Info=False;User ID={用户名};Password={密码};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=8;"

Windows：

powershell
.\mssql_exporter.exe serve -ConfigFile "metrics.json" -DataSource "Server=tcp:{你的数据库地址},1433;Initial Catalog={初始数据库};Persist Security Info=False;User ID={用户名};Password={密码};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=8;"

4. 访问指标：打开 http://localhost/metrics，指标示例：

txt
# HELP mssql_process_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 按数据库统计连接数量
# TYPE mssql_process_connections gauge
mssql_process_connections{dbname="master"} 29
mssql_process_connections{dbname="tempdb"} 1
# HELP mssql_timeouts 查询超时数量
# TYPE mssql_timeouts gauge
mssql_timeouts 0
# HELP mssql_exceptions 查询异常数量
# TYPE mssql_exceptions gauge
mssql_exceptions 0
# HELP mssql_deadlocks mssql_deadlocks
# TYPE mssql_deadlocks gauge
mssql_deadlocks 0
# HELP mssql_up 数据库连接状态
# TYPE mssql_up gauge
mssql_up 1

指标说明：

• mssql_up：1表示数据库可连接，0表示连接失败
• mssql_exceptions：0表示所有查询成功执行，否则为异常查询数量
• mssql_timeouts：0表示所有查询在配置超时内完成，否则为超时查询数量

5. 配置Prometheus抓取目标：

yml
global:
  scrape_interval:     15s # 抓取间隔设为15秒，默认1分钟
  evaluation_interval: 15s # 规则评估间隔设为15秒，默认1分钟
  
scrape_configs:
  - job_name: 'mssql-exporter'
    static_configs:
    - targets: ['localhost'] # 假设exporter与Prometheus在同一机器

命令行选项

命令
   help - 显示帮助信息
   serve - 启动exporter服务
      -DataSource (连接字符串) - SQL Server连接字符串
      -ConfigFile (metrics.json) - 配置文件路径
      -ServerPath (/metrics) - Prometheus指标路径
      -ServerPort (80) - 服务端口
      -AddExporterMetrics (false) - 是否添加导出器自身指标
      -ConfigText () - 配置内容（直接指定配置，忽略ConfigFile）

环境变量替代：
      PROMETHEUS_MSSQL_DataSource
      PROMETHEUS_MSSQL_ConfigFile
      PROMETHEUS_MSSQL_ServerPath
      PROMETHEUS_MSSQL_ServerPort
      PROMETHEUS_MSSQL_AddExporterMetrics
      PROMETHEUS_MSSQL_ConfigText
      PROMETHEUS_MSSQL_Serilog__MinimumLevel

配置参数说明

作为Windows服务运行

可通过以下命令将exporter安装为Windows服务：

bash
sc create mssql_exporter binpath="%mssql_exporter.exe的完整路径%"

日志配置修改

日志使用https://github.com/serilog/serilog-settings-configuration%E8%BF%9B%E8%A1%8C%E9%85%8D%E7%BD%AE%E3%80%82%E7%BC%96%E8%BE%91%60config.json%60%E6%96%87%E4%BB%B6%E5%8F%AF%E4%BF%AE%E6%94%B9%E6%97%A5%E5%BF%97%E7%9B%B8%E5%85%B3%E8%AE%BE%E7%BD%AE%EF%BC%8C%E9%BB%98%E8%AE%A4%E5%90%AF%E7%94%A8%E6%8E%A7%E5%88%B6%E5%8F%B0%E6%97%A5%E5%BF%97%EF%BC%8C%E5%90%8C%E6%97%B6%E6%94%AF%E6%8C%81%E6%96%87%E4%BB%B6%E6%97%A5%E5%BF%97%EF%BC%88%E9%80%9A%E8%BF%87%60Serilog.Sinks.File%60%EF%BC%89%E3%80%82%E5%A6%82%E9%9C%80%E5%85%B6%E4%BB%96%E6%97%A5%E5%BF%97%E8%BE%93%E5%87%BA%E6%96%B9%E5%BC%8F%EF%BC%8C%E9%9C%80%E6%B7%BB%E5%8A%A0%E7%9B%B8%E5%BA%94%E4%BE%9D%E8%B5%96%E3%80%82

调试运行和Docker

1. 运行SQL Server容器：

powershell
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

2. 从源码目录运行exporter：

powershell
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;"

或使用Docker Compose：

powershell
docker-compose up