用脚本定时监控SQL Server主从一致性
用脚本定时监控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 -- 要复制的库
GO
select article from dbo.MSreplication_objects
group by article
GO
有9个表做了复制
2、建立linkedserver
--建立linkedserver
USE
GO
DECLARE @IP NVARCHAR(MAX)
DECLARE @Login NVARCHAR(MAX)
DECLARE @PWD NVARCHAR(MAX)
SET @Login = N'xxx' --★Do
SET @PWD = N'xxx'--★Do
SET@IP ='192.168.100.6,1433'
EXEC master.dbo.sp_addlinkedserver @server = @IP,@srvproduct = N'SQL Server'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation compatible', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'data access', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'dist',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'pub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'rpc out',@optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP, @optname = N'sub',@optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'connect timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'collation name', @optvalue = NULL
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'lazy schema validation', @optvalue = N'false'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'query timeout', @optvalue = N'0'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'use remote collation', @optvalue = N'true'
EXEC master.dbo.sp_serveroption @server = @IP,@optname = N'remote proc transaction promotion',@optvalue = N'true'
USE
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = @IP,
@locallogin = NULL,
@useself = N'False',
@rmtuser = @Login,
@rmtpassword = @PWD
View Code 建立linkedserver的目的是连接到发布服务器获取数据，如果是不同机房，那么只需要改IP为公网IP和端口就可以了
3、在订阅服务器上建表
在订阅端建立两个表，这两个表的作用是保存校验数据
我说一下Repl_NeedMonitor表的need_monitor 字段，如果你有一天不想监控某个表了，你需要将那个表的need_monitor 字段改为0就可以了
Repl_NeedMonitor表需要预先插入你要监控的表，在这里第一步的“在订阅端执行查看哪些表做了复制”为了这一步做铺垫的
执行完第一步，你知道有哪些表需要做监控，然后插入数据到Repl_NeedMonitor表就可以了
---建表
USE --★Do
GO
--要监控的表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'U'))
BEGIN
DROP TABLE .
END
CREATE TABLE .
(
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'.') AND type in (N'U'))
BEGIN
DROP TABLE .
END
CREATE TABLE .
(
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 --★Do
( ,
,
)
VALUES( N'Site' , -- tbname - nvarchar(500)
1 , -- need_monitor - int
GETDATE()-- update_time - datetime
)
SELECT * FROM
Repl_NeedMonitor表
4、创建执行数据一致性校验存储过程
USE
GO
/****** Object:StoredProcedure . Script Date: 03/19/2015 15:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE . ( @tbname NVARCHAR(500) )
AS
BEGIN
DECLARE @is_Consistency INT--是否一致
DECLARE @master_record INT
DECLARE @slave_record INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @LinkServer NVARCHAR(100)
DECLARE @DBName NVARCHAR(100)
DECLARE @SQLCountMaster NVARCHAR(MAX)
DECLARE @SQLCountSlave NVARCHAR(MAX)
SET @LinkServer = '192.168.100.6,1433'--★Do
SET @DBName = 'Task'--★Do
--获取主库表的记录数
SET @SQLCountMaster = '
SELECT TOP 1sysindx. FROM ' + '[' + @LinkServer + '].' + '['
+ @DBName + '].' + '. AS sysobj
INNER JOIN [' + @LinkServer + '].' + '[' + @DBName + '].'
+ '. AS sysindx ON sysobj. = sysindx.ANDsysobj. = ''u'' AND sysobj. ='
+ '''' + @tbname + ''''
--获取从库表的记录数
SET @SQLCountSlave = '
SELECT TOP 1sysindx. FROM ' + '[' + @DBName + '].'
+ '. AS sysobj
INNER JOIN [' + @DBName + '].'
+ '. AS sysindx ON sysobj. = sysindx. AND sysobj. = ''u''AND sysobj. ='
+ '''' + @tbname + ''''
--创建临时表保存临时结果
IF EXISTS ( SELECT* FROM ..sysobjects WHERE id = OBJECT_ID('tempdb..#tmptb1') )
BEGIN
DROP TABLE .[#tmptb1]
END
IF EXISTS ( SELECT* FROM ..sysobjectsWHERE id = OBJECT_ID('tempdb..#tmptb2') )
BEGIN
DROP TABLE .[#tmptb2]
END
IF EXISTS ( SELECT*FROM ..sysobjects WHERE id = OBJECT_ID('tempdb..#tmptb3') )
BEGIN
DROP TABLE .[#tmptb3]
END
CREATE TABLE [#tmptb1] ( INT )-- 一致为1,不一致为0
CREATE TABLE [#tmptb2]( BIGINT)--主库记录数
CREATE TABLE [#tmptb3]( BIGINT) --从库记录数
INSERTINTO [#tmptb2]( ) EXEC ( @SQLCountMaster)
INSERTINTO [#tmptb3]( ) EXEC ( @SQLCountSlave)
SELECT TOP ( 1 ) @master_record = FROM [#tmptb2]
SELECT TOP ( 1 ) @slave_record = FROM [#tmptb3]
IF ( @master_record@slave_record )
BEGIN
SET @is_Consistency = 0
END
ELSE
BEGIN
--显示订阅表里面有的记录不在发布表里面的记录有多少 如果不为0 即数据不一致
SET @SQL = 'SELECTCOUNT(*) FROM( SELECT*FROM .[' + @tbname + ']' --发布表
+ ' EXCEPT ' + 'SELECT * FROM[' + @LinkServer + '].'
+ '[' + @DBName + '].' + '.[' + @tbname + ']' --订阅表
+ ') AS T;'
INSERTINTO [#tmptb1]() EXEC (@SQL)
IF ( SELECT TOP 1 FROM [#tmptb1])0
BEGIN
SET @is_Consistency = 0
END
ELSE
BEGIN
SET @is_Consistency = 1
END
END
INSERTINTO
( ,
,
,
,
)
SELECT@tbname ,
@is_Consistency ,
@master_record ,
@slave_record ,
GETDATE()
END
View Code 注意：脚本中凡是有--★Do 的都是你需要结合自己情况去修改的变量
这个脚本的原理很简单，是读取主库表的记录数，然后读取从库表的记录数，然后进行比较
当两边的记录数是一致的，那么再用EXCEPT减法归零的方法比较两边表数据的内容是否一致
如果也是一致的，那么两边表的数据就是一致的，否则就是不一致的，这里有一个效率问题，就是首先判断记录数是否一致
如果不一致就没有必要再去比较内容一致了，最后把数据插入到表Repl_MonitorStatus
5、创建扫描要监控的表存储过程
这里用游标检查哪一个表需要进行校验，然后调用usp_ReplConsistencyCheck存储过程进行校验
USE --★Do
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATEPROCEDURE .
AS
BEGIN
DECLARE @TBNAME NVARCHAR(100)
DECLARE CurTBName CURSOR
FOR
--获取需要监控的表的表名
SELECTtbname
FROM .
WHERE need_monitor = 1
OPEN CurTBName
FETCH NEXT FROM CurTBName INTO @TBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC . @TBNAME
FETCH NEXT FROM CurTBName INTO @TBNAME
END
CLOSE CurTBName
DEALLOCATE CurTBName
END
View Code
6、创建定时校验复制主从数据一致性JOB
每隔13个小时调用一次存储过程，当然这个调用频率可以结合实际情况进行修改
USE
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- ==============================================
--以什么登录用户身份运行作业
DECLARE @login_name NVARCHAR(100)
SET @login_name=N'sa'--★Do
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:JobCategory []] Script Date: 03/16/2015 15:18:09 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N''
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =msdb.dbo.sp_add_job @job_name=N'定时校验复制主从数据一致性JOB',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'定时校验复制主从数据一致性JOB',
@category_name=N'',
@owner_login_name=@login_name, @job_id = @jobId OUTPUT
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
/****** Object:Step Script Date: 03/16/2015 15:18:10 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ReplScanMonitorTb',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec .',
@database_name=N'Task',
@flags=0
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'执行频率',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=13,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110316,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'ddbd2dbc-ab05-4d0a-a4ca-60becc2620ac'
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR0 OR @ReturnCode0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
View Code
看一下执行结果
SELECT * FROM
从作业历史里看一下总执行时间
从执行结果里面也可以看到执行时间
脚本缺陷
这个脚本是有缺陷的，如果你是复制表里面的几个字段而不是整表复制的话，那么他就不能比较两边的一致性了
情况一：只复制表里的几个字段，并只需要监控一张表
解决办法：在第一个存储过程里面《执行数据一致性校验》存储过程 修改一下下面的代码只select复制的字段，而不是select *
--显示订阅表里面有的记录不在发布表里面的记录有多少 如果不为0 即数据不一致
SET @SQL = 'SELECTCOUNT(*) FROM( SELECT字段1，字段2。。。FROM .[' + @tbname + ']' --发布表
+ ' EXCEPT ' + 'SELECT字段1，字段2。。。 FROM[' + @LinkServer + '].'
+ '[' + @DBName + '].' + '.[' + @tbname + ']' --订阅表
+ ') AS T;'
情况二：只复制表里的几个字段，并且需要监控几张表，这些表中，有些表是整表复制，有些表只复制几个字段
由于脚本里面没有加入判断复制项目，那么对于这种情况，这个脚本无能为力
总结
在线上使用了事务复制这麽久不知道有多少人会定期的进行一下数据校验，当主库发生宕机的时候，你的从库的数据是否是一致的
如果你的主库因为硬件问题宕机，并且不能在最短的时间之内修复好，那么你这时再做主从数据的一致性校验已经没有可能了
这时候你有两个选择
1、冒险使用从库的数据，将从库变为主库
2、放弃使用从库，全部数据不要（当然了，全部数据不要是没有可能的！）
至于在SQL Server中比较两张表的数据一致性的方法和性能，可以参考下面这篇文章
SQLSERVER中如何快速比较两张表的不一样
如有任何问题，欢迎大家向我反馈o(∩_∩)o
2015-3-23 补充：今天发现主从数据出现了不一致的情况
可以看到即使两边的数据记录是一样的，但是也不代表两边的数据是一致的，表里面的数据内容也有可能不一致
Dest这张表两边都是82条记录
我们用SQL语句来检查一下
SELECT*FROM .EXCEPT
SELECT * FROM...
SELECT*FROM... EXCEPT
SELECT * FROM.
发现了ID为36的这条记录的Pause字段不一样，第6台是主，第11台是从
询问开发，开发说主那边是正确的，然后对从那边进行update回去正确的数据
注意：从数据库是设置了db_datareader的，只有DBA才可以对从数据库进行update操作！
2015-3-24 补充：如果要复制的表中包含了smalldatetime数据类型，那么在except比较的时候会出现不一致的情况
表定义
CREATE TABLE .(
IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
NOT NULL,
(32) NULL)
linkserver查出来的smalldatetime数据类型带秒小数部分，而在本地查询不会出现这种情况
后来查了资料发现这中间有一个数据类型映射问题
解决方法有两个：
1、表定义的时候不用smalldatetime 而用datetime
2、脚本里对用了smalldatetime 类型的字段做一下数据类型转换
第二个解决方法要写更加多的动态SQL判断字段所用数据类型，拼接更加多的SQL
当然第一个解决方案会浪费空间，如果业务是不需要记录秒级的时间的，那么就浪费4个字节的空间了
详细可以查看MSDN
分布式查询的数据类型映射
页:
[1]