文章出處

用腳本定時監控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個表做了復制

 

2、建立linkedserver

--建立linkedserver
USE [master]
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 [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]
        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表

 

4、創建執行數據一致性校驗存儲過程

USE [Task]
GO
/****** Object:  StoredProcedure [dbo].[usp_ConsistencyCheck]    Script Date: 03/19/2015 15:36:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 sysobj
INNER 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 sysobj
INNER 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()
    
    END
View Code

注意：腳本中凡是有--★Do 的都是你需要結合自己情況去修改的變量

這個腳本的原理很簡單，是讀取主庫表的記錄數，然后讀取從庫表的記錄數，然后進行比較

當兩邊的記錄數是一致的，那么再用EXCEPT  減法歸零的方法比較兩邊表數據的內容是否一致

如果也是一致的，那么兩邊表的數據就是一致的，否則就是不一致的，這里有一個效率問題，就是首先判斷記錄數是否一致

如果不一致就沒有必要再去比較內容一致了，最后把數據插入到表Repl_MonitorStatus

 

5、創建掃描要監控的表存儲過程

這里用游標檢查哪一個表需要進行校驗，然后調用usp_ReplConsistencyCheck存儲過程進行校驗

USE [Task] --★Do  
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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'  --★Do


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @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)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 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'[Uncategorized (Local)]', 
        @owner_login_name=@login_name, @job_id = @jobId OUTPUT
IF (@@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=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) 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 (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
View Code

 

 看一下執行結果

SELECT * FROM [Repl_MonitorStatus]

從作業歷史里看一下總執行時間

從執行結果里面也可以看到執行時間

 

腳本缺陷

這個腳本是有缺陷的，如果你是復制表里面的幾個字段而不是整表復制的話，那么他就不能比較兩邊的一致性了

情況一：只復制表里的幾個字段，并只需要監控一張表

解決辦法：在第一個存儲過程里面《執行數據一致性校驗》存儲過程 修改一下下面的代碼只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;'

 

情況二：只復制表里的幾個字段，并且需要監控幾張表，這些表中，有些表是整表復制，有些表只復制幾個字段

由于腳本里面沒有加入判斷復制項目，那么對于這種情況，這個腳本無能為力

 

總結

在線上使用了事務復制這麼久不知道有多少人會定期的進行一下數據校驗，當主庫發生宕機的時候，你的從庫的數據是否是一致的

如果你的主庫因為硬件問題宕機，并且不能在最短的時間之內修復好，那么你這時再做主從數據的一致性校驗已經沒有可能了

這時候你有兩個選擇

1、冒險使用從庫的數據，將從庫變為主庫

2、放棄使用從庫，全部數據不要（當然了，全部數據不要是沒有可能的！）

 

當然，這樣直接在主庫上進行select查詢，會影響到主庫的業務，嚴重的話，可能會遇到死鎖

我們可以變通一下,使用快照數據庫來解決,linkedserver連接到快照數據庫進行數據內容對比

--進行數據對比之前先建立主庫的數據庫快照,再進行對比
CREATE DATABASE sss_ss ON
(NAME = N'sss', FILENAME = N'E:\DataBase\sss.ss' )
    AS SNAPSHOT OF [sss];
GO

--linkedserver直接連接到快照數據庫進行對比
USE [sss_ss]
go

SELECT * FROM [dbo].[test]

------------------------------------------------
--對比完之后,再刪除快照數據庫
USE [master]
GO

DROP DATABASE [sss_ss]

 

 

至于在SQL Server中比較兩張表的數據一致性的方法和性能，可以參考下面這篇文章

SQLSERVER中如何快速比較兩張表的不一樣 

 

 

如有任何問題，歡迎大家向我反饋o(∩_∩)o 

 

2015-3-23 補充：今天發現主從數據出現了不一致的情況

可以看到即使兩邊的數據記錄是一樣的，但是也不代表兩邊的數據是一致的，表里面的數據內容也有可能不一致

Dest這張表兩邊都是82條記錄

我們用SQL語句來檢查一下

SELECT  *  FROM [dbo].[Dest]  EXCEPT 
SELECT * FROM  [192.168.100.116].[Task].[dbo].[Dest]



SELECT  *  FROM  [192.168.100.116].[Task].[dbo].[Dest]   EXCEPT 
SELECT * FROM  [dbo].[Dest]

發現了ID為36的這條記錄的Pause字段不一樣，第6臺是主，第11臺是從

詢問開發，開發說主那邊是正確的，然后對從那邊進行update回去正確的數據

注意：從數據庫是設置了db_datareader的，只有DBA才可以對從數據庫進行update操作！

 

2015-3-24 補充：如果要復制的表中包含了smalldatetime數據類型，那么在except比較的時候會出現不一致的情況

 

 

 

 

表定義

CREATE TABLE [dbo].[Extension](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AddOn] [smalldatetime] NOT NULL,
    [Hash] [nchar](32) NULL)

linkserver查出來的smalldatetime數據類型帶秒小數部分，而在本地查詢不會出現這種情況

后來查了資料發現這中間有一個數據類型映射問題

解決方法有兩個：

1、表定義的時候不用smalldatetime 而用datetime

2、腳本里對用了smalldatetime 類型的字段做一下數據類型轉換

第二個解決方法要寫更加多的動態SQL判斷字段所用數據類型，拼接更加多的SQL

當然第一個解決方案會浪費空間，如果業務是不需要記錄秒級的時間的，那么就浪費4個字節的空間了

 

詳細可以查看MSDN

分布式查詢的數據類型映射

 


文章列表


不含病毒。www.avast.com
arrow
arrow
    全站熱搜
    創作者介紹
    創作者 大師兄 的頭像
    大師兄

    IT工程師數位筆記本

    大師兄 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