發行項帶篩選的合併複製問題之訂閱者更新導致部份數據丟失

开始，下边的内容是我在工作中，碰到的问题，并且拿出来进行分析和解决，是对其过程的描述。由于时间原因没有进行繁体简体的转换。在一些字眼或术语中，简体繁体之间可能存在些差异。 如有不明可以通过文章后面的留言发表意见和建议，或发Email与我联系。

問題描述

在SQL Server 2005(版本：Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86))，帶篩選的合併複製中，發現有這樣的一個問題，在訂閱者Insert數據，數據上傳到發行者，然後下載至訂閱者，發現訂閱者的部份數據被Delete，如圖描述：

image

下面就真實環境中的問題，創建一個模擬環境來分析說明。這裡列舉一個數據庫名為：ReplicationDB的同步情況：

image

各資料表之間的關係圖：

image

創建資料表結構和初始化數據腳本：

View Code
Use [ReplicationDB]
Go
/* 創建表結構Andy 2011-10-13*/

--(1/5) DataOwner
If object_id('[DataOwner]') Is Null
Begin
    Create Table [DataOwner]
    (
        [ID] smallint Identity(1,1) Not Null ,
        [Owner] nvarchar(50) Null 
    )
    Alter Table [DataOwner] Add Constraint [PK_DataOwner] Primary Key Clustered ([ID] Asc)
End

--(2/5) Data1
If object_id('[Data1]') Is Null
Begin
    Create Table [Data1]
    (
        [ID] uniqueidentifier Not Null ,
        [OwnerID] smallint Not Null 
    )
    Alter Table [Data1] Add Constraint [PK_Data1] Primary Key Nonclustered ([ID] Asc)
    Alter Table [Data1] Add Constraint [FK_Data1_DataOwner] Foreign Key ([OwnerID]) References [DataOwner] ([ID])
End

--(3/5) Data2
If object_id('[Data2]') Is Null
Begin
    Create Table [Data2]
    (
        [ID] uniqueidentifier Not Null ,
        [ParentID] uniqueidentifier Not Null 
    )
    Alter Table [Data2] Add Constraint [PK_Data2] Primary Key Nonclustered ([ID] Asc)
    Alter Table [Data2] Add Constraint [FK_Data2_Data1] Foreign Key ([ParentID]) References [Data1] ([ID])
End

--(4/5) SysDataType
If object_id('[SysDataType]') Is Null
Begin
    Create Table [SysDataType]
    (
        [ID] smallint Identity(1,1) Not Null ,
        [Name] nvarchar(50) Null 
    )
    Alter Table [SysDataType] Add Constraint [PK_SysDataType] Primary Key Nonclustered ([ID] Asc)
End

--(5/5) DataRelation
If object_id('[DataRelation]') Is Null
Begin
    Create Table [DataRelation]
    (
        [ID] uniqueidentifier not null ,
        [ParentID] uniqueidentifier Null ,
        [DataTypeID] smallint Null 
    )
    Alter Table [DataRelation] Add Constraint [PK_DataRelation] Primary Key Nonclustered ([ID] Asc)
    Alter Table [DataRelation] Add Constraint [FK_DataRelation_DataTypeID] Foreign Key ([DataTypeID]) References [SysDataType] ([ID])
    Alter Table [DataRelation] Add Constraint [U_DataRelation_ParentID_DataTypeID] Unique Nonclustered ([ParentID],[DataTypeID])
End
Go

if Not Exists(Select 1 From SysDataType)
    Insert into SysDataType (Name)
        Select N'DataType1' Union All
        Select N'DataType2' Union All
        Select N'DataType3' 

if Not Exists(Select 1 From DataOwner)
    Insert into DataOwner ([Owner])
        Select N'PC1' Union All
        Select N'PC2' Union All
        Select N'PC3' 


go
--Procedures
Go

If object_id('sp_InsertData') Is not null  Drop Procedure sp_InsertData
Go
Create Procedure sp_InsertData
(
    @ID uniqueidentifier,
    @ParentID uniqueidentifier,
    @DataType1 Smallint=null,
    @DataType2 Smallint=null,
    @DataType3 Smallint=null
    
)
As
Begin Try
    Begin tran
        
        Insert into Data2(ID,ParentID)
                Values(@ID,@ParentID)
                
        
        ;With CTE_Data As
        (
            Select @DataType1 As DataTypeID Union All
            Select @DataType2 As DataTypeID Union All
            Select @DataType3 As DataTypeID 
        )
        Insert into DataRelation(ID,ParentID,DataTypeID)
              Select newid(),@ID,DataTypeID
                From CTE_Data
                Where DataTypeID is not null
        
              
    Commit Tran
End Try
Begin Catch
    Declare @Error nvarchar(2047)
    Set @Error=Error_message()
    Raiserror 50001 @Error
    Rollback Tran
