怎么做淘宝客网站赚钱,凡科建站微信小程序,wordpress店铺,做网站去哪里找问题SQL Server在很多企业中部署在多个平台上(Windows,Linux和Container)#xff0c;需要一种能支持多平台的解决方案用于收集和展示相关的监控指标。我选择企业中比较流行的监控展示工具Grafana和监控指标收集工具Telegraf进行实现。这也是为了方便与企业中已经在存在监控平台… 问题SQL Server在很多企业中部署在多个平台上(Windows,Linux和Container)需要一种能支持多平台的解决方案用于收集和展示相关的监控指标。我选择企业中比较流行的监控展示工具Grafana和监控指标收集工具Telegraf进行实现。这也是为了方便与企业中已经在存在监控平台进行整合和对接。如上图所示Telegraf部署在SQL所在host收集数据发送给时序数据库Influxdb存储然后Grafana用于展示数据。解决方案安装和配置InfluxDB# 下载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 --zonepublic --add-port8086/tcp --permanent
firewall-cmd --zonepublic --add-port8088/tcp --permanent
firewall-cmd --reload# 连接到influxdb并创建用户
fluxdbCREATE USER admin WITH PASSWORD password WITH ALL PRIVILEGES# 启用http用户验证修改influxdb.conf中http p中auth-enabled true
vim /etc/influxdb/influxdb.conf
systemctl restart influxdb# 创建用于存储监控数据的数据库保存6个月的数据
influx -username admin -password passwordCREATE DATABASE telegrafCREATE RETENTION POLICY telegraf_6m ON telegraf DURATION 180d REPLICATION 1 DEFAULTSHOW 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 --zonepublic --add-port3000/tcp --permanent
firewall-cmd --reload
然后在Browser中访问http://:3000第一次访问时默登录认账号和密码都为admin登录后会提示修改密码。在在被监控的数据库主机安装和配置TelegrafTelegraf连接到SQL需要一个login具有 VIEW SERVER STATE and VIEW ANY DEFINITION的权限所以在每个被监控的实例上都需要创建之。USE master;
GO
CREATE LOGIN [telegraf] WITH PASSWORD N1qazWSX;
GO
GRANT VIEW SERVER STATE TO [telegraf];
GO
GRANT VIEW ANY DEFINITION TO [telegraf];
GO
Telegraf on Linuxwget 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 Authusername adminpassword password
找到[[inputs.sqlserver]]部分,取消相关配置项的注释servers部分连接到本地实例。Telegraf默认的Plugin中包括了对SQL Server的实现, 这个Plugin还包括了对Azure SQL PaaS的实现# # Read metrics from Microsoft SQL Server[[inputs.sqlserver]]
# ## Specify instances to monitor with a list of connection strings.
# ## All connection parameters are optional.
# ## By default, the host is localhost, listening on default port, TCP 1433.
# ## for Windows, the user is the currently running AD user (SSO).
# ## See https://github.com/denisenkom/go-mssqldb for detailed connection
# ## parameters, in particular, tls connections can be created like so:
# ## encrypttrue;certificatecert;hostNameInCertificateSqlServer host fqdnservers [Serverlocalhost;Port1433;User Idtelegraf;PasswordyourPassword;app nametelegraf;log1;]
#
# ## Optional parameter, setting this to 2 will use a new version
# ## of the collection queries that break compatibility with the original
# ## dashboards.query_version 2
#
# ## If you are using AzureDB, setting this to true will gather resource utilization metrics
# # azuredb false
#
# ## Possible queries:
# ## - PerformanceCounters
# ## - WaitStatsCategorized
# ## - DatabaseIO
# ## - DatabaseProperties
# ## - CPUHistory
# ## - DatabaseSize
# ## - DatabaseStats
# ## - MemoryClerk
# ## - VolumeSpace
# ## - PerformanceMetrics
# ## - Schedulers
# ## - AzureDBResourceStats
# ## - AzureDBResourceGovernance
# ## - SqlRequests
# ## - ServerProperties
# ## A list of queries to include. If not specified, all the above listed queries are used.
# # include_query []
#
# ## A list of queries to explicitly ignore.
# exclude_query [ Schedulers , SqlRequests]
启动Telegraf之后可以看到时已经加载的inputs和收集间隔[rootSQL19N1 log]# systemctl status telegraf
● telegraf.service - The plugin-driven server agent for reporting metrics into InfluxDBLoaded: loaded (/usr/lib/systemd/system/telegraf.service; enabled; vendor preset: disabled)Active: active (running) since Tue 2020-05-26 14:19:07 UTC; 19min agoDocs: https://github.com/influxdata/telegrafMain PID: 12359 (telegraf)CGroup: /system.slice/telegraf.service└─12359 /usr/bin/telegraf -config /etc/telegraf/telegraf.conf -config-directory /etc/telegraf/telegraf.dMay 26 14:19:07 SQL19N1 systemd[1]: Started The plugin-driven server agent for reporting metrics into InfluxDB.
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Starting Telegraf 1.14.3
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Loaded inputs: system cpu disk diskio kernel mem processes swap sqlserver
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Loaded aggregators:
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Loaded processors:
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Loaded outputs: influxdb
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! Tags enabled: hostSQL19N1
May 26 14:19:07 SQL19N1 telegraf[12359]: 2020-05-26T14:19:07Z I! [agent] Config: Interval:20s, Quiet:false, Hostname:SQL19N1, Flush Interval:10s
Telegraf on Windows以管理员身份执行如下PowerShell命令# 下载软件
wget https://dl.influxdata.com/telegraf/releases/telegraf-1.14.3_windows_amd64.zip ·-OutFile c:\temp\telegraf-1.14.3_windows_amd64.zip# 解压缩到C:\Program Files\Telegraf
Expand-Archive c:\temp\telegraf-1.14.3_windows_amd64.zip, C:\Program Files# 将telegraf安装为windows服务
C:\Program Files\Telegraf\telegraf.exe --service install
修改telegraf.conf中outputs.influxdb和添加inputs.sqlserver部分这些内容和在Linux上的配置一样就不赘述了。conf修改完成后可以先测试一下telegraf是否能正常启动没问题的话就启动telegraf服务。# 测试
C:\Program Files\Telegraf\telegraf.exe --config C:\Program Files\Telegraf\telegraf.conf --test# 启动服务
C:\Program Files\Telegraf\telegraf.exe --service start
配置Grafana的数据源和Dashboard登录Grafana后,在左侧的Configuration-Data Source中配置InfluxDB数据源填写地址、账号、密码并设置为默认数据源如下图Dashboard可以自己创建也可以在采用公开社区的(感谢热心无私的大佬们)。这里我采用SQL Servers by Jonathan Rioux。这个Dashboard中使用的Piechart不是Grafana预置的所以还需要安装# Grafana所在Host安装重启服务生效
grafana-cli plugins install grafana-piechart-panel
systemctl restart grafana-server.service
然后在Grafana界面选择左侧的Dashboard-Import-填入Dashboard ID-Import如下图配置完成后的可以看这个Dashboard提供的信息还比较丰富的您也可以根据自己的需要修改和添加相关内容.总结实际情况中自带的数据收集和报表不能完全满足业务需求自定义的数据收集和自定义的Dashboard也是非常容易实现的下次再写如果已经在使用Zabbix了Grafana可以直接对接到Zabbix的数据输出。Telegraf能非常好的支持Cloud环境下次说说对Azure SQL PaaS的监控转载自https://www.cnblogs.com/Joe-T/p/12973362.html
