使用Telegraf+Grafana监控Microsoft SQLServer数据库
使用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-server2、方法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数据库中创建账号用于监控
