SQL Server在很多企业中部署在多个平台上(Windows,Linux和Container)，需要一种能支持多平台的解决方案用于收集和展示相关的监控指标。
我选择企业中比较流行的监控展示工具Grafana和监控指标收集工具Telegraf进行实现。这也是为了方便与企业中已经在存在监控平台进行整合和对接。
如上图所示，Telegraf部署在SQL所在host，收集数据发送给时序数据库Influxdb存储，然后Grafana用于展示数据。
解决方案安装和配置InfluxDB
我将InfluxDB和Grafana安装在同一台CentOS主机上，生产环境中最好是分开。
# 下载1.8的stable version后进行安装 wget https://dl.influxdata.com/influxdb/releases/influxdb-1.8.0.x86_64.rpm chmod 755 influxdb-1.8.0.x86_64.rpm yum localinstall influxdb-1.8.0.x86_64.rpm # 启动并设置自启动 systemctl start influxdb systemctl enable influxdb # 8086用于客户端的HTTP连接，8088用于CLI调用RPC进行备份和还原操作 firewall-cmd --zone=public --add-port=8086/tcp --permanent firewall-cmd --zone=public --add-port=8088/tcp --permanent firewall-cmd --reload # 连接到influxdb并创建用户 fluxdb > CREATE USER admin WITH PASSWORD '<password>' WITH ALL PRIVILEGES # 启用http用户验证，修改influxdb.conf中http section中auth-enabled = true vim /etc/influxdb/influxdb.conf systemctl restart influxdb # 创建用于存储监控数据的数据库，保存6个月的数据 influx -username 'admin' -password '<password>' > CREATE DATABASE telegraf > CREATE RETENTION POLICY telegraf_6m ON telegraf DURATION 180d REPLICATION 1 DEFAULT > SHOW DATABASES安装和配置Grafana
# 下载并安装Grafana wget https://dl.grafana.com/oss/release/grafana-7.0.1-1.x86_64.rpm chmod 775 grafana-7.0.1-1.x86_64.rpm yum localinstall grafana-7.0.1-1.x86_64.rpm # 设置自启动 systemctl start grafana-server.service systemctl enable grafana-server.service # 允许Grafana默认的端口3000 firewall-cmd --zone=public --add-port=3000/tcp --permanent firewall-cmd --reload然后在Browser中访问:3000，第一次访问时默登录认账号和密码都为admin，登录后会提示修改密码。
在客户端主机安装和配置Telegraf
所谓客户端，就是SQL所在主机
Telegraf连接到SQL，需要一个login，具有 VIEW SERVER STATE and VIEW ANY DEFINITION的权限，所以在每个被监控的实例上都需要创建之。
USE master; GO CREATE LOGIN [telegraf] WITH PASSWORD = N'1qaz@WSX'; GO GRANT VIEW SERVER STATE TO [telegraf]; GO GRANT VIEW ANY DEFINITION TO [telegraf]; GOTelegraf on Linux
wget https://dl.influxdata.com/telegraf/releases/telegraf-1.14.3-1.x86_64.rpm sudo yum localinstall telegraf-1.14.3-1.x86_64.rpm安装完成后，先要修改Telegraf的配置文件，再启动。在配置文件中主要配置两个部分：inputs和outputs。 inputs表示监控数据从哪里来，outputs表示监控要发送到哪里去。
打开/etc/telegraf/telegraf.conf，找到[[outputs.influxdb]]部分，所有配置项默认都被注释了。我们需要删除注释并配置一些项。主要是Influxdb的地址，用户名、密码和数据库名等。
[[outputs.influxdb]] ## The full HTTP or UDP URL for your InfluxDB instance. ## ## Multiple URLs can be specified for a single cluster, only ONE of the ## urls will be written to each interval. # urls = ["unix:///var/run/influxdb.sock"] # urls = ["udp://127.0.0.1:8089"] urls = ["http://172.17.2.4:8086"] ## The target database for metrics; will be created as needed. ## For UDP url endpoint database needs to be configured on server side. database = "telegraf" ## The value of this tag will be used to determine the database. If this ## tag is not set the 'database' option is used as the default. # database_tag = "" ## If true, the 'database_tag' will not be included in the written metric. # exclude_database_tag = false ## If true, no CREATE DATABASE queries will be sent. Set to true when using ## Telegraf with a user without permissions to create databases or when the ## database already exists. skip_database_creation = true ## Name of existing retention policy to write to. Empty string writes to ## the default retention policy. Only takes effect when using HTTP. retention_policy = "" ## The value of this tag will be used to determine the retention policy. If this ## tag is not set the 'retention_policy' option is used as the default. # retention_policy_tag = "" ## If true, the 'retention_policy_tag' will not be included in the written metric. # exclude_retention_policy_tag = false ## Write consistency (clusters only), can be: "any", "one", "quorum", "all". ## Only takes effect when using HTTP. write_consistency = "any" ## Timeout for HTTP messages. timeout = "5s" ## HTTP Basic Auth username = "admin" password = "<password>"找到[[inputs.sqlserver]]部分,取消相关配置项的注释，servers部分连接到本地实例。