[转贴]使用TableDiff实用工具解决事务复制中的问题

事务复制是数据同步中常用的一种手段，复制过程难免会遇到不少问题，就笔者遇到的问题而言，一般有两大类：一类是通过重新启动Distributor Agent即可解决的问题，另一类是因为Subscriber修改了数据，导致发布的数据有冲突，这类问题一般需要手工去修复。
TableDiff是SQL Server 2005的一个命令行实用工具，该工具可以比较两个表，并且生成数据同步的脚本。借助这个工具，可以很容易地修复两个表数据不一致的问题。
但如果应用该工具来解决事务复制中数据冲突的问题，则除了同步数据外，还必须解决手工同步数据后，跳过未发布的错误事务序列的问题。
本文的第1部分介绍了TableDiff工具的用法和笔者的一些使用测试，第2部分介绍了如何借助这个工具来修复事务复制中的数据冲突问题。希望通过这两个部分的介绍，能让大家对于解决事务复制中的问题有所帮助。
比较两个非收敛的表中的数据，可以从命令提示符或在批处理文件中使用该实用工具执行以下任务：
Ø 在充当复制发布服务器的SQL Server实例中的源表与充当复制订阅服务器的一个或多个SQL Server实例中的目标表之间进行逐行比较。
Ø 通过只比较行数和架构可以执行快速比较。
Ø 执行列级比较。
Ø 生成T-SQL脚本，用以修复目标服务器中的差异，以使源表和目标表实现收敛。
Ø 将结果记录到输出文件或目标数据库的表中
使用该工具，需要满足下述条件：
Ø 只能用于SQL Server。
Ø 表中不包含sql_variant 数据类型的列
Ø Source Table和Destination Table需要满足下列一致性：
n 数目一致
n 名称一致
n 如果使用 -strict 选项，要求列的类型一致，否则，仅要求列的类型兼容。下面的数据类型是兼容的

源数据类型
目标数据类型
源数据类型
目标数据类型
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必须至少包含一个:
n 主键
n 标识
n ROWGUID 列
n UNIQUE列
n 使用 -strict 选项时，Destination Table也必须至少包含一个上述列
Ø 如果生成T-SQL脚本，则脚本中不包含下列数据类型的列：
n varchar(max)
n nvarchar(max)
n varbinary(max)
n text
n ntext
n image
n timestamp
n xml
下表说明TableDiff的使用语法及相关的参数说明

TableDiff语法
参数说明
 [ -? ] |
{
    -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 ]
 
}
返回支持参数的列表
 
设置Source信息。
如果未指定sourceuser，表示使用Windows身份验证。
Sourcelocked指定比较过程中锁定源表的方式，可以是TABLOCK或者HOLDLOCK, 未指定，则不锁定源表（NOLOCK）
设置Destination信息。
如果未指定destinationuser，表示使用Windows身份验证。
destinationlocked指定比较过程中锁定目的表的方式，可以是TABLOCK或者HOLDLOCK, 未指定，则不锁定目的表（NOLOCK）
比较方式：
-q 只比较行数和架构
-c 比较列级差异，如果生成T-SQL脚本文件，则无论是否指定这个选项，都会进行列级差异比较
-strict 对源架构和目标架构进行严格比较
要比较的大型对象数据类型列的字节数，默认只比较前8000字节
生成T-SQL脚本的选项
-f 指定T-SQL脚本文件名
- bf 指定每个T-SQL脚本文件最多允许的语句数，超过此值会生成新脚本文件
输出文件的完整名称和路径
输出结果表
-et 指定输出结果表名（位于Subscriber）
如果结果表已经存在，则还需要指定-dt参数
指定连接相关的信息
-rc 指定失败重试的次数
-ri 指定重试的时间间隔
-t 指定连接超时时间
 

