-
数据库
站-
热门城市 全国站>
-
其他省市
-
-
400-636-0069
小标
2019-04-16
来源 ：
阅读 636
评论 0
摘要：本文主要向大家介绍了SQLServer数据库之用脚本定时监控SQL Server主从一致性，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。
本文主要向大家介绍了SQLServer数据库之用脚本定时监控SQL Server主从一致性，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。
首先说一下我们的环境
我们使用的是事务复制，复制是单向的，主服务器和从服务器都在同一个机房，当然不同机房也可以，只需要改一下IP和端口
下面的脚本在我们的SQLServer 2008上已经应用，暂时没有发现问题，当然，如果大家使用过程中有发现问题欢迎向我反馈o(∩_∩)o
首先，我们为什麽要校验呢？
我们知道因为网络延迟，或者从库有写入的情况（当然一般我们在订阅端会设置为db_datareader，不允许写）会造成主从数据不一致的情况
无论是SQL Server还是MySQL，所以我们就需要进行数据校验，以便大概知道我们的数据什么时候开始不一致
而校验是不可能每时每刻都做校验的，因为需要读取全表数据，对性能会有影响
下面的过程只需要远程上去从服务器，也就是订阅服务器上面做就可以了，完全不需要远程主服务器也就是发布服务器
线上我们做复制的表都比较小，数据量也不大
我们做复制的最大一个表是600MB的表
600MB的表 校验时间是1 分钟，那么可以推算 50000MB（50GB）的表 大概80分钟 ，至于这个时间根据不同的环境 硬件和软件 所需的校验时间可能会有所不同
我们使用的服务器是DELL R720
这个脚本原理很简单，就是利用SQL Server的job每天定时执行来获取主从上面的数据，从而判断主从数据是否一致
废话不说了，上脚本
1、在订阅端执行查看哪些表做了复制
首先你需要知道你现在哪些表是做了复制的，当然有些人会到发布服务器上去看，点击几下按钮，其实在订阅端是有视图可以看出
当前哪些表做了复制的
--在订阅端执行use [Task] -- 要复制的库GOselect article from dbo.MSreplication_objectsgroup by articleGO
有9个表做了复制
2、建立linkedserver
--建立linkedserverUSE [master]GODECLARE @IP NVARCHAR(MAX)DECLARE @Login NVARCHAR(MAX)DECLARE @PWD NVARCHAR(MAX)SET @Login = N'xxx' --★DoSET @PWD = N'xxx' --★DoSET @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 = NULLEXEC 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 [master]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 [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] ENDCREATE 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] ENDCREATE 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表
4、创建执行数据一致性校验存储过程
USE [Task]GO/****** Object: StoredProcedure [dbo].[usp_ConsistencyCheck] Script Date: 03/19/2015 15:36:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <桦仔>-- Create date: <2015.03.08>-- Description: <执行数据一致性校验> -- =============================================CREATE PROCEDURE [dbo].[usp_ReplConsistencyCheck] ( @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 1 sysindx.[rowcnt] FROM ' + '[' + @LinkServer + '].' + '[' + @DBName + '].' + '[sys].[sysobjects] AS sysobjINNER JOIN [' + @LinkServer + '].' + '[' + @DBName + '].' + '[sys].[sysindexes] AS sysindx ON sysobj.[id] = sysindx.[id] AND sysobj.[xtype] = ''u'' AND sysobj.[name] =' + '''' + @tbname + ''''--获取从库表的记录数 SET @SQLCountSlave = 'SELECT TOP 1 sysindx.[rowcnt] FROM ' + '[' + @DBName + '].' + '[sys].[sysobjects] AS sysobjINNER JOIN [' + @DBName + '].' + '[sys].[sysindexes] AS sysindx ON sysobj.[id] = sysindx.[id] AND sysobj.[xtype] = ''u'' AND sysobj.[name] =' + '''' + @tbname + '''' --创建临时表保存临时结果 IF EXISTS ( SELECT * FROM [tempdb]..sysobjects WHERE id = OBJECT_ID('tempdb..#tmptb1') ) BEGIN DROP TABLE [tempdb].[#tmptb1] END IF EXISTS ( SELECT * FROM [tempdb]..sysobjects WHERE id = OBJECT_ID('tempdb..#tmptb2') ) BEGIN DROP TABLE [tempdb].[#tmptb2] END IF EXISTS ( SELECT * FROM [tempdb]..sysobjects WHERE id = OBJECT_ID('tempdb..#tmptb3') ) BEGIN DROP TABLE [tempdb].[#tmptb3] END CREATE TABLE [#tmptb1] ( [is_Consistency] INT )-- 一致为1, 不一致为0 CREATE TABLE [#tmptb2]([master_record] BIGINT)--主库记录数 CREATE TABLE [#tmptb3]([slave_record] BIGINT) --从库记录数 INSERT INTO [#tmptb2]( [master_record]) EXEC ( @SQLCountMaster) INSERT INTO [#tmptb3]( [slave_record]) EXEC ( @SQLCountSlave) SELECT TOP ( 1 ) @master_record = [master_record] FROM [#tmptb2] SELECT TOP ( 1 ) @slave_record = [slave_record] FROM [#tmptb3] IF ( @master_record <> @slave_record ) BEGIN SET @is_Consistency = 0 END ELSE BEGIN --显示订阅表里面有的记录不在发布表里面的记录有多少 如果不为0 即数据不一致 SET @SQL = 'SELECT COUNT(*) FROM ( SELECT * FROM [dbo].[' + @tbname + ']' --发布表 + ' EXCEPT ' + 'SELECT * FROM [' + @LinkServer + '].' + '[' + @DBName + '].' + '[dbo].[' + @tbname + ']' --订阅表 + ') AS T;' INSERT INTO [#tmptb1]([is_Consistency]) EXEC (@SQL) IF ( SELECT TOP 1 [is_Consistency] FROM [#tmptb1]) <> 0 BEGIN SET @is_Consistency = 0 END ELSE BEGIN SET @is_Consistency = 1 END END INSERT INTO [Repl_MonitorStatus] ( [tbname] , [is_Consistency] , [master_record] , [slave_record] , [update_time] ) SELECT @tbname , @is_Consistency , @master_record , @slave_record , GETDATE() ENDView Code
注意：脚本中凡是有--★Do 的都是你需要结合自己情况去修改的变量
这个脚本的原理很简单，是读取主库表的记录数，然后读取从库表的记录数，然后进行比较
当两边的记录数是一致的，那么再用EXCEPT 减法归零的方法比较两边表数据的内容是否一致
如果也是一致的，那么两边表的数据就是一致的，否则就是不一致的，这里有一个效率问题，就是首先判断记录数是否一致
如果不一致就没有必要再去比较内容一致了，最后把数据插入到表Repl_MonitorStatus
5、创建扫描要监控的表存储过程
这里用游标检查哪一个表需要进行校验，然后调用usp_ReplConsistencyCheck存储过程进行校验
USE [Task] --★Do GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <桦仔>-- Create date: <2015.03.08>-- Description: <扫描要监控的表> -- =============================================CREATE PROCEDURE [dbo].[usp_ReplScanMonitorTb]AS BEGIN DECLARE @TBNAME NVARCHAR(100) DECLARE CurTBName CURSOR FOR --获取需要监控的表的表名 SELECT tbname FROM [dbo].[Repl_NeedMonitor] WHERE need_monitor = 1 OPEN CurTBName FETCH NEXT FROM CurTBName INTO @TBNAME WHILE @@FETCH_STATUS = 0 BEGIN EXEC [dbo].[usp_ReplConsistencyCheck] @TBNAME FETCH NEXT FROM CurTBName INTO @TBNAME END CLOSE CurTBName DEALLOCATE CurTBName END
View Code
6、创建定时校验复制主从数据一致性JOB
每隔13个小时调用一次存储过程，当然这个调用频率可以结合实际情况进行修改
USE [msdb]GO-- =============================================-- Author:<桦仔>-- Create date: <2015.03.8>-- Description: <定时校验复制主从数据一致性JOB>-- ==============================================--以什么登录用户身份运行作业DECLARE @login_name NVARCHAR(100)SET @login_name=N'sa' --★DoBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/16/2015 15:18:09 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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'[Uncategorized (Local)]', @owner_login_name=@login_name, @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [ResetLoginPassword] 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 [dbo].[usp_ReplScanMonitorTb]', @database_name=N'Task', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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 (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO
View Code
看一下执行结果
SELECT * FROM [Repl_MonitorStatus]
从作业历史里看一下总执行时间
从执行结果里面也可以看到执行时间
本文由职坐标整理并发布，希望对同学们学习SQL Server有所帮助，更多内容请关注职坐标数据库SQL Server数据库频道！
喜欢 | 0
不喜欢 | 0
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了，明天再试试吧！
我们会在第一时间安排职业规划师联系您！
您也可以联系我们的职业规划师咨询：
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com，All Rights Reserved.
沪公网安备 31011502005948号
索取资料
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈
