开始，下边的内容是我在工作中，碰到的问题，并且拿出来进行分析和解决，是对其过程的描述。由于时间原因没有进行繁体简体的转换。在一些字眼或术语中，简体繁体之间可能存在些差异。 如有不明可以通过文章后面的留言发表意见和建议，或发Email与我联系。
問題描述
在SQL Server 2005(版本：Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86))，帶篩選的合併複製中，發現有這樣的一個問題，在訂閱者Insert數據，數據上傳到發行者，然後下載至訂閱者，發現訂閱者的部份數據被Delete，如圖描述：
下面就真實環境中的問題，創建一個模擬環境來分析說明。這裡列舉一個數據庫名為：ReplicationDB的同步情況：
各資料表之間的關係圖：
創建資料表結構和初始化數據腳本：
View Code 
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掉，如圖描述（同步設置過程這裡忽略）：
上图，在訂閱者向表DataRelation插入了5條記錄，並且成功上載至發行者。最後一行發現下載變更到訂閱者，對表DataRelation刪除了剛上載的5條記錄。導致了訂閱者DataRelation新增的數據丟失。在發行者上，對DataRelation新插入的數據是沒有被刪除的。
下面表格是補充例子中的幾個資料表的篩選條件：
在發行者上Insert，會不會發生類似的問題呢？在實際例子中是正常的，如图：
可以看出在在發行者向表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參考值。經過執行上面的兩個連續的腳本，可得到似下面的結果數據：
這裡捕捉到訂閱者MSmerge_genhistory表中有一條記錄generation=74，genstatus=1(已開啟狀態)
，來自訂閱者本身的Insert操作，影響記錄行為2，在MSmerge_contents表可以找到兩行generation=74的記錄行與之對應。
等到數據同步OK(連續執行的合併複製，默認訂閱間隔時間是1分鐘),我們再看回MSmerge_genhistory,MSmerge_contents,MSmerge_tombstone表的數據變化：
相隔大約1分鐘后，數據從發行者同步回到訂閱者，MSmerge_genhistory 發現多了1行generation=75的歷史記錄，而且在MSmerge_tombstone表中發現新加入5行generation=75的記錄行。說明有資料表DataRelation有數據被刪除了。
在訂閱者，我們發現了DataRelation 表Insert動作，引發同步過程的變化。在此期間，我們先不要對資料表DataRelation進行其他的更新刪除操作，接著來看在發行者，DataRelation 表的Insert 過程如何變化。
在發行者，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
當看回表結構的時候，果真發現了問題所在：
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 來設置各個表的同步處理順序：
當Insert數據的時候，會先處理資料表DataOwner，最後是處理資料表DataRelation。相反，當Delete數據的時候，會先處理資料表DataRelation,最後處理資料表DataOwner。
數值10,20,30,40 這樣的設置，也是有其意義的，當我們日後有新增一張資料表，它在合併複製中的處理順序是在資料表Data1和資料表Data2之間，那麼我們直接設置新增加的資料表處理順序為25，不需要再設置處理順序>20的資料表處理順序。這一點在真實應用環境中，是值得參考的。
小結：
綜上所述，在具有篩選的合併式複製中，在篩選條件中含有多表關聯時候，特別要注意表的結構，檢查篩選的字段是否存在外鍵、Check約束，或者是否有設置了合併複製的處理順序。在一些古老的數據庫機構中，我們無法保證篩選的字段存在外鍵或Check約束，這時候，個人認為優先考慮的是外鍵，無法使用外鍵的時候，考慮是否可以使用Check約束；在不能改變數據庫資料表結構，或者對各表的關係非常的熟悉，最後可以考慮使用合併複製中的處理順序功能。
附件，提供这篇文章的DOC格式文档， 喜欢的朋友下载调式：