TableDiff的参数较长，根据使用的需求，下面的脚本可以帮助快速构建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
对TableDiff的测试及测试结果如下。
能正确的生成同步Destination Table的脚本，通过执行该脚本，能够使Destination Table和Source Table的数据保持一致。
工具能报告Destination Table和Source Table结构有差异，但无法列出差异的明细，也无法生成结构差异修正的脚本。
如果使用/q选项进行测试，则工具能报告Destination Table和Source Table的记录数以及是否有差异，但无法生成同步Destination Table的脚本。
4.         测试的Table是否位于Replication中
无论Table是否位于Replication中，只要TableDiff对表的要求，这些表都可以用TableDiff进行处理
经常测试，该工具对于SQL Server 2000和SQL Server 2005均支持，由于条件的原因，并未测试SQL Server 7.0
测试具有675万条记录的表，如果只比较记录数和Schema，则所需时间1.2秒左右；如果是列级比较并生成T-SQL脚本，则所需时间为150秒左右。
未进行下面的测试：
n 包含sql_variant、text、ntext、varchar(max)、nvarchar(max)、image、varbinary(max)类型之一的列
n -strict、-bf、-rc、-ri、-t 选项测试
五、    应用TableDiff修复事务复制中的数据差异
对于事务复制，导致复制出错最主要有下面两个故障：
Ø 网络或者服务器故障
这种故障，一般在网络或者服务器恢复后，重新启动Distributor Agent就可以解决。
注： 如果Distributor Agent失败，Distributor Agent会停止， SQL Server 2005会自动重启该Distributor Agent，而SQL Server 2000则不会。
Ø 直接修改订阅者的数据，导致发布冲突
对于这类故障，必须修复冲突的数据才能解决问题，一般来说，解决这类问题有几种方法：
n 重新初始化（重建发布/订阅）
如果一个发布中只有一个项目，并且数据量小，则通过重新初始化订阅的数据来解决数据冲突的问题比较适合。
使用重新初始化来解决数据冲突，要求在定义发布项目的时候，对于“名称冲突”的处理方式，不能选择“保持现有表不变”。
注： SQL Server 2005可以重新生成初始化快照并且初始化订阅，但SQL Server 2000只能用删除重新建立的方法。
n 在订阅的表中，修改数据来解决冲突
如果知道冲突的数据是怎么样的，则可以在订阅的表上手工改写数据来解决问题。不过，一般来说，会比较难于知道冲突的数据是怎么样的，所以这种方法可使用性并不太高
n 在订阅表中，修复数据差异，并且跳过冲突的事务序列
如果数据变化的频率不太高，并且数据量大，发布在包含多个项目时，这种方法比较适用。
对于修复事务复制中的故障，一般可以按下面的步骤进行：
1)         在Distributor服务器上检查Distributor Agent的状态
执行下面的语句
EXEC dbo.sp_MSenum_replication_agents
@type = 3
检查返回的结果集中，记录列status的值为6的信息。
2)         阅读step.1中，列comments中的信息，如果信息表明此问题可以通过重启Job来解决，则进入stop.3，否则进入step.4
3)         通过重新启动Job来解决问题
执行下面的语句
EXEC msdb.dbo.sp_start_job
@job_id = 0xF418774CDF675D47A140D43CD333D0EB
参数@Job_id的的值来自于step.2中记录信息的job_id列。
如果有多个Job需要通过此方法来处理，则重复step.3。
4)         如果无法通过重新启动Job来解决问题。
如果考虑通过“修复数据差异，并且跳过冲突的事务序列”的方法来解决，则继续下面的步骤。
其他处理方式不在这里描述。
5)         查看和记录未成功在订阅服务器上应用的信息
在subscript服务器上执行下面的语句，获取已经应用到subscriber上的最后一个事务序列号
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服务器上执行下面的语句，获取未应用到指定subscriber上的所有命令、事务序列号，及最后一个事务序列号
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
如果你想查看命令中的详细信息，你可以把commands转换成nvarchar来显示，执行类似下面的语句：
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)         实现publisher和subscriber之间的数据同步
在Publisher服务器上执行下面的语句，可以获取指定publisher和subscriber之间的所有同步的项目
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
获取了同步的项目后，就可以借助TableDiff工具逐个比较每个同步项目的数据差异，对于有差异的项目，通过该工具生成同步的脚本，然后在subscriber上执行这些脚本来实现数据的同步。
7)         跳过已经同步的事务日志序列
通过stp.7，已经实现了publisher和subscriber之间的数据同步，因此，之前未在subscriber上应用的事务日志序列都应该丢弃。
在Distributor服务器上执行下面的语句，记录手工修复事务复制的日志
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服务器上执行下面的脚本，设置已经应用的最后一个事务日志，以跳过手工修复的事务日志序列
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的运行情况，确定修复成功。
TableDiff工具在使用上还是比较简单，只是参数稍微显得有些多而已。经测试发现，在VS2005中，可以直接引用TableDiff进行二次开发（只是无法捕获到其内置检查出现的错误），故可考虑把修复事务复制问题的处理写成一个Tools。
另外，由于TableDiff可以用于非复制的表，因此，有时也可以用该工具来实现表的数据同步，或者是生成表的数据插入脚本。



