使用Telegraf+Grafana监控Microsoft SQLServer数据库

(图片点击放大查看)

本文参考如下链接完成

https://tsql.tech/how-to-use-grafana-on-docker-to-monitor-your-sql-server-eventually-on-docker-too-feat-influxdb-and-telegraf/
https://bbs.huaweicloud.com/blogs/358876?utm_source=zhihu&utm_medium=bbs-ex&utm_campaign=other&utm_content=content
https://www.likecs.com/show-308132817.html
https://my.oschina.net/u/3772973/blog/4383713
https://36chambers.wordpress.com/2019/02/05/metrics-for-free-sql-server-monitoring-with-telegraf/

一、安装Influxdb

1、安装Influxdb

rpm -ivh influxdb-1.8.10.x86_64.rpm 
systemctl start influxdb
systemctl enable influxdb
firewall-cmd --zone=public --add-port=8086/tcp --permanent
firewall-cmd --reload

(图片点击放大查看)

2、创建telegraf数据库，并设置数据库账号密码

influx
show databases
CREATE USER admin WITH PASSWORD 'influxDB@2023' WITH ALL PRIVILEGES
CREATE USER telegraf WITH PASSWORD 'TeleGraf@2023' WITH ALL PRIVILEGES
show users
create database telegraf
CREATE RETENTION POLICY telegraf_12m ON telegraf DURATION 360d REPLICATION 1 DEFAULT
show databases

(图片点击放大查看)

3、Influxdb配置文件修改

方法1、手工修改vim /etc/influxdb/influxdb.conf

启用http用户验证，修改influxdb.conf中http section中auth-enabled = true

方法2、sed命令替换

sed -i 's/# bind-address = ":8086"/ bind-address = ":8086"/g' /etc/influxdb/influxdb.conf sed -i "s/# auth-enabled = false/auth-enabled = true/g" /etc/influxdb/influxdb.conf systemctl restart influxdb systemctl status influxdb

(图片点击放大查看)

4、使用账号密码方式登录验证

influx -username "telegraf" -password ''

(图片点击放大查看)

二、安装telegraf

rpm -ihv telegraf-1.25.1-1.x86_64.rpm 
vim /etc/telegraf/telegraf.conf

找到[[outputs.influxdb]]配置的位置，取消注释并修改相关配置

urls = ["http://192.168.31.170:8086"]
database = "telegraf"
skip_database_creation = true
username = "telegraf"
password = "TeleGraf@2023"

(图片点击放大查看)

systemctl restart telegraf.service 
systemctl status telegraf.service -l

(图片点击放大查看)

三、安装grafana

yum localinstall grafana-9.3.6-1.x86_64.rpm 
firewall-cmd --permanent --zone=public --add-port=3000/tcp
firewall-cmd --reload
systemctl enable grafana-server
systemctl start grafana-server

(图片点击放大查看)

添加influxDB数据源

(图片点击放大查看)

import Dashboard ID 5955

可以看到telegraf所在服务器的基础监控信息

(图片点击放大查看)

四、SQLServer数据库的监控需要修改

telegraf上配置inputs.sqlserver

接下来进入正题 vim /etc/telegraf/telegraf.conf

修改如下配置，填写SQLServer数据库 sa账号密码信息

vim /etc/telegraf/telegraf.conf
[[inputs.sqlserver]]中的配置
servers = [
     "Server=192.168.1.10;Port=1433;User Id=<user>;Password=<pw>;app name=telegraf;log=1;",
   ]  
query_version = 2

(图片点击放大查看)

systemctl restart telegraf.service 
systemctl status telegraf.service -l

(图片点击放大查看)

确认是否可以正常连接到数据库

这时导入import Dashboard ID 9386

(图片点击放大查看)

grafana-piechart-panel已过时问题

1、方法1

grafana-cli plugins install grafana-piechart-panel
systemctl restart grafana-server

2、方法2 ：更新为新的piechart

(图片点击放大查看)

最终的效果如下：

(图片点击放大查看)

五、总结

1、SQLServer数据库的grafana Dashboard

1、ID 409

https://grafana.com/grafana/dashboards/409-sql-server-telegraf/

导入后不可行，无数据

2、另外一个不错的Dashboards

https://github.com/tboggiano/grafana/tree/master/Dashboards

手动导入Instance Overview.json 并调整Datasource，可行

3、当然SQLServer也可以用prometheus-mssql-exporter+Prometheus的方式进行监控

配置也很简单，这里就不演示了

1、启动prometheus-mssql-exporter的容器

docker run -e SERVER=XXXXXX -e USERNAME=sa -e PASSWORD=XXXX -e DEBUG=app -p 4000:4000 --name prometheus-mssql-exporter awaragi/prometheus-mssql-exporter
2、Prometheus添加一下targets
3、Grafana上面导入如下ID即可

https://github.com/awaragi/prometheus-mssql-exporter https://grafana.com/grafana/dashboards/13919-microsoft-sql-server/ https://grafana.com/grafana/dashboards/9336-prometheus-mssql-exporter/