Alwayson架构下 服务器 各虚拟IP漂移监控告警的功能实现

我们知道,在SQL Server Alwayson 架构中,有多种虚拟IP,例如 WindowsCluster IP,ListenIP,角色高可用性IP(类似于侦听IP)。在某些条件下,例如系统故障,会触发虚拟IP的漂移,如何高效率、低延迟、更好地监控IP漂移情况,是我们DB的一个重要工作。

下面是我们的一个通过SQL Server 存储过程来实现的方案。

2.基本原理 

 周期性收集当前Server 上相应的IP地址,并与上个周期收集的结果比较判断,看那些IP发生了漂移变化。其主要流程图如下;

Alwayson架构下 服务器 各虚拟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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyzddz.html