------------

用TableDiff产生SQL Server同步脚本

TableDiff是一个控制台，它可以在SQL Server的表之间进行数据比较。另外，因为它是用SQL Server打包的，所以如果你想在不同的数据库环境下检查查找表的话，你就不需要另外购买其他工具。 'GLm"nQ  
05#]L*|:  
在不同的数据库环境中，DBA经常不得不在查找表中寻找它们的不同（例如：开发、质量保证和生产等不同的环境下）。这些查找表中的数据必须适应于所有的环境，这样才能确保测试的准确。 ~`X0~3g>G  
" ak2$\I  
在市场上有一些非常好的工具可以进行这些比较，也能执行很多其它功能。但是现在你不需要去购买它们了，因为SQL Server已经自带了这种工具，那就是TableDiff，它能为你完成这些功能。 z pf,&D  
4-]n1to]  
TableDiff使你可以很轻松地在表中进行数据比较，同时它也可以会为你创建脚本来同步程序环境。另外，作为一个能使查找表在测试环境和产品环境之间同步的优秀工具，TableDiff对于数据在产品服务器和复制服务器之间的同步也同样是非常有用的，这样，当发生复制问题的时候，它就能体现出优势了。 Rv, Jik  
_ya]{&k  
示例 (s*7>c l  
C+olp(CpqL  
TabelDiff是一个控制台应用软件，所以，你需要通过命令提示符、批处理文件或者通过使用xp_cmdshell的SQL Server来调用它。在这个例子中，我将在同一个服务器上的两个数据库之间搭建一个小的环境，然后比较两个表中的数据。在这里我是通过一个批处理文件来调用TableDiff。 4<Roj]X5  
?&A>17!  
Listing A中的脚本创建了两个数据库，并在每个数据库中创建了一个SourceTable表。然后将数据分别插入到每个环境中的SourceTable表里面，这时候，插入到两个表中的数据是明显不同的。（就我的SQL Server 2005环境而言，TableDiff在这个目录下：C:Program FilesMicrosoft SQL ServerCOM。文件的位置对你指定安装是非常重要的，因为你调用批处理文件进行创建的时候需要知道它的确切位置。）

批处理文件 #?%aHQZv  
:x7T.pjs  
在这个例子中，我将调用TableDiff utility，同时带上了一些必要的参数，对DatabaseA和DatabaseB两个数据库中各自的SourceTable表的数据进行比较。Listing B中的脚本带着参数通过源服务器（SourceServer）、源数据库（ SourceDatabase）、源表 SourceTable、目标服务器（ DestinationServer）、目标数据库（ DestinationDatabase）和目标表（DestinationTable）到达TableDiff utility。对于每一个站点来说，服务器名和表名都是相同的，因为我是在同一个数据库服务器的两个不同的数据库中比较使用的同一个名字的表。我通过的最后目的地是当地，同步脚本放置在那里。我将这个脚本存放在C:/根目录下，名字为diffs.txt。 %C1D{}zP^  
]=9 *v5Zm  
一旦我执行了这个批处理文件，在Listing C中的文本信息就会被放到C:diffs.txt这个文件里面。这个TSQL脚本能同步运行两个数据库之间的表。 8ruDpAtm-  
 jCJ7/BiU  
使用简单 _-RC t{8M  
=@AsSz?W  
TableDiff utility使用非常简单，配置也很方便，因为它是用SQL Server打包的，所以不需要购买任何其他附加的工具来同步不同环境下的表。由于它使用非常方便，也很容易获得它，所以如果有必要，我们就能使用它来开发一些解决方案自动创建脚本，以便同步不同的环境。 ksHG a<  
"^]42F-  
Tim Chapman是一位SQL Server数据库管理员，他现在在Louisville, KY的一家银行工作，他具有7年以上的IT经验，同时也获得了微软SQL Server 2000和SQL Server 2005认证。

列表A &kP *j+D  
use master S6F|-;a  
Go %m#>tBP@  
IF DB_ID('DatabaseA') IS NOT NULL     DROP DATABASE DatabaseA x`x7Ul!z  
GO \=XK/>Dc  
IF DB_ID('DatabaseB') IS NOT NULL     DROP DATABASE DatabaseB ^7{<A-L^a  
GO *H#DjPHw  
CREATE DATABASE DatabaseA 5e>t@}a*^  
GO ~&r9t8(|R  
CREATE DATABASE DatabaseB ?[~^yP  
GO a]Rj   
USE DatabaseA $o]:-  
GO Ox2~n"7*:=  
CREATE TABLE SourceTable &Glcn&fh  
(     IDCol INT IDENTITY(1,1),     Field1 SMALLINT,     Field2 SMALLINT,     Field3 SMALLINT,     Field4 SMALLINT   Vg#FpUk?  
) 'e6/d]  
GO ^kH}_t6n  
USE DatabaseB fKGad8l  
GO DBr< ogb  
CREATE TABLE SourceTable T(g#av]  
(     IDCol INT IDENTITY(1,1),     Field1 SMALLINT,     Field2 SMALLINT,     Field3 SMALLINT,     Field4 SMALLINT   q=7^ ^$j  
) DK(Vkm:\NO  
GO c#R[*>]!  
USE DatabaseA &|.>1 hwW  
GO @ aiVUFlP  
INSERT INTO SourceTable 2*.@Dgxs,X  
(Field1, Field2, Field3, Field4) >N7$0"?K+  
SELECT 1, 1, 1, 2 Dq3la#UZ  
UNION aN7!`MV)  
SELECT 1, 1, 2, 2 P3?'FH:Y  
UNION vhg|SWy  
SELECT 1, 3, 2, 2 8m_O0LVU{  
UNION iSr(k6XVsd  
SELECT 1, 3, 2, 2 `V3(l_L  
UNION T=,u#e&FH  
SELECT 4, 3, 2, 2 3 ]H@p  
GO A$<iax>7  
USE DatabaseB #$A%#p|W4  
GO |o?Co__<  
INSERT INTO SourceTable cbAhI(`  
(Field1, Field2, Field3, Field4) .Y;8JX3(K  
SELECT 1, 1, 1, 2 []rZ~k  
UNION i$adxNToM  
SELECT 1, 3, 2, 1 S rjfRnC  
UNION )7Y[pf  
SELECT 1, 3, 2, 2 | >}b 63  
UNION B4cEq!j  
SELECT 1, 3, 2, 2 x an@'$2K  
UNION r,be`G:  
SELECT 5, 3, 2, 2 eE$R+ix  
UNION S.! dVOO  
SELECT 5, 4, 3, 2 n4w^/OOk$  
GO


---
列表B ~ i^lt  
cd Program FilesMicrosoft SQL ServerCOMTableDiff -sourceserver "DatabaseServer" -sourcedatabase "DatabaseA" -sourcetable "SourceTable" -destinationserver "DatabaseServer" -destinationdatabase "DatabaseB" -destinationtable "SourceTable" -f "C:diffs.txt" kK`owXj.D  
PAUSE
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liangqihui/p/1031263.html