End Catch
Go

If object_id('sp_DeleteData') Is not null  Drop Procedure sp_DeleteData
Go
Create Procedure sp_DeleteData
(
    @ID uniqueidentifier
    
)
As
Begin Try
    Begin tran
        
        Delete c 
            From Data1 As a
                Inner Join Data2 As b On b.ParentID=a.ID
                Inner Join DataRelation As c On c.ParentID=b.ID
            Where a.ID=@ID
         
         Delete b 
            From Data1 As a
                Inner Join Data2 As b On b.ParentID=a.ID
            Where a.ID=@ID
        
         Delete From Data1 Where ID=@ID
                     
    Commit Tran
End Try
Begin Catch
    Declare @Error nvarchar(2047)
    Set @Error=Error_message()
    Raiserror 50001 @Error
    Rollback Tran
End Catch
Go

腳本中的存儲過程sp_InsertData，sp_DeleteData 只是爲了分析過程應用到。

下面抽取【中央DB】à【分支DB】其中1條ReplicationDB同步例子來看在訂閱者Insert數據，會導致訂閱者部份數據給Delete掉，如圖描述（同步設置過程這裡忽略）：

image

上图，在訂閱者向表DataRelation插入了5條記錄，並且成功上載至發行者。最後一行發現下載變更到訂閱者，對表DataRelation刪除了剛上載的5條記錄。導致了訂閱者DataRelation新增的數據丟失。在發行者上，對DataRelation新插入的數據是沒有被刪除的。

下面表格是補充例子中的幾個資料表的篩選條件：

image

在發行者上Insert，會不會發生類似的問題呢？在實際例子中是正常的，如图：

image

可以看出在在發行者向表DataRelation插入數據，下載變更到訂閱者過程中只有插入5條記錄的動作，沒看到在訂閱者的刪除動作。

問題分析

接下來，我們跟蹤合併複製的過程，來分析問題所在。SQL Server提供了一些系統表來為我們跟蹤複製的同步過程,如：

  • Sysmergearticles 描述發行項內容，包含有nickname發現項的暱稱，分析過程會用到這一字段關聯。
  • MSmerge_contents 包含已發行資料表中Inserted/Updated的行(row).
  • MSmerge_tombstone包含已發行資料表中deleted的行(row).
  • MSmerge_genhistory包含傳遞至發行者或訂閱者的變更集合.簡單說就是記錄發行者/訂閱者的Inserted/Updated/deleted歷史。
  • MSmerge_partition_groups資料表會針對給定資料庫中每個預先計算的資料分割，各儲存一個資料列。當前分析，暫不用應用不到。
  • MSmerge_current_partition_mappings 描述Inserted/Updated的分區信息，與MSmerge_contents 相關。
  • MSmerge_past_partition_mappings描述Updated/Deleted的分區信息，與MSmerge_contents 和MSmerge_tombstone 相關。

做個例子，在Microsoft SQL Server Management Studio(MSSMS)，連接到訂閱者伺候器上，對ReplicationDB數據庫做個Insert數據的實例，類似于前面的操作：

use ReplicationDB
GO
Begin Tran
    Insert into Data1(ID,OwnerID)
            values ('80B370BA-446A-48D3-9EAC-2DE7FDC11997',3)
            
    
    Exec sp_InsertData 
            @ID = 'ACE3BA3E-C6FE-49B5-8E19-AB2F0F894F09', -- uniqueidentifier
            @ParentID = '80B370BA-446A-48D3-9EAC-2DE7FDC11997', -- uniqueidentifier
            @DataType1 = 1, -- smallint
            @DataType2 = 2, -- smallint
            @DataType3 = default -- smallint
    
    Exec sp_InsertData 
            @ID = 'AFD1B3B5-45D1-4F97-B4DA-AA6BF965C8BD', -- uniqueidentifier
            @ParentID = '80B370BA-446A-48D3-9EAC-2DE7FDC11997', -- uniqueidentifier
            @DataType1 = 1, -- smallint
            @DataType2 = 2, -- smallint
            @DataType3 = 3 -- smallint
Commit tran

Commit Tran代碼後面，填寫上跟蹤同步過程的T-SQL語句：

--跟蹤
Select * From sysmergearticles Where name='DataRelation'
Select * From MSmerge_contents Where tablenick=16720001 Order by generation Desc /*tablenick=25999002: OUPartitionAssignmentPlan*/
Select * From MSmerge_tombstone Where tablenick=16720001 Order by generation Desc /*tablenick=25999002: OUPartitionAssignmentPlan*/
Select * From MSmerge_genhistory Where art_nick=16720001 Order by coldate Desc,generation Desc /*art_nick=25999002: OUPartitionAssignmentPlan*/
Select a.*,b.name From MSmerge_current_partition_mappings As a Inner Join sysmergearticles As b On b.nickname=a.tablenick
Select * From MSmerge_past_partition_mappings  Where tablenick=16720001 Order by generation Desc

