使用TableDiff實(shí)用工具解決事務(wù)復(fù)制中的問題 收藏
事務(wù)復(fù)制是數(shù)據(jù)同步中常用的一種手段，復(fù)制過程難免會(huì)遇到不少問題，就筆者遇到的問題而言，一般有兩大類：一類是通過重新啟動(dòng)Distributor Agent即可解決的問題，另一類是因?yàn)镾ubscriber修改了數(shù)據(jù)，導(dǎo)致發(fā)布的數(shù)據(jù)有沖突，這類問題一般需要手工去修復(fù)。
TableDiff是SQL Server 2005的一個(gè)命令行實(shí)用工具，該工具可以比較兩個(gè)表，并且生成數(shù)據(jù)同步的腳本。借助這個(gè)工具，可以很容易地修復(fù)兩個(gè)表數(shù)據(jù)不一致的問題。
但如果應(yīng)用該工具來解決事務(wù)復(fù)制中數(shù)據(jù)沖突的問題，則除了同步數(shù)據(jù)外，還必須解決手工同步數(shù)據(jù)后，跳過未發(fā)布的錯(cuò)誤事務(wù)序列的問題。
本文的第1部分介紹了TableDiff工具的用法和筆者的一些使用測(cè)試，第2部分介紹了如何借助這個(gè)工具來修復(fù)事務(wù)復(fù)制中的數(shù)據(jù)沖突問題。希望通過這兩個(gè)部分的介紹，能讓大家對(duì)于解決事務(wù)復(fù)制中的問題有所幫助。
一、 工具的作用
比較兩個(gè)非收斂的表中的數(shù)據(jù)，可以從命令提示符或在批處理文件中使用該實(shí)用工具執(zhí)行以下任務(wù)：
Ø 在充當(dāng)復(fù)制發(fā)布服務(wù)器的SQL Server實(shí)例中的源表與充當(dāng)復(fù)制訂閱服務(wù)器的一個(gè)或多個(gè)SQL Server實(shí)例中的目標(biāo)表之間進(jìn)行逐行比較。
Ø 通過只比較行數(shù)和架構(gòu)可以執(zhí)行快速比較。
Ø 執(zhí)行列級(jí)比較。
Ø 生成T-SQL腳本，用以修復(fù)目標(biāo)服務(wù)器中的差異，以使源表和目標(biāo)表實(shí)現(xiàn)收斂。
Ø 將結(jié)果記錄到輸出文件或目標(biāo)數(shù)據(jù)庫的表中
二、 工具的使用要求
使用該工具，需要滿足下述條件：
Ø 只能用于SQL Server。
Ø 表中不包含sql_variant 數(shù)據(jù)類型的列
Ø Source Table和Destination Table需要滿足下列一致性：
n 數(shù)目一致
n 名稱一致
n 如果使用 -strict 選項(xiàng)，要求列的類型一致，否則，僅要求列的類型兼容。下面的數(shù)據(jù)類型是兼容的
源數(shù)據(jù)類型 目標(biāo)數(shù)據(jù)類型 源數(shù)據(jù)類型 目標(biāo)數(shù)據(jù)類型
tinyint smallint、int、bigint nvarchar(max) ntext
smallint Int、bigint varbinary(max) image
int bigint text varchar(max)
timestamp varbinary ntext nvarchar(max)
varchar(max) text image varbinary(max)
Ø Source Table必須至少包含一個(gè):
n 主鍵
n 標(biāo)識(shí)
n ROWGUID 列
n UNIQUE列
n 使用 -strict 選項(xiàng)時(shí)，Destination Table也必須至少包含一個(gè)上述列
Ø 如果生成T-SQL腳本，則腳本中不包含下列數(shù)據(jù)類型的列：
n varchar(max)
n nvarchar(max)
n varbinary(max)
n text
n ntext
n image
n timestamp
n xml
三、 使用說明
1. 語法及參數(shù)說明：
下表說明TableDiff的使用語法及相關(guān)的參數(shù)說明
TableDiff語法 參數(shù)說明
[ -? ] |
{
-sourceserver source_server_name[\instance_name]
-sourcedatabase source_database
-sourcetable source_table_name
[ -sourceschema source_schema_name ]
[ -sourcepassword source_password ]
[ -sourceuser source_login ]
[ -sourcelocked ]
-destinationserver destination_server_name[\instance_name]
-destinationdatabase subscription_database
-destinationtable destination_table
[ -destinationschema destination_schema_name ]
[ -destinationpassword destination_password ]
[ -destinationuser destination_login ]
[ -destinationlocked ]
[ -q ]
[ -c ]
[ -strict ]
[ -b large_object_bytes ]
[ -bf number_of_statements ]
[ -f [ file_name ] ]
[ -o output_file_name ]
[ -et table_name ]
[ -dt ]
[ -rc number_of_retries ]
[ -ri retry_interval ]
[ -t connection_timeouts ]
} 返回支持參數(shù)的列表
設(shè)置Source信息。
如果未指定sourceuser，表示使用Windows身份驗(yàn)證。
Sourcelocked指定比較過程中鎖定源表的方式，可以是TABLOCK或者HOLDLOCK, 未指定，則不鎖定源表（NOLOCK）
設(shè)置Destination信息。
如果未指定destinationuser，表示使用Windows身份驗(yàn)證。
destinationlocked指定比較過程中鎖定目的表的方式，可以是TABLOCK或者HOLDLOCK, 未指定，則不鎖定目的表（NOLOCK）
比較方式：
-q 只比較行數(shù)和架構(gòu)
-c 比較列級(jí)差異，如果生成T-SQL腳本文件，則無論是否指定這個(gè)選項(xiàng)，都會(huì)進(jìn)行列級(jí)差異比較
-strict 對(duì)源架構(gòu)和目標(biāo)架構(gòu)進(jìn)行嚴(yán)格比較
要比較的大型對(duì)象數(shù)據(jù)類型列的字節(jié)數(shù)，默認(rèn)只比較前8000字節(jié)
生成T-SQL腳本的選項(xiàng)
-f 指定T-SQL腳本文件名
- bf 指定每個(gè)T-SQL腳本文件最多允許的語句數(shù)，超過此值會(huì)生成新腳本文件
輸出文件的完整名稱和路徑
輸出結(jié)果表
-et 指定輸出結(jié)果表名（位于Subscriber）
如果結(jié)果表已經(jīng)存在，則還需要指定-dt參數(shù)
指定連接相關(guān)的信息
-rc 指定失敗重試的次數(shù)
-ri 指定重試的時(shí)間間隔
-t 指定連接超時(shí)時(shí)間
2. 幫助快速生成命令的腳本
TableDiff的參數(shù)較長，根據(jù)使用的需求，下面的腳本可以幫助快速構(gòu)建TableDiff命令。
DECLARE
@User sysname, @Pwd sysname, @lock sysname,
@Source nvarchar(1000), @Destination nvarchar(1000)
-- set parameters on here
SELECT
@User = '',
@Pwd = '',
@lock = 'HOLDLOCK',
@Source = N'publisher.pubs..titles',
@Destination = N'subscriber.pubs..titles'
SELECT 'tablediff'
+ ' /sourceserver' + QUOTENAME(sSrv, '"')
+ ' /sourcedatabase' + QUOTENAME(sDb, '"')
+ ' /sourceschema' + QUOTENAME(sSch, '"')
+ ' /sourcetable' + QUOTENAME(sTb, '"')
+ CASE
WHEN @lock IS NULL OR @lock = '' THEN ''
ELSE ' /sourcelocked' + QUOTENAME(@lock, '"') END
+ CASE
WHEN @User IS NULL OR @User = '' THEN ''
ELSE ' /sourceuser' + QUOTENAME(@User, '"')
+ ' /sourcepassword' + QUOTENAME(@Pwd, '"')
END
+ ' /destinationserver' + QUOTENAME(dSrv, '"')
+ ' /destinationdatabase' + QUOTENAME(dDb, '"')
+ ' /destinationschema' + QUOTENAME(dSch, '"')
+ ' /destinationtable' + QUOTENAME(dTb, '"')
+ CASE
WHEN @lock IS NULL OR @lock = '' THEN ''
ELSE ' /destinationlocked' + QUOTENAME(@lock, '"') END
+ CASE
WHEN @User IS NULL OR @User = '' THEN ''
ELSE ' /destinationuser' + QUOTENAME(@User, '"')
+ ' /destinationpassword' + QUOTENAME(@Pwd, '"')
END
-- + ' /q'
-- + ' /c'
-- + ' /strict'
-- + ' /b"8000"'
-- + ' /bf"10000"'
-- + ' /f"c:\syn.sql"'
-- + ' /o"c:\output.txt"'
-- + ' /et"TableDiffResult"'
-- + ' /dt'
-- + ' /rc"3"'
-- + ' /ri"300"'
-- + ' /t"15"'
FROM(
SELECT
sSrv = ISNULL(PARSENAME(Source, 4), N'localhost'),
sDb = ISNULL(PARSENAME(Source, 3), N'master'),
sSch = ISNULL(PARSENAME(Source, 2), N'dbo'),
sTb = ISNULL(PARSENAME(Source, 1), N'notable'),
dSrv = ISNULL(PARSENAME(Destination, 4), N'localhost'),
dDb = ISNULL(PARSENAME(Destination, 3), N'master'),
dSch = ISNULL(PARSENAME(Destination, 2), N'dbo'),
dTb = ISNULL(PARSENAME(Destination, 1), N'notable')
FROM(
SELECT
Source = @Source, Destination = @Destination
)A
)A
四、 所做測(cè)試及測(cè)試結(jié)果
對(duì)TableDiff的測(cè)試及測(cè)試結(jié)果如下。
1. 數(shù)據(jù)內(nèi)容差異比較測(cè)試
能正確的生成同步Destination Table的腳本，通過執(zhí)行該腳本，能夠使Destination Table和Source Table的數(shù)據(jù)保持一致。
2. 結(jié)構(gòu)差異測(cè)試
工具能報(bào)告Destination Table和Source Table結(jié)構(gòu)有差異，但無法列出差異的明細(xì)，也無法生成結(jié)構(gòu)差異修正的腳本。
3. 記錄數(shù)差異測(cè)試
如果使用/q選項(xiàng)進(jìn)行測(cè)試，則工具能報(bào)告Destination Table和Source Table的記錄數(shù)以及是否有差異，但無法生成同步Destination Table的腳本。
4. 測(cè)試的Table是否位于Replication中
無論Table是否位于Replication中，只要TableDiff對(duì)表的要求，這些表都可以用TableDiff進(jìn)行處理
5. 測(cè)試適用的版本
經(jīng)常測(cè)試，該工具對(duì)于SQL Server 2000和SQL Server 2005均支持，由于條件的原因，并未測(cè)試SQL Server 7.0
6. 測(cè)試速度
測(cè)試具有675萬條記錄的表，如果只比較記錄數(shù)和Schema，則所需時(shí)間1.2秒左右；如果是列級(jí)比較并生成T-SQL腳本，則所需時(shí)間為150秒左右。
7. 未進(jìn)行的測(cè)試
未進(jìn)行下面的測(cè)試：
n 包含sql_variant、text、ntext、varchar(max)、nvarchar(max)、image、varbinary(max)類型之一的列
n -strict、-bf、-rc、-ri、-t 選項(xiàng)測(cè)試
五、 應(yīng)用TableDiff修復(fù)事務(wù)復(fù)制中的數(shù)據(jù)差異
1. 事務(wù)復(fù)制中的故障
對(duì)于事務(wù)復(fù)制，導(dǎo)致復(fù)制出錯(cuò)最主要有下面兩個(gè)故障：
Ø 網(wǎng)絡(luò)或者服務(wù)器故障
這種故障，一般在網(wǎng)絡(luò)或者服務(wù)器恢復(fù)后，重新啟動(dòng)Distributor Agent就可以解決。
注： 如果Distributor Agent失敗，Distributor Agent會(huì)停止， SQL Server 2005會(huì)自動(dòng)重啟該Distributor Agent，而SQL Server 2000則不會(huì)。
Ø 直接修改訂閱者的數(shù)據(jù)，導(dǎo)致發(fā)布沖突
對(duì)于這類故障，必須修復(fù)沖突的數(shù)據(jù)才能解決問題，一般來說，解決這類問題有幾種方法：
n 重新初始化（重建發(fā)布/訂閱）
如果一個(gè)發(fā)布中只有一個(gè)項(xiàng)目，并且數(shù)據(jù)量小，則通過重新初始化訂閱的數(shù)據(jù)來解決數(shù)據(jù)沖突的問題比較適合。
使用重新初始化來解決數(shù)據(jù)沖突，要求在定義發(fā)布項(xiàng)目的時(shí)候，對(duì)于“名稱沖突”的處理方式，不能選擇“保持現(xiàn)有表不變”。
注： SQL Server 2005可以重新生成初始化快照并且初始化訂閱，但SQL Server 2000只能用刪除重新建立的方法。
n 在訂閱的表中，修改數(shù)據(jù)來解決沖突
如果知道沖突的數(shù)據(jù)是怎么樣的，則可以在訂閱的表上手工改寫數(shù)據(jù)來解決問題。不過，一般來說，會(huì)比較難于知道沖突的數(shù)據(jù)是怎么樣的，所以這種方法可使用性并不太高
n 在訂閱表中，修復(fù)數(shù)據(jù)差異，并且跳過沖突的事務(wù)序列
如果數(shù)據(jù)變化的頻率不太高，并且數(shù)據(jù)量大，發(fā)布在包含多個(gè)項(xiàng)目時(shí)，這種方法比較適用。
2. 修復(fù)事務(wù)復(fù)制中的故障
對(duì)于修復(fù)事務(wù)復(fù)制中的故障，一般可以按下面的步驟進(jìn)行：
1) 在Distributor服務(wù)器上檢查Distributor Agent的狀態(tài)
執(zhí)行下面的語句
EXEC dbo.sp_MSenum_replication_agents
@type = 3
檢查返回的結(jié)果集中，記錄列status的值為6的信息。
2) 閱讀step.1中，列comments中的信息，如果信息表明此問題可以通過重啟Job來解決，則進(jìn)入stop.3，否則進(jìn)入step.4
3) 通過重新啟動(dòng)Job來解決問題
執(zhí)行下面的語句
EXEC msdb.dbo.sp_start_job
@job_id = 0xF418774CDF675D47A140D43CD333D0EB
參數(shù)@Job_id的的值來自于step.2中記錄信息的job_id列。
如果有多個(gè)Job需要通過此方法來處理，則重復(fù)step.3。
4) 如果無法通過重新啟動(dòng)Job來解決問題。
如果考慮通過“修復(fù)數(shù)據(jù)差異，并且跳過沖突的事務(wù)序列”的方法來解決，則繼續(xù)下面的步驟。
其他處理方式不在這里描述。
5) 查看和記錄未成功在訂閱服務(wù)器上應(yīng)用的信息
在subscript服務(wù)器上執(zhí)行下面的語句，獲取已經(jīng)應(yīng)用到subscriber上的最后一個(gè)事務(wù)序列號(hào)
USE [test_sub] -- subscript database, info come from stp.2
DECLARE
@publisher sysname, @publisher_db sysname, @publication sysname
SELECT -- get publish info on stp.2
@publisher = N'WSCDMIS048',
@publisher_db= N'test',
@publication = N'test'
SELECT
hashid = CASE DATALENGTH(transaction_timestamp)
WHEN 16 THEN ISNULL(SUBSTRING(transaction_timestamp, 16, 1), 0)
ELSE 0 END,
xact_seqno = transaction_timestamp,
subscription_guid
FROM dbo.MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication= @publication
AND subscription_type = 0
在Distributor服務(wù)器上執(zhí)行下面的語句，獲取未應(yīng)用到指定subscriber上的所有命令、事務(wù)序列號(hào)，及最后一個(gè)事務(wù)序列號(hào)
USE [distribution]
EXEC dbo.sp_MSget_repl_commands
@agent_id = 9, -- come from step.2
@last_xact_seqno = 0x000000470000013F0006000000000000,
-- come from step.5
@get_count = 0,
@compatibility_level = 9000000
如果你想查看命令中的詳細(xì)信息，你可以把commands轉(zhuǎn)換成nvarchar來顯示，執(zhí)行類似下面的語句：
SELECT
commandtext = CONVERT(nvarchar(1024), CASE WHEN type = 30 THEN SUBSTRING(command, 17, 1024) ELSE command END),
*
FROM dbo.MSRepl_commands
WHERE xact_seqno >= 0x00000047000001300000
-- come from step 5
6) 實(shí)現(xiàn)publisher和subscriber之間的數(shù)據(jù)同步
在Publisher服務(wù)器上執(zhí)行下面的語句，可以獲取指定publisher和subscriber之間的所有同步的項(xiàng)目
USE [test] -- publisher database, info from step.2
DECLARE
@publication sysname,
@subscriber sysname, @subscriber_db sysname
SELECT -- get publish info on stp.2
@publication = N'test',
@subscriber = N'WSCDMIS048',
@subscriber_db = N'test_sub'
EXEC sp_helpsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriber_db
獲取了同步的項(xiàng)目后，就可以借助TableDiff工具逐個(gè)比較每個(gè)同步項(xiàng)目的數(shù)據(jù)差異，對(duì)于有差異的項(xiàng)目，通過該工具生成同步的腳本，然后在subscriber上執(zhí)行這些腳本來實(shí)現(xiàn)數(shù)據(jù)的同步。
7) 跳過已經(jīng)同步的事務(wù)日志序列
通過stp.7，已經(jīng)實(shí)現(xiàn)了publisher和subscriber之間的數(shù)據(jù)同步，因此，之前未在subscriber上應(yīng)用的事務(wù)日志序列都應(yīng)該丟棄。
在Distributor服務(wù)器上執(zhí)行下面的語句，記錄手工修復(fù)事務(wù)復(fù)制的日志
USE [distribution]
EXEC dbo.sp_MSadd_distribution_history
@agent_id = 9, -- come from step 2
@runstatus = 1,
@comments = N'fix by DBA',
@xact_seqno = 0x00000041000000F80004 -- from step 5(last_xact_seqno)
在subscriber服務(wù)器上執(zhí)行下面的腳本，設(shè)置已經(jīng)應(yīng)用的最后一個(gè)事務(wù)日志，以跳過手工修復(fù)的事務(wù)日志序列
USE [test_sub] -- subscript database, this info come from stp.2
DECLARE
@transaction_timestamp varbinary(16), @time datetime,
@publisher sysname, @publisher_db sysname, @publication sysname
SELECT -- subscript database, this info come from stp.2
@publisher = N'WSCDMIS048',
@publisher_db = N'test',
@publication = N'test',
@transaction_timestamp = 0x00000041000000F80004,
-- come from step 5, (last_xact_seqno)
@time= GETDATE()
UPDATE dbo.MSreplication_subscriptions SET
transaction_timestamp = CAST(@transaction_timestamp as binary(15))
+ CAST(SUBSTRING(transaction_timestamp, 16, 1) as binary(1)),
[time] = @time
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication = @publication
AND subscription_type = 0
AND (
SUBSTRING(transaction_timestamp, 16, 1) = 0 OR
DATALENGTH(transaction_timestamp) < 16)
8) 檢查Distribution Agent的運(yùn)行情況，確定修復(fù)成功。
六、 其他
TableDiff工具在使用上還是比較簡單，只是參數(shù)稍微顯得有些多而已。經(jīng)測(cè)試發(fā)現(xiàn)，在VS2005中，可以直接引用TableDiff進(jìn)行二次開發(fā)（只是無法捕獲到其內(nèi)置檢查出現(xiàn)的錯(cuò)誤），故可考慮把修復(fù)事務(wù)復(fù)制問題的處理寫成一個(gè)Tools。
另外，由于TableDiff可以用于非復(fù)制的表，因此，有時(shí)也可以用該工具來實(shí)現(xiàn)表的數(shù)據(jù)同步，或者是生成表的數(shù)據(jù)插入腳本。
