問題
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中通路http://: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];
GO
-
Telegraf 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部分連接配接到本地執行個體。
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:
# ## "encrypt=true;certificate=<cert>;hostNameInCertificate=<SqlServer host fqdn>"
servers = [
"Server=localhost;Port=1433;User Id=telegraf;Password=<yourPassword>;app name=telegraf;log=1;"
]
#
# ## 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和收集間隔
[root@SQL19N1 log]# systemctl status telegraf
● telegraf.service - The plugin-driven server agent for reporting metrics into InfluxDB
Loaded: loaded (/usr/lib/systemd/system/telegraf.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2020-05-26 14:19:07 UTC; 19min ago
Docs: https://github.com/influxdata/telegraf
Main PID: 12359 (telegraf)
CGroup: /system.slice/telegraf.service
└─12359 /usr/bin/telegraf -config /etc/telegraf/telegraf.conf -config-directory /etc/telegraf/telegraf.d
May 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: host=SQL19N1
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的監控
- 本文内容僅代表個人觀點，與任何公司群組織無關
-------------------------------------
作者：Joe.TJ
Joe's Blog:http://www.cnblogs.com/Joe-T/