SQL Server Alwayson架構下伺服器各虛擬IP漂移監控告警的功能實現

東山絮柳仔發表於2019-06-27

1.需求概括

我們知道，在SQL Server Alwayson 架構中，有多種虛擬IP，例如 WindowsCluster IP，ListenIP，角色高可用性IP（類似於偵聽IP）。在某些條件下，例如系統故障，會觸發虛擬IP的漂移，如何高效率、低延遲、更好地監控IP漂移情況，是我們DB的一個重要工作。

下面是我們的一個通過SQL Server 儲存過程來實現的方案。

2.基本原理

週期性收集當前Server 上相應的IP地址，並與上個週期收集的結果比較判斷，看那些IP發生了漂移變化。其主要流程圖如下;

3.程式碼實現

表 DBA_ServerIPDataBase_OverCheck，主要儲存當前（本收集週期） Server的資訊（主要是IP資訊、ServerName資訊等），其建立指令碼如下;

/****** Object:  Table [dbo].[DBA_ServerIPDataBase_OverCheck]    Script Date: 2019/6/27 16:01:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DBA_ServerIPDataBase_OverCheck](
    [LocalServerIP] [varchar](20) NULL,
    [ClusterName] [varchar](50) NULL,
    [ServerIP] [varchar](20) NULL,
    [ServerName] [varchar](100) NULL,
    [ServerFullName] [varchar](100) NULL,
    [ServerIPType] [varchar](20) NULL,
    [DataBaseName] [varchar](300) NULL,
    [DisabledFlag] [varchar](1) NULL,
    [CreateTime] [datetime] NULL,
    [CreateBy] [varchar](50) NULL,
    [ModifyTime] [datetime] NULL,
    [ModifyBy] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Windows叢集名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'ClusterName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'IP地址' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'ServerIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'計算機物件名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'ServerName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'計算機物件全稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'ServerFullName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'計算機物件全稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'ServerIPType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0實時有效，1第一次失效，2第二次失效，3第三次失效，4第四次失效，5第五次失效，徹底刪除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'DisabledFlag'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'CreateTime'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'建立人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBA_ServerIPDataBase_OverCheck', @level2type=N'COLUMN',@level2name=N'CreateBy'
GO

表 DBA_ServerIPDataBase_OverCheckOriginOrigin，主要儲存以前（前一個收集週期） Server的資訊，是用來比較變化的基準表，其表結構與表DBA_ServerIPDataBase_OverCheck相同，建立指令碼如下：

/****** Object:  Table [dbo].[DBA_ServerIPDataBase_OverCheckOriginOrigin]    Script Date: 2019/6/27 16:56:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DBA_ServerIPDataBase_OverCheckOriginOrigin](
    [LocalServerIP] [varchar](20) NULL,
    [ClusterName] [varchar](50) NULL,
    [ServerIP] [varchar](20) NULL,
    [ServerName] [varchar](100) NULL,
    [ServerFullName] [varchar](100) NULL,
    [ServerIPType] [varchar](20) NULL,
    [DataBaseName] [varchar](300) NULL,
    [DisabledFlag] [varchar](1) NULL,
    [CreateTime] [datetime] NULL,
    [CreateBy] [varchar](50) NULL,
    [ModifyTime] [datetime] NULL,
    [ModifyBy] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

具體的儲存過程 USP_DBA_ServerIPDataBase_OverCheck，用來實現收集、比較、告警等功能，程式碼實現如下：

/****** Object:  StoredProcedure [dbo].[USP_DBA_ServerIPDataBase_OverCheck]    Script Date: 2019/6/27 15:26:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[USP_DBA_ServerIPDataBase_OverCheck]
AS
BEGIN 

DECLARE @ipline VARCHAR(200) 
        DECLARE @ip VARCHAR(20)
        DECLARE @pingname VARCHAR(40)
        DECLARE @ServerName VARCHAR(100)
        DECLARE @ServerFullName VARCHAR(100)

        Declare @CurrDateTime nvarchar(20)
        Declare @PreDiffDateTime nvarchar(20) =''

        Set @CurrDateTime=CONVERT(VARCHAR(19),GETDATE(),120) 
 
        SET NOCOUNT ON

        Declare @ServerIP VARCHAR(20)
        SET @ServerName=@@SERVERNAME
        SET @ServerIP = CAST(CONNECTIONPROPERTY('local_net_address') AS varchar(20)) 

        ----Set @ServerIP ='XXX.XXX.XXX.XXX'   --如果不準確的話，請手動定義
            
        DELETE FROM DBA_ServerIPDataBase_OverCheck 
        WHERE CreateTime< CONVERT(VARCHAR(19),DATEADD( HH,-10,GETDATE()),120) 

        SET @ip = NULL 
        IF OBJECT_ID('tempdb..#tempserverip') IS NOT NULL
            DROP TABLE #tempserverip 
        CREATE TABLE #tempserverip ( ipline VARCHAR(200) ) 
        INSERT  #tempserverip
                EXEC master..xp_cmdshell 'ipconfig /all ' 
        
        INSERT  INTO DBA_ServerIPDataBase_OverCheck
                ( LocalServerIP,ServerIP,DisabledFlag,CreateTime,CreateBy
                )
                SELECT @ServerIP, p.ServerIP,'0'AS DisabledFlag,GETDATE() AS CreateTime ,@@SERVERNAME AS CreateBy
                FROM    dbo.DBA_ServerIPDataBase_OverCheck i
                        RIGHT JOIN ( SELECT RTRIM(LTRIM(REPLACE(SUBSTRING(ipline,
                                                              CHARINDEX(':',
                                                              ipline) + 1, 20),
                                                              '(首選)', ''))) AS 'ServerIP'
                                     FROM   #tempserverip
                                     WHERE  UPPER(ipline) LIKE '%IPv4 地址%'--這裡需要注意一下，系統不同這裡的匹配值就不同 
                                            AND UPPER(ipline) NOT LIKE '%192.168.%'
                                            AND UPPER(ipline) NOT LIKE '%169.254.%'
                                   ) p ON i.ServerIP = p.ServerIP
                WHERE   i.ServerIP IS NULL  --只關注漂來飄往資料

    --0002 -20180530 針對20180530持續告警問題，發現告警時間超過預期，進行優化。聚焦點再表DBA_ServerIPDataBase_OverCheck中的CreateTime欄位，精準更新  begin
    update i set i.CreateTime=getdate()
    FROM    dbo.DBA_ServerIPDataBase_OverCheck i
                        RIGHT JOIN ( SELECT RTRIM(LTRIM(REPLACE(SUBSTRING(ipline,
                                                              CHARINDEX(':',
                                                              ipline) + 1, 20),
                                                              '(首選)', ''))) AS 'ServerIP'
                                     FROM   #tempserverip
                                     WHERE  UPPER(ipline) LIKE '%IPv4 地址%'--這裡需要注意一下，系統不同這裡的匹配值就不同 
                                            AND UPPER(ipline) NOT LIKE '%192.168.%'
                                            AND UPPER(ipline) NOT LIKE '%169.254.%'
                                   ) p ON i.ServerIP = p.ServerIP

    ------
        DECLARE IP CURSOR
        FOR
            SELECT  ServerIP
            FROM    dbo.DBA_ServerIPDataBase_OverCheck WHERE DisabledFlag IS NOT NULL AND DisabledFlag=0
        OPEN IP
        FETCH NEXT FROM IP INTO @ip
        WHILE @@FETCH_STATUS = 0
            BEGIN
                --SET @pingname = 'ping -a ' + @ip
                SET @pingname = 'ping -a ' + @ip + ' -n 1 -l 10'

                TRUNCATE TABLE #tempserverip
                INSERT  #tempserverip
                        EXEC master..xp_cmdshell @pingname

                SELECT  @ServerName = REPLACE(RTRIM(LTRIM(SUBSTRING(ipline, 8,
                                                              CHARINDEX('[',
                                                              ipline) - 8))),
                                              '.XXXXXX.com', '') ,-----加域的電腦，計算機名字可能帶有域名，請根據實際情況替換
                        @ServerFullName = RTRIM(LTRIM(SUBSTRING(ipline, 8,
                                                              CHARINDEX('[',
                                                              ipline) - 8)))
                FROM    #tempserverip
                WHERE   ipline LIKE '%正在 Ping%'

                UPDATE  dbo.DBA_ServerIPDataBase_OverCheck
                SET     ServerName = @ServerName ,
                        ServerFullName = @ServerFullName
                WHERE   ServerIP = @ip

                FETCH NEXT FROM IP INTO @ip
            END
        CLOSE IP 
        DEALLOCATE IP

        UPDATE  dbo.DBA_ServerIPDataBase_OverCheck
        SET     ServerIPType = 'Localhost'
        WHERE   ServerName = @@SERVERNAME


        if OBJECT_ID('sys.availability_group_listener_ip_addresses') IS NOT NULL 
        begin
          update a set a.ServerIPType='ListenIP'
          from DBA_ServerIPDataBase_OverCheck a inner join sys.availability_group_listener_ip_addresses b
          on a.ServerIP=b.ip_address
        end 

        IF OBJECT_ID('sys.dm_hadr_cluster') IS NOT NULL
            BEGIN
                UPDATE dbo.DBA_ServerIPDataBase_OverCheck SET ClusterName=(SELECT cluster_name FROM sys.dm_hadr_cluster)
                UPDATE dbo.DBA_ServerIPDataBase_OverCheck SET ServerIPType='WindowsCluster' WHERE ServerName=(SELECT cluster_name FROM sys.dm_hadr_cluster)
            END
        IF OBJECT_ID('sys.dm_hadr_cluster') IS NULL
            BEGIN
                UPDATE dbo.DBA_ServerIPDataBase_OverCheck SET ClusterName='Not Cluster'
            END
--0002 -20180530  針對20180530持續告警問題，發現告警時間超過預期，進行優化。聚焦點再表DBA_ServerIPDataBase_OverCheck中的CreateTime欄位，精準更新  begin
        UPDATE DBA_ServerIPDataBase_OverCheck SET  DataBaseName=STUFF(
            (SELECT ',' + name FROM sys.databases  
                               WHERE name not in ('master'
                                 ,'tempdb','model','msdb','ReportServer','ReportServerTempDB','distribution')
                               for xml path('')   ),
                            1,1,'')
-----002 end 
        --the mail alarm 
           declare @SQL as varchar(200)
        declare @Subject as varchar(200)=N'DB SERVER IP 有漂移,請檢查確認！'
        declare @Body as nvarchar(max)=''

        select @PreDiffDateTime= CreateTime  from DBA_ServerIPDataBase_OverCheckOriginOrigin
        order by CreateTime
        
        SELECT  TOP 0 A.*  into #temp_DBA_ServerIPDataBase_OverCheck_diff FROM DBA_ServerIPDataBase_OverCheck A INNER JOIN DBA_ServerIPDataBase_OverCheckOriginOrigin  B
         ON A.ServerIP =B.ServerIP  

        IF EXISTS(SELECT * FROM DBA_ServerIPDataBase_OverCheck A LEFT JOIN DBA_ServerIPDataBase_OverCheckOriginOrigin  B ON A.ServerIP =B.ServerIP  WHERE B.ServerIP IS NULL)
        BEGIN

           INSERT  INTO  #temp_DBA_ServerIPDataBase_OverCheck_diff
           SELECT A.*  FROM DBA_ServerIPDataBase_OverCheck A LEFT JOIN DBA_ServerIPDataBase_OverCheckOriginOrigin  B ON A.ServerIP =B.ServerIP  WHERE B.ServerIP IS NULL

            if exists(    select * from #temp_DBA_ServerIPDataBase_OverCheck_diff  ) 
            begin
            set @Body= N'<html>' 
                    + N'<style type="text/css">' 
                    + N' td {border:solid #9ec9ec;  border-width:1px 1px 1px 1px; padding:4px 0px;}' 
                    + N' table {border:1px solid #9ec9ec;width:80%;border-width:0px 0px 0px 0px;font-size:14px}' 
                    + N'</style>'
                    + N'<H1 style="color:#FF0000;font-size:14px"></H1>' 
            SET @Body=@Body+'<body><font color=#0000CC>Dear All,<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;此List是監控到過去10 MIn Server IP 異常情況,'+@ServerIP +'伺服器有新IP建立宣告（新增）， 請及時Check。具體資料如下：;<br><br><table>' 
            SET @Body=@Body+'<tr bgcolor=#FFFF00 align="center"><td>ClusterName</td><td>ServerIP</td><td>ServerName</td><td>ServerIPType</td><td>DatabaseName</td><td>DisabledFlag</td><td>當前時間</td><td>差異采樣時間</td></tr>'    
            SELECT @Body=@Body+'<tr><td>'+ClusterName+'</td><td>'+ServerIP+'</td><td>'+ServerName+'</td><td>'+ServerIPType+'</td><td>'+DatabaseName+'</td><td>'+ DisabledFlag+'</td><td>'+@CurrDateTime+'</td><td>'+ @PreDiffDateTime+'</td></tr>'
             from #temp_DBA_ServerIPDataBase_OverCheck_diff     

             SET @Body = @Body +'</table><font color=#0000CC><br><br>DBA<br>Best wishes</body><html>'
            end 
       END


        IF EXISTS(SELECT * FROM DBA_ServerIPDataBase_OverCheck A RIGHT JOIN DBA_ServerIPDataBase_OverCheckOriginOrigin  B ON A.ServerIP =B.ServerIP  WHERE A.ServerIP IS NULL)
        BEGIN

           DELETE FROM #temp_DBA_ServerIPDataBase_OverCheck_diff
           INSERT  INTO  #temp_DBA_ServerIPDataBase_OverCheck_diff
           SELECT A.*  FROM DBA_ServerIPDataBase_OverCheck A RIGHT JOIN DBA_ServerIPDataBase_OverCheckOriginOrigin  B ON A.ServerIP =B.ServerIP  WHERE A.ServerIP IS NULL

            if exists(    select * from #temp_DBA_ServerIPDataBase_OverCheck_diff  ) 
            begin
            set @Body= N'<html>' 
                    + N'<style type="text/css">' 
                    + N' td {border:solid #9ec9ec;  border-width:1px 1px 1px 1px; padding:4px 0px;}' 
                    + N' table {border:1px solid #9ec9ec;width:80%;border-width:0px 0px 0px 0px;font-size:14px}' 
                    + N'</style>'
                    + N'<H1 style="color:#FF0000;font-size:14px"></H1>' 
            SET @Body=@Body+'<body><font color=#0000CC>Dear All,<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;此List是監控到過去10 MIn Server IP 異常情況,'+@ServerIP +'伺服器有IP漂移（消減）， 請及時Check。具體資料如下：;<br><br><table>' 
            SET @Body=@Body+'<tr bgcolor=#FFFF00 align="center"><td>ClusterName</td><td>ServerIP</td><td>ServerName</td><td>ServerIPType</td><td>DatabaseName</td><td>DisabledFlag</td><td>當前時間</td><td>差異采樣時間</td></tr>'    
            SELECT @Body=@Body+'<tr><td>'+ClusterName+'</td><td>'+ServerIP+'</td><td>'+ServerName+'</td><td>'+ServerIPType+'</td><td>'+DatabaseName+'</td><td>'+ DisabledFlag+'</td><td>'+@CurrDateTime+'</td><td>'+ @PreDiffDateTime+'</td></tr>'
             from #temp_DBA_ServerIPDataBase_OverCheck_diff     

             SET @Body = @Body +'</table><font color=#0000CC><br><br>DBA<br>Best wishes</body><html>'
            end 

       END  
            SET @BODY=REPLACE(@BODY,'''','')
            IF REPLACE(@BODY,' ','')<>''
                BEGIN

                    Declare @AllEmailToAddress varchar(3000)=''
                    Declare @AllEmailCcAddress varchar(3000)=''
                    Select @AllEmailToAddress='hanmeimei;xiaoming;lilei'

                    Select @AllEmailCcAddress='laoban'


                    exec msdb..sp_send_dbmail @profile_name =  'AutoMail'               -- profile 名稱，請檢查此引數，根據實際情況進行替換 
                     ,@recipients   =  @AllEmailToAddress         -- 收件人郵箱 
                     ,@copy_recipients=@AllEmailCcAddress
                     ,@subject      =  @Subject -- 郵件標題 
                     ,@body         =  @BODY            -- 郵件內容 
                     ,@body_format  =  'HTML'                      -- 郵件格式 
                     ,@file_attachments=''
                     ,@importance = 'HIGH' -- varchar(10) 告警級別
                END


        ------------------新增立即插入-----------

        insert into [dbo].[DBA_ServerIPDataBase_OverCheckOriginOrigin]
        select a.* from  DBA_ServerIPDataBase_OverCheck a left join DBA_ServerIPDataBase_OverCheckOriginOrigin b
        on a.ServerIP=b.ServerIP
        where b.ServerIP is null

        ---漂移後，指定時間段後直接刪除過時資料,暫定八個小時。【即如果有漂移（減少），減少的IP資訊，則在指定時間後，刪除。】

        delete b
        from  DBA_ServerIPDataBase_OverCheck a right join DBA_ServerIPDataBase_OverCheckOriginOrigin b
        on a.ServerIP=b.ServerIP
        where a.ServerIP is null
        and b.CreateTime< CONVERT(VARCHAR(19),DATEADD( HH,-3,GETDATE()),120)

       -----將資料插入到遠端Server DB中，遠端Server有一個SP過程，用來判斷漂移前/後DB是否有變化。遠端的SP主要是依據 LocalServerIP 和 ServerIP 對應關係變化情況來判斷。這段程式碼省略，下次再描述

        SET NOCOUNT OFF 

End


GO

4.功能實現

例如當伺服器有新IP建立宣告（新增）時，其發出的告警郵件如下：

本文版權歸作者所有，未經作者同意不得轉載,謝謝配合！！！

SQL Server Alwayson架構下伺服器各虛擬IP漂移監控告警的功能實現 -2（虛擬IP視角）
2019-06-28
SQLServer架構伺服器
VRRP虛IP漂移
2019-02-25
VR
通過Python實現對SQL Server 資料檔案大小的監控告警
2021-04-22
PythonSQLServer
Zabbix監控虛擬機器服務-告警與自動恢復
2021-01-22
虛擬機
Cable：360實現的新虛擬網路架構
2019-03-27
架構
UAVStack的慢SQL資料庫監控功能及其實現
2019-08-16
SQL資料庫
監控 SQL Server 的執行狀況
2019-05-07
SQLServer
虛IP解決程式連只讀伺服器故障漂移
2019-07-31
伺服器
對SQL Server 2014 alwayson 架構的資料庫在主節點加新的datafile檔案
2018-12-13
SQLServer架構資料庫
SQL Server AlwaysOn的Oracle等價技術
2019-08-18
SQLServerOracle
[AlwaysOn] 建立SQL Server AlwaysOn高可用性組T-SQL語法
2019-08-11
SQLServer
prometheus監控+alertmanager告警
2024-03-07
Prometheus
使用zabbix監控sql server的釋出訂閱
2020-09-09
SQLServer
SQL Server 2016 + AlwaysOn 無域叢集
2019-05-21
SQLServer
如何在SQL Server中實現 Limit m,n 的功能
2021-09-09
SQLServerMIT
雲原生架構日誌監控最佳實踐
2022-02-28
架構
TiDB監控實現--存活監控
2019-08-30
TiDB
虛擬網路監控進階之路-DeepFlow
2020-12-15
Zabbix如何監控Oracle的告警日誌
2024-09-21
Oracle
如何在伺服器上新增虛擬IP？
2021-06-29
伺服器
獨家揭祕：SQL Server AlwaysOn在阿里雲的突破
2022-02-28
SQLServer阿里
SQL Server跨庫跨伺服器訪問實現
2019-06-19
SQLServer伺服器
構建Spring Boot應用的微服務服務監控與告警
2024-08-23
Spring Boot微服務
分散式架構的監控與指標
2023-12-30
分散式架構指標
win10虛擬機器監控程式怎麼用_win10vmware虛擬機器監控程式使用教程
2020-06-17
Win10虛擬機
維護SQL Server虛擬機器的高可用性NJ
2022-03-22
SQLServer虛擬機
伺服器虛擬化開源技術主流架構之爭
2019-04-20
伺服器架構
監控系統告警指令碼集合
2021-05-08
指令碼
starrocks基於prometheus實現監控告警
2023-04-26
Prometheus
容器叢集監控系統架構如何對症下藥？
2023-03-24
架構
SQL Server底層架構技術對比
2023-04-24
SQLServer架構
vivo 服務端監控架構設計與實踐
2022-02-21
服務端架構
vivo 容器叢集監控系統架構與實踐
2022-06-20
架構
京東架構師解析URL監控
2018-05-02
架構
微服務架構之「監控系統」
2019-05-21
微服務架構
微服務架構之「呼叫鏈監控」
2019-05-28
微服務架構
zanePerfor監控系統在高流量專案下的架構配置建議實踐說明
2019-04-09
架構
虛擬機器效能監控和故障處理工具
2018-04-20
虛擬機

SQL Server Alwayson架構下 伺服器 各虛擬IP漂移監控告警的功能實現

1.需求概括

2.基本原理

3.程式碼實現

4.功能實現

相關文章

SQL Server Alwayson架構下伺服器各虛擬IP漂移監控告警的功能實現