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上数据库的名字（有可能数据库多少不完全一致）。
本文版权归作者所有，未经作者同意不得转载,谢谢配合！！！
本文版权归作者所有，未经作者同意不得转载,谢谢配合！！！
本文版权归作者所有，未经作者同意不得转载,谢谢配合！！！