SRE文档

深浅模式

https://www.alibabacloud.com/blog/600909

1. windows_exporter ​

1.1 介绍 ​

既能收集win机器指标，又能收集SqlServer

  • 默认安装到C:\Program Files \windows_exporter目录。
  • 默认监听端口是9182

1.2 部署 ​

看windows_exporter章节介绍

1.配置mssql ​

C:\Program Files\windows_exporter\config.yml

默认是空，添加如下配置即可开启mssql监控，重启服务

collectors:
  enabled: cs,dhcp,dns,cpu,logical_disk,memory,net,os,textfile,thermalzon,time,cpu_info,mem,tcp,udp,diskdrive,logon,physical_disk,system,service,process,mssql
log:
  level: warn
collectors:
  enabled: cs,dhcp,dns,cpu,logical_disk,memory,net,os,textfile,thermalzon,time,cpu_info,mem,tcp,udp,diskdrive,logon,physical_disk,system,service,process,mssql
log:
  level: warn

2.开启SQL Server Agent in SSMS ​

img

  • 配置开机自起

img

3.调试 ​

手动启动并配置参数

"C:\Program Files\windows_exporter\windows_exporter.exe" --collectors.enabled="mssql" --log.format logger:eventlog?name=windows_exporter --log.file eventlog --config.file="C:\Program Files\windows_exporter\config.yaml"
"C:\Program Files\windows_exporter\windows_exporter.exe" --collectors.enabled="mssql" --log.format logger:eventlog?name=windows_exporter --log.file eventlog --config.file="C:\Program Files\windows_exporter\config.yaml"
  • 效果

image-20241121221410390

1.3 配置Prometheus采集 ​

yaml
    - job_name: Windows
      static_configs:
      - targets:
        - 10.103.236.129:9182
      scrape_interval: 1m
      scrape_timeout: 30s
      scheme: http
      metrics_path: "/metrics"
      honor_labels: true
    - job_name: Windows
      static_configs:
      - targets:
        - 10.103.236.129:9182
      scrape_interval: 1m
      scrape_timeout: 30s
      scheme: http
      metrics_path: "/metrics"
      honor_labels: true
  • 重启服务
bash
curl -XPOST  http://prometheus.ikubernetes.net/-/reload
curl -XPOST  http://prometheus.ikubernetes.net/-/reload

1.告警规则 ​

https://github.com/prometheus-community/windows_exporter/blob/master/docs/collector.service.md

yaml
  ############## SqlServer_rules ###########
  SqlServer.rules: |
    groups:
    - name: "SqlServer.rules"
      rules:
      - alert: SqlServer_Down
        expr: windows_service_state{ name="MSSQLSERVER",state="running"} == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: SQL Server down (instance {{ $labels.instance }})
          description: "SQL server instance is down\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  ############## SqlServer_rules ###########
  SqlServer.rules: |
    groups:
    - name: "SqlServer.rules"
      rules:
      - alert: SqlServer_Down
        expr: windows_service_state{ name="MSSQLSERVER",state="running"} == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: SQL Server down (instance {{ $labels.instance }})
          description: "SQL server instance is down\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

1.4 grafana ​

15024

2. prometheus-mssql-exporter ​

1.1 介绍 ​

官当

1.2 部署 ​

1.k8s ​

yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: sql-exporter
  labels:
    app: sql-exporter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sql-exporter
  template:
    metadata:
      labels:
        app: sql-exporter
    spec:
      containers:
      - name: sql-exporter
        image: awaragi/prometheus-mssql-exporter:latest
        env:
        - name: SERVER
          value: "your-mssql-server"
        - name: USERNAME
          value: "your-username"
        - name: PASSWORD
          value: "your-password"
        - name: DEBUG
          value: "app"
        - name: PORT
          value: "1433"
        ports:
        - containerPort: 4000
---
apiVersion: v1
kind: Service
metadata:
  name: sql-exporter
spec:
  selector:
    app: sql-exporter
  ports:
    - port: 4000
      targetPort: 4000
apiVersion: apps/v1
kind: Deployment
metadata:
  name: sql-exporter
  labels:
    app: sql-exporter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: sql-exporter
  template:
    metadata:
      labels:
        app: sql-exporter
    spec:
      containers:
      - name: sql-exporter
        image: awaragi/prometheus-mssql-exporter:latest
        env:
        - name: SERVER
          value: "your-mssql-server"
        - name: USERNAME
          value: "your-username"
        - name: PASSWORD
          value: "your-password"
        - name: DEBUG
          value: "app"
        - name: PORT
          value: "1433"
        ports:
        - containerPort: 4000
---
apiVersion: v1
kind: Service
metadata:
  name: sql-exporter
spec:
  selector:
    app: sql-exporter
  ports:
    - port: 4000
      targetPort: 4000

2.配置采集 ​

yaml
- job_name: sql-exporter
    static_configs:
    - targets: [sql-exporter:4000]
- job_name: sql-exporter
    static_configs:
    - targets: [sql-exporter:4000]

3.grafana ​

3. mssql_exporter ​

1. 介绍 ​

https://github.com/DanielOliver/mssql_exporter

这种方式所有sql语句自己收集

2. 部署 ​

vi docker-compose.yml

yaml
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
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

$env:PROMETHEUS_MSSQL_DataSource="Server=tcp:hjgjhg324.database.windows.net,1433;Initial Catalog=jjkhjk34;Persist Security Info=False;User ID={ USER ID };Password={ PASWORD }; MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

3. 配置采集 ​

  - job_name: 'netcore-prometheus'
    # metrics_path defaults to '/metrics'
    static_configs:
    - targets: ['localhost']
  - job_name: 'netcore-prometheus'
    # metrics_path defaults to '/metrics'
    static_configs:
    - targets: ['localhost']

4. grafana ​

自己画

自定义

4. sql_exporter-推荐这个 ​

https://github.com/burningalchemist/sql_exporter（一直在更新）

更多、更高级的sql server采集指标：https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver?tdsourcetag=s_pctim_aiomsg

sql server 对象监控 参考：https://docs.microsoft.com/zh-cn/sql/relational-databases/performance-monitor/use-sql-server-objects?view=sql-server-ver15

4.1 介绍 ​

可以自定义查询sql语句来生成指标

参考：

https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-wait-statistics-object?view=sql-server-2017

https://github.com/free/sql_exporter

https://grafana.com/grafana/dashboards/13919-microsoft-sql-server/