使用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/
4、注意事项
由于telegraf配置文件中填写的账号密码为sa明文密码，请注意密码保密！
当然你可以单独在SQLServer数据库中创建账号用于监控
