转载

用脚本定时监控SQL Server主从一致性

首先说一下我们的环境

我们使用的是事务复制，复制是单向的，主服务器和从服务器都在同一个机房，当然不同机房也可以，只需要改一下IP和端口

下面的脚本在我们的 SQLServer 2008 上已经应用，暂时没有发现问题，当然，如果大家使用过程中有发现问题欢迎向我反馈o(∩_∩)o

首先，我们为什麽要校验呢？

我们知道因为网络延迟，或者从库有写入的情况（当然一般我们在订阅端会设置为 db_datareader ，不允许写）会造成主从数据不一致的情况

无论是SQL Server还是MySQL，所以我们就需要进行数据校验，以便大概知道我们的数据什么时候开始不一致

而校验是不可能每时每刻都做校验的，因为需要读取全表数据，对性能会有影响

下面的过程只需要远程上去从服务器，也就是订阅服务器上面做就可以了，完全不需要远程主服务器也就是发布服务器

线上我们做复制的表都比较小，数据量也不大

我们做复制的最大一个表是600MB的表

600MB的表校验时间是1 分钟，那么可以推算 50000MB（50GB）的表大概80分钟，至于这个时间根据不同的环境硬件和软件所需的校验时间可能会有所不同

我们使用的服务器是DELL R720

这个脚本原理很简单，就是利用SQL Server的job每天定时执行来获取主从上面的数据，从而判断主从数据是否一致

废话不说了，上脚本

1、在订阅端执行查看哪些表做了复制

首先你需要知道你现在哪些表是做了复制的，当然有些人会到发布服务器上去看，点击几下按钮，其实在订阅端是有视图可以看出

当前哪些表做了复制的

--在订阅端执行 use [Task] -- 要复制的库 GO    select article from dbo.MSreplication_objects group by article GO

有9个表做了复制

用脚本定时监控SQL Server主从一致性

2、建立linkedserver

用脚本定时监控SQL Server主从一致性 View Code

建立linkedserver的目的是连接到发布服务器获取数据，如果是不同机房，那么只需要改IP为公网IP和端口就可以了

3、在订阅服务器上建表

在订阅端建立两个表，这两个表的作用是保存校验数据

我说一下 Repl_NeedMonitor 表的need_monitor 字段，如果你有一天不想监控某个表了，你需要将那个表的need_monitor 字段改为0就可以了

Repl_NeedMonitor表需要预先插入你要监控的表，在这里第一步的“在订阅端执行查看哪些表做了复制”为了这一步做铺垫的

执行完第一步，你知道有哪些表需要做监控，然后插入数据到 Repl_NeedMonitor 表就可以了

---建表 USE [Task]  --★Do   GO --要监控的表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Repl_NeedMonitor]') AND type in (N'U'))   BEGIN       DROP TABLE [dbo].[Repl_NeedMonitor]   END CREATE TABLE [dbo].[Repl_NeedMonitor]     (       id INT IDENTITY(1, 1)        PRIMARY KEY ,       tbname NVARCHAR(400) UNIQUE ,       need_monitor INT ,  --是否需要监控       update_time DATETIME     ) --监控情况表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Repl_MonitorStatus]') AND type in (N'U'))   BEGIN       DROP TABLE [dbo].[Repl_MonitorStatus]   END CREATE TABLE [dbo].[Repl_MonitorStatus]     (       id INT IDENTITY(1, 1)        PRIMARY KEY ,       tbname NVARCHAR(500) ,       is_Consistency INT ,  -- 一致为1,  不一致为0       master_record BIGINT , --主库表记录数       slave_record BIGINT ,  --从库表记录数       update_time DATETIME  --更新时间     ) --插入要监控的表数据 INSERT INTO [Repl_NeedMonitor]   --★Do     ( [tbname] ,     [need_monitor] ,     [update_time]   ) VALUES  ( N'Site' , -- tbname - nvarchar(500)     1 , -- need_monitor - int     GETDATE()  -- update_time - datetime   ) SELECT * FROM [Repl_NeedMonitor]

Repl_NeedMonitor表

用脚本定时监控SQL Server主从一致性

4、创建执行数据一致性校验存储过程

用脚本定时监控SQL Server主从一致性 View Code

注意：脚本中凡是有--★Do 的都是你需要结合自己情况去修改的变量

这个脚本的原理很简单，是读取主库表的记录数，然后读取从库表的记录数，然后进行比较

当两边的记录数是一致的，那么再用EXCEPT 减法归零的方法比较两边表数据的内容是否一致

如果也是一致的，那么两边表的数据就是一致的，否则就是不一致的，这里有一个效率问题，就是首先判断记录数是否一致

如果不一致就没有必要再去比较内容一致了，最后把数据插入到表 Repl_MonitorStatus

5、创建扫描要监控的表存储过程

这里用游标检查哪一个表需要进行校验，然后调用 usp_ReplConsistencyCheck 存储过程进行校验

用脚本定时监控SQL Server主从一致性 View Code

6、创建定时校验复制主从数据一致性JOB

每隔 13个小时 调用一次存储过程，当然这个调用频率可以结合实际情况进行修改

用脚本定时监控SQL Server主从一致性 View Code

看一下执行结果

SELECT * FROM [Repl_MonitorStatus]

用脚本定时监控SQL Server主从一致性

从作业历史里看一下总执行时间

用脚本定时监控SQL Server主从一致性

从执行结果里面也可以看到执行时间

用脚本定时监控SQL Server主从一致性

脚本缺陷

这个脚本是有缺陷的，如果你是复制表里面的几个字段而不是 整表复制 的话，那么他就不能比较两边的一致性了

情况一：只复制表里的几个字段，并只需要监控一张表

解决办法：在第一个存储过程里面《执行数据一致性校验》存储过程修改一下下面的代码只select复制的字段，而不是select *

--显示订阅表里面有的记录不在发布表里面的记录有多少 如果不为0 即数据不一致                 SET @SQL = 'SELECT  COUNT(*) FROM  ( SELECT  字段1，字段2。。。  FROM [dbo].[' + @tbname + ']' --发布表                     + ' EXCEPT ' + 'SELECT  字段1，字段2。。。 FROM  [' + @LinkServer + '].'                     + '[' + @DBName + '].' + '[dbo].[' + @tbname + ']' --订阅表                     + ') AS T;'