1.需求描述
我們知道Windows Cluster 都是多節點的，當虛擬IP漂移的時候，一般都是從一個節點漂移到另外一個節點。如果可以及時捕捉到舊節點資訊是什麼、新節點資訊是什麼對我們提供高可用的資料庫服務很重要，只有捕捉到這些資訊後才可以進一步檢查相應的Job、賬號，甚至是調整相應的應用服務等。
2.基本原理
在上一節《SQL Server Alwayson架構下 伺服器 各虛擬IP漂移監控告警的功能實現 -1（伺服器視角）》，我們實現了針對某一個節點的監控即這個節點是否有虛擬IP新增或消減。接下來我們需要實現的是針對一個叢集（或是Alwayson）漂移情況的監控，例如監控虛擬IP是從哪兒來，又要到哪兒去。這個功能的實現依賴上一節的功能和資料。
實現方式是將各個單節點的資料傳送儲存到一臺遠端伺服器上，然後根據叢集資訊進行聚合判斷就可以了。
其聚合判斷功能實現：根據指定時間段內，同一個虛擬IP是否對應兩個ServerIP，來判讀是否發生了漂移。如果發生了漂移，進而根據相應資料的時間先後判斷從哪兒來，到哪兒去。
3.程式碼實現
中央伺服器中表 DBA_ServerIPDataBase_OverCheck 的建立指令碼。主要儲存了指定時間段內（例如 6H），各個Server節點的各種IP資料。
/****** Object: Table [dbo].[DBA_ServerIPDataBase_OverCheck] Script Date: 2019/6/28 11:24:11 ******/ 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](550) 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_ServerIPDataBaseCheck_Base，儲存比較的資料。
/****** Object: Table [dbo].[DBA_ServerIPDataBaseCheck_Base] Script Date: 2019/6/28 13:33:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBA_ServerIPDataBaseCheck_Base]( [LocalServerIP] [varchar](20) NULL, [ServerIP] [varchar](20) NULL, [MaxCurrDateTime] [datetime] NULL, [DataBaseName] [varchar](550) NULL, [IPFailOverResult] [varchar](50) NULL, [DBFailOverResult] [varchar](250) NULL, [CreateTime] [nvarchar](20) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
在各個DB Server上原來的儲存過程 USP_DBA_ServerIPDataBase_OverCheck 的尾部，新增以下程式碼，實現的功能是將資料彙總到中央伺服器的資料庫中，用來聚合判斷。
（USP_DBA_ServerIPDataBase_OverCheck的更多內容，請參照SQL Server Alwayson架構下 伺服器 各虛擬IP漂移監控告警的功能實現 -1（伺服器視角）
-----將資料插入到遠端Server DB中，用來判斷漂移前/後DB是否有變化 INSERT INTO [中心伺服器IP].中心資料庫.dbo.[DBA_ServerIPDataBase_OverCheck]([LocalServerIP] ,[ClusterName] ,[ServerIP] ,[ServerName] ,[ServerFullName] ,[ServerIPType] ,[DataBaseName] ,[DisabledFlag] ,[CreateTime],[CreateBy],[ModifyTime] ,[ModifyBy]) SELECT [LocalServerIP] ,[ClusterName] ,[ServerIP] ,[ServerName] ,[ServerFullName] ,[ServerIPType] ,[DataBaseName] ,[DisabledFlag] ,[CreateTime] ,[CreateBy] ,[ModifyTime] ,[ModifyBy] FROM [dbo].[DBA_ServerIPDataBase_OverCheck]
聚合判斷、發郵件的儲存過程為 USP_DBA_ServerIPDataBase_OverCheck_Alarm，其具體的指令碼如下：
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[USP_DBA_ServerIPDataBase_OverCheck_Alarm] AS BEGIN SET NOCOUNT ON DECLARE @Localip VARCHAR(20) DECLARE @ip VARCHAR(20) DECLARE @ServerName VARCHAR(100) DECLARE @ServerFullName VARCHAR(100) Declare @CurrDateTime nvarchar(20) Declare @PreDiffDateTime nvarchar(20) ='' Set @CurrDateTime=CONVERT(VARCHAR(19),GETDATE(),120) Declare @MaxCurrDateTime datetime -------刪除歷史資料，暫無歸檔 DELETE FROM DBA_ServerIPDataBase_OverCheck WHERE CreateTime<CONVERT(VARCHAR(19),DATEADD( HH,-6,GETDATE()),120) DELETE FROM DBA_ServerIPDataBaseCheck_Base -----查詢出一僕二主的ServerIP select ServerIP,count(distinct LocalServerIP) As 'Qty' into #temp_ServerIP from [dbo].[DBA_ServerIPDataBase_OverCheck] group by ServerIP having count(distinct LocalServerIP)>1 --------查詢出不同的載體 if exists(select 1 from #temp_ServerIP ) begin ----找出對應關係 select distinct a.LocalServerIP,a.ServerIP into #temp_diffIP from DBA_ServerIPDataBase_OverCheck a inner join #temp_ServerIP b on a.ServerIP=b.ServerIP Declare DBName Cursor for Select LocalServerIP, ServerIP from #temp_diffIP Open DBName Fetch next from DBName INTO @Localip, @ip While (@@fetch_status=0) Begin select @MaxCurrDateTime= max(CreateTime) from DBA_ServerIPDataBase_OverCheck where LocalServerIP =@Localip and ServerIP =@ip insert into DBA_ServerIPDataBaseCheck_Base ([LocalServerIP],[ServerIP],MaxCurrDateTime,CreateTime) values(@Localip,@ip,@MaxCurrDateTime,@CurrDateTime) fetch next from DBName INTO @Localip, @ip END --select * from DBA_ServerIPDataBase_OverCheck a update a set a.IPFailOverResult='IP 漂移進來' from DBA_ServerIPDataBaseCheck_Base a where exists(select * from DBA_ServerIPDataBaseCheck_Base b where a.ServerIP=b.ServerIP and a.MaxCurrDateTime>b.MaxCurrDateTime) update a set a.IPFailOverResult='IP 漂移出來' from DBA_ServerIPDataBaseCheck_Base a where exists(select * from DBA_ServerIPDataBaseCheck_Base b where a.ServerIP=b.ServerIP and a.MaxCurrDateTime<b.MaxCurrDateTime) ---------------------增加資料庫 --select * update a set a.DataBaseName=b.DataBaseName from DBA_ServerIPDataBaseCheck_Base a inner join DBA_ServerIPDataBase_OverCheck b on a.ServerIP=b.ServerIP and a.LocalServerIP = b.LocalServerIP and a.MaxCurrDateTime =b.CreateTime -------郵件告警 declare @SQL as varchar(200) declare @Subject as varchar(200)=N'DB SERVER IP ABNORMAL,PLEASE CHECK！' declare @Body as nvarchar(max)='' 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> 此List是監控到過去10 MIn 有DB Server IP 出現漂移情況,請及時Check。'+'<br>' +' 具體資料如下：;<br><br><table>' SET @Body=@Body+'<tr bgcolor=#ffaa11 align="center"><td colspan="5">IP漂移後的宿主資訊</td><td colspan="5">IP漂移前的宿主資訊</td></tr>' SET @Body=@Body+'<tr bgcolor=#FFFF00 align="center"><td>LocalIP</td><td>ServerIP</td><td>DataBaseName</td><td>IPOverResult</td><td>CheckDateTime</td> <td>LocalIP</td><td>ServerIP</td><td>DataBaseName</td><td>IPOverResult</td><td>CheckDateTime</td></tr>' SELECT @Body=@Body+'<tr><td>'+a.LocalServerIP+'</td><td>'+a.ServerIP+'</td><td>'+isnull(a.DataBaseName,'')+'</td><td>'+a.IPFailOverResult+'</td><td>'+ CONVERT(VARCHAR(19),a.MaxCurrDateTime,120)+'</td> <td>'+b.LocalServerIP+'</td><td>'+ b.ServerIP+'</td><td>'+isnull(b.DataBaseName,'')+'</td><td>'+b.IPFailOverResult+'</td><td>'+ CONVERT(VARCHAR(19),b.MaxCurrDateTime,120)+'</td></tr>' from DBA_ServerIPDataBaseCheck_Base a left join DBA_ServerIPDataBaseCheck_Base b on a.ServerIP=b.ServerIP where a.IPFailOverResult='IP 漂移進來' and b.IPFailOverResult='IP 漂移出來' SET @Body = @Body +'</table><font color=#0000CC><br><br>DBA<br>Best wishes</body><html>' 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 end END
4.功能實現
例如下面的告警郵件，告訴監控人員：虛擬IP（169.XXX.XXX.247） 從 Server --169.XXX.XXX.44 轉移到了Server---169.XXX.XXX.43，並且包含了漂移前後Server上資料庫的名字（有可能資料庫多少不完全一致）。
本文版權歸作者所有，未經作者同意不得轉載,謝謝配合！！！
本文版權歸作者所有，未經作者同意不得轉載,謝謝配合！！！
本文版權歸作者所有，未經作者同意不得轉載,謝謝配合！！！