對MSmerge, MSmerge_tombstone, MSmerge_genhistory, MSmerge_past_partition_mappings表，只是查詢出有關DataRelation表的數據，通過sysmergearticles表中的nickname列返回的值作為Where參考值。經過執行上面的兩個連續的腳本，可得到似下面的結果數據：

image

這裡捕捉到訂閱者MSmerge_genhistory表中有一條記錄generation=74，genstatus=1(已開啟狀態)

，來自訂閱者本身的Insert操作，影響記錄行為2，在MSmerge_contents表可以找到兩行generation=74的記錄行與之對應。

等到數據同步OK(連續執行的合併複製，默認訂閱間隔時間是1分鐘),我們再看回MSmerge_genhistory,MSmerge_contents,MSmerge_tombstone表的數據變化：

image

相隔大約1分鐘后，數據從發行者同步回到訂閱者，MSmerge_genhistory 發現多了1行generation=75的歷史記錄，而且在MSmerge_tombstone表中發現新加入5行generation=75的記錄行。說明有資料表DataRelation有數據被刪除了。

在訂閱者，我們發現了DataRelation 表Insert動作，引發同步過程的變化。在此期間，我們先不要對資料表DataRelation進行其他的更新刪除操作，接著來看在發行者，DataRelation 表的Insert 過程如何變化。

image

在發行者，MSmerge_genhistory表新插入有兩條記錄，coldate時間比較少的1條的genstatus=2,表示已關閉，並且已在另一個訂閱者產生，這個訂閱者為subscriber_number=2(可以通過Select subscriber_number,subscriber_server from sysmergesubscriptions查詢到subscriber_number對應的subscriber_serve數據)。是于generation=63，在這裡無法找到對應的關聯數據，可能是在上載到發行者的時候發生，這位置捕捉不到。

MSmerge_genhistory表中，coldate時間比較晚的1條generation=65，可以在MSmerge_contents，MSmerge_past_partition_mappings表中找到對應的數據。問題就是這裡，MSmerge_past_partition_mappings中怎麼會存在generation=65的數據，按正常，Insert數據的時候，在MSmerge_current_partition_mappings表有1條或多條記錄與之對應。出現在MSmerge_past_partition_mappings表，只有Update or Delete的時候才會發生。

跟蹤都這裡，開始的時候，我判斷在SQL Server 合併複製中的觸發器某一環節出現bug導致這樣的問題出現，還在SQL Server 2008 R2上測試沒問題。後來查證了MSDN上的資料，得到一些啓發:

在合併同步處理期間，依預設，發行項將按物件間相依性所需的順序處理，包括在基底資料表上定義的宣告式參考完整性 (DRI) 條件約束。處理包括列舉對資料表所作的變更，然後套用這些變更。如果沒有 DRI，但資料表發行項之間存在聯結篩選或邏輯記錄，發行項將以篩選和邏輯記錄所需的順序處理。透過 DRI、聯結篩選、邏輯記錄或其他相依性與任何其他發行項無關的發行項。

根據這一說明，先從DRI方面考慮，先去瞭解各個表的結構。因為沒有設置過邏輯記錄的順序處理編號，通過下面的T-SQL語句可以查詢：

Select name,processing_order From sysmergearticles

image

當看回表結構的時候，果真發現了問題所在：

Create Table [DataRelation]
    (
        [ID] uniqueidentifier not null ,
        [ParentID] uniqueidentifier Null ,
        [DataTypeID] smallint Null 
    )
    Alter Table [DataRelation] Add Constraint [PK_DataRelation] Primary Key Nonclustered ([ID] Asc)
    Alter Table [DataRelation] Add Constraint [FK_DataRelation_DataTypeID] Foreign Key ([DataTypeID]) References [SysDataType] ([ID])
    Alter Table [DataRelation] Add Constraint [U_DataRelation_ParentID_DataTypeID] Unique Nonclustered ([ParentID],[DataTypeID])

資料表DataRelation，在字段ParentID上沒有創建外鍵約束。這裡沒有外鍵約束，同步過程中無法套用DRI條件約束，而且又沒設置邏輯記錄的順序處理，導致了問題所在。

提示：

如果想瞭解有關SQL Server 合併複製的跟蹤說明，可以參考聯機幫助文檔的《合併式複寫如何追蹤和列舉變更》：

http://msdn.microsoft.com/zh-cn/library/ms151789(v=SQL.90).aspx （簡體說明）

http://msdn.microsoft.com/zh-tw/library/ms151789(v=SQL.90).aspx (繁體說明)

