使用zabbix監控sql server的釋出訂閱

gegeman發表於2020-09-09
SQLServer

（一）背景

個人在使用sql server時，用到了sql server的釋出訂閱來做主從同步，類似MySQL的非同步複製。在釋出訂閱環境搭建完成後，最重要的就是如何監控複製的狀態了，sql server提供了複製監視器來方便我們監控複製狀態、複製延遲等資訊，總體來說，非常好用，複製監視器的介面如下，能夠很清楚的看到哪些失敗了，哪些有延遲。

clipboard

clipboard

然而，在運維過程中發現，我們不可能一直盯著複製監視器，甚至每天看一次都不可能，往往發生了同步故障，釋出訂閱停止了幾天才發現。因此，如何實時監控訂閱釋出變得非常急切，因為公司使用的是zabbix監控，所以希望把對sql server的監控也放在zabbix上。


（二）監控方法概述

zabbix提供了多種方法來監控資料庫，常見方法如下：

個人試了一下外掛監控方式，配置起來較為複雜，也不知道如何自定義新的監控項。最終選擇了zabbix+unixODBC來監控sql server資料庫。


（三）監控邏輯

sql server的釋出訂閱錯誤資訊儲存在 [distribution].[dbo].[MSrepl_errors] 表中，資訊如下：

clipboard

可以看到，如果釋出訂閱出現異常，每分鐘大約會報出3條錯誤，這裡我們簡單粗暴的監控錯誤次數：如果最近1小時出現了5次報錯，則認為複製存在異常，具體SQL語句如下：

select case when count(*) > 5 then 'REPL_ERROR' else 'NORMAL' END monitor from [distribution].[dbo].[MSrepl_errors] a where a.time > dateadd(hour,-1,GETDATE());


（四）具體實現過程

使用zabbix自帶的資料庫監控API，依賴於zabbix server的ODBC，而ODBC又依賴於freetds。下面我們依次安裝配置：freetds --> unixODBC --> zabbix監控

（4.1）安裝freetds

下載freetds http://www.freetds.org/software.html。

解壓freetds：

[root@zabbixserver ~]# ll
-rw-r--r--   1 root  root  3038783 Sep  9 11:33 freetds-1.2.4.tar.gz

[root@zabbixserver ~]# tar -xzvf freetds-1.2.4.tar.gz 

[root@zabbixserver freetds-1.2.4]# ls
aclocal.m4  CMakeLists.txt  config.status    COPYING.txt   freetds.spec.in  libtool       Makefile.am    NEWS.md    src          vms
AUTHORS.md  compile         config.sub       depcomp       include          locales.conf  Makefile.in    PWD        tds.dox      win32
autogen.sh  config.guess    configure        doc           INSTALL.md       ltmain.sh     misc           PWD.in     test-driver
BUGS.md     config.log      configure.ac     freetds.conf  install-sh       m4            missing        README.md  Thanks-1.0
ChangeLog   config.rpath    COPYING_LIB.txt  freetds.spec  interfaces       Makefile      mkinstalldirs  samples    TODO.md

安裝freetds:

# freetds需要使用gcc編譯，先安裝gcc
yum install gcc

[root@zabbixserver freetds-1.2.4]# pwd
/root/freetds-1.2.4

# 手動編譯安裝
./configure --prefix=/usr/local/freetds  --enable-msdblib
make
make install


（4.2）安裝unixODBC

yum install -y unixODBC unixODBC-devel

在/etc/odbcinst.ini中配置驅動程式，新增

[FreeTDS]                                       # unixodbc驅動名稱
Description=FreeTDS driver                      # 簡介    
Driver=/usr/local/freetds/lib/libtdsodbc.so     # 驅動所在位置，需要檢查該驅動是否存在，如果不存在，建議重新編譯安裝freetds

在/etc/odbc.ini中配置資料庫連線認證

[root@zabbixserver ~]# cat /etc/odbc.ini
[mssql71]   
Driver = FreeTDS
Server = 10.1.106.71 
PORT = 1433
TDS_Version = 8.0

[mssql72]   
Driver = FreeTDS
Server = 10.1.106.72 
PORT = 1433
TDS_Version = 8.0

使用unixODBC連線資料庫，連線資料庫的格式為：

isql db_str user password

db_str           ：資料庫連線字串，在/etc/odbc.ini檔案中定義
user              ：資料庫使用者名稱
pawwword     ：資料庫密碼


連線資料庫進行測試，可以正常訪問資料庫

[root@zabbixserver ~]# isql mssql71 sa zxc@1234
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

SQL> select getdate();
+------------------------+
| 2020-09-09 14:20:03.933|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>


（4.3）配置zabbix監控項和觸發器

配置zabbix監控項，在zabbix上開啟sql server主機，新增監控項：

  • 名稱：監控項的名稱，可以隨便填，為了好記，建議為monitor_repl
  • 型別：資料庫監控
  • 鍵值：db.odbc.select[str1,str2]：第1個字串隨便填，第2個字串是資料庫連線字串，是在zabbix伺服器的/etc/odbc.ini中定義的。
  • 使用者名稱：sql server的連線使用者
  • 密碼：sql server的連線密碼
  • SQL查詢：自定義sql語句，我們這裡監控釋出訂閱的SQL語句為
select case when count(*) > 5 then 'REPL_ERROR' else 'NORMAL' END monitor from [distribution].[dbo].[MSrepl_errors] a where a.time > dateadd(hour,-1,GETDATE());

clipboard

在完成監控項的建立之後，需要到最新資料裡面檢視是否收到了資料，這裡可以看到已經有資料收到了。

image

接下來建立觸發器：

clipboard

到zabbix的首頁檢視，已經捕獲到了複製錯誤

clipboard


（五）總結

通過zabbix+unixODBC，我們實現了自定義監控sql server資料庫的釋出訂閱功能，總結一下：

（1）如果要新增新的sql server資料庫，只需要在zabbix server伺服器上的/etc/odbc.ini裡面新增資料庫連線字串即可；

（2）如果要新增新的監控項，只需在zabbix web介面對應的sql server主機處新增監控項並輸入SQL語句即可。



【完】

相關文章