http://msdn.microsoft.com/en-us/library/ms151789(v=SQL.90).aspx (英文說明)

《指定合併發行項的處理順序》:

http://msdn.microsoft.com/zh-cn/library/ms152469(v=SQL.90) (簡體說明)

http://msdn.microsoft.com/zh-tw/library/ms152469(v=SQL.90) (繁體說明)

術語：

DRI: declarative referential integrity 宣告式參考完整性，簡體意思是声明性引用完整性。

解決方法

根據前邊的分析，這裡列出幾個解決方法:

  • 增加外鍵約束，在資料表DataRelation字段ParentID上增加外鍵約束
  • 增加Check約束，在資料表DataRelation創建Check約束，檢查ParentID是否在資料表DATA2存在
  • 設置邏輯記錄的處理順序

增加外鍵約束:

在不重新設置同步複寫的前提下，在發行者實例上執行創建外鍵的腳本：

use ReplicationDB
Go
if object_id('FK_DataRelation_ParentID') Is null
    Alter table DataRelation Add COnstraint FK_DataRelation_ParentID foreign Key(ParentID) References Data2(ID)
Go

增加Check約束:

在不重新設置同步複寫的前提下，增加Check約束需要按嚴格的步驟進行。先在發行者和訂閱者兩個實例上執行腳本(檢查資料表DataRelation中的ParentID列數據是否在資料表Data2中存在)：

use ReplicationDB
Go
if object_id('Fn_CheckDataRelation_ParentID') Is Not Null
    Drop Function Fn_CheckDataRelation_ParentID
GO
Create Function Fn_CheckDataRelation_ParentID
(
    @ParentID uniqueidentifier
)
Returns bit
As
begin
Return(Select Case When Exists(Select 1 From Data2 Where ID=@ParentID) Then 1 Else 0 End)
End
Go

再在發行者實例上執行下面的創建Check約束腳本：

use ReplicationDB
Go
if object_id('CK_DataRelation_ParentID') Is null
    Alter table DataRelation Add COnstraint CK_DataRelation_ParentID Check(dbo.Fn_CheckDataRelation_ParentID(ParentID)=1)
Go

設置邏輯記錄的處理順序:

在不重新設置同步複寫的前提下，在發行者實例上執行下面的腳本

use ReplicationDB
Go
Exec sys.sp_changemergearticle 
      @publication = 'Merge-PC3',
        @article = 'DataOwner',
        @property = 'processing_order',
        @value = 10,
        @force_invalidate_snapshot =0,
        @force_reinit_subscription =0

Exec sys.sp_changemergearticle 
      @publication = 'Merge-PC3',
        @article = 'Data1',
        @property = 'processing_order',
        @value = 20,
        @force_invalidate_snapshot =0,
        @force_reinit_subscription =0
        
Exec sys.sp_changemergearticle 
      @publication = 'Merge-PC3',
        @article = 'Data2',
        @property = 'processing_order',
        @value = 30,
        @force_invalidate_snapshot =0,
        @force_reinit_subscription =0

Exec sys.sp_changemergearticle 
      @publication = 'Merge-PC3',
        @article = 'DataRelation',
        @property = 'processing_order',
        @value = 40,
        @force_invalidate_snapshot =0,
        @force_reinit_subscription =0

上面腳本是通過SQL Server提供的系統函數sys.sp_changemergearticle 來設置各個表的同步處理順序：

image

當Insert數據的時候，會先處理資料表DataOwner，最後是處理資料表DataRelation。相反，當Delete數據的時候，會先處理資料表DataRelation,最後處理資料表DataOwner。

數值10,20,30,40 這樣的設置，也是有其意義的，當我們日後有新增一張資料表，它在合併複製中的處理順序是在資料表Data1和資料表Data2之間，那麼我們直接設置新增加的資料表處理順序為25，不需要再設置處理順序>20的資料表處理順序。這一點在真實應用環境中，是值得參考的。

小結：

綜上所述，在具有篩選的合併式複製中，在篩選條件中含有多表關聯時候，特別要注意表的結構，檢查篩選的字段是否存在外鍵、Check約束，或者是否有設置了合併複製的處理順序。在一些古老的數據庫機構中，我們無法保證篩選的字段存在外鍵或Check約束，這時候，個人認為優先考慮的是外鍵，無法使用外鍵的時候，考慮是否可以使用Check約束；在不能改變數據庫資料表結構，或者對各表的關係非常的熟悉，最後可以考慮使用合併複製中的處理順序功能。

附件，提供这篇文章的DOC格式文档， 喜欢的朋友下载调式：

/Files/wghao/發行項帶篩選的合併複製問題之----訂閱者更新導致部份數據丟失.doc 

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wghao/p/2212637.html