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

原创 2006年12月27日 20:33:00

 

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

源数据类型
目标数据类型
源数据类型
目标数据类型
tinyint
smallintintbigint
nvarchar(max)
ntext
smallint
Intbigint
varbinary(max)
image
int
bigint
text
varchar(max)
timestamp
varbinary
ntext
nvarchar(max)
varchar(max)
text
image
varbinary(max)

Ø Source Table必须至少包含一个:
主键
标识
ROWGUID 列
UNIQUE列
使用 -strict 选项时,Destination Table也必须至少包含一个上述列
Ø 如果生成T-SQL脚本,则脚本中不包含下列数据类型的列:
varchar(max)
nvarchar(max)
varbinary(max)
text
ntext
image
timestamp
xml
三、    使用说明
1.         语法及参数说明:
下表说明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 指定连接超时时间
 

2.         帮助快速生成命令的脚本
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的测试及测试结果如下。
1.         数据内容差异比较测试
能正确的生成同步Destination Table的脚本,通过执行该脚本,能够使Destination Table和Source Table的数据保持一致。
2.         结构差异测试
工具能报告Destination Table和Source Table结构有差异,但无法列出差异的明细,也无法生成结构差异修正的脚本。
3.         记录数差异测试
如果使用/q选项进行测试,则工具能报告Destination Table和Source Table的记录数以及是否有差异,但无法生成同步Destination Table的脚本。
4.         测试的Table是否位于Replication
无论Table是否位于Replication中,只要TableDiff对表的要求,这些表都可以用TableDiff进行处理
5.         测试适用的版本
经常测试,该工具对于SQL Server 2000和SQL Server 2005均支持,由于条件的原因,并未测试SQL Server 7.0
6.         测试速度
测试具有675万条记录的表,如果只比较记录数和Schema,则所需时间1.2秒左右;如果是列级比较并生成T-SQL脚本,则所需时间为150秒左右。
7.         未进行的测试
未进行下面的测试:
包含sql_variant、text、ntext、varchar(max)、nvarchar(max)、image、varbinary(max)类型之一的列
-strict、-bf、-rc、-ri、-t 选项测试
五、    应用TableDiff修复事务复制中的数据差异
1.         事务复制中的故障
对于事务复制,导致复制出错最主要有下面两个故障:
Ø 网络或者服务器故障
这种故障,一般在网络或者服务器恢复后,重新启动Distributor Agent就可以解决。
注: 如果Distributor Agent失败,Distributor Agent会停止, SQL Server 2005会自动重启该Distributor Agent,而SQL Server 2000则不会。
Ø 直接修改订阅者的数据,导致发布冲突
对于这类故障,必须修复冲突的数据才能解决问题,一般来说,解决这类问题有几种方法:
重新初始化(重建发布/订阅)
如果一个发布中只有一个项目,并且数据量小,则通过重新初始化订阅的数据来解决数据冲突的问题比较适合。
使用重新初始化来解决数据冲突,要求在定义发布项目的时候,对于“名称冲突”的处理方式,不能选择“保持现有表不变”。
注: SQL Server 2005可以重新生成初始化快照并且初始化订阅,但SQL Server 2000只能用删除重新建立的方法。
在订阅的表中,修改数据来解决冲突
如果知道冲突的数据是怎么样的,则可以在订阅的表上手工改写数据来解决问题。不过,一般来说,会比较难于知道冲突的数据是怎么样的,所以这种方法可使用性并不太高
在订阅表中,修复数据差异,并且跳过冲突的事务序列
如果数据变化的频率不太高,并且数据量大,发布在包含多个项目时,这种方法比较适用。
2.         修复事务复制中的故障
对于修复事务复制中的故障,一般可以按下面的步骤进行:
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可以用于非复制的表,因此,有时也可以用该工具来实现表的数据同步,或者是生成表的数据插入脚本。

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

MSSQL 2005, 2008自带 表比较工具 tablediff.exe

SQL Server 2005新增工具工具TABLEDIFF,用于比价两张表之间的差异。可以比较行数、架构、列;可将比较结果输出成T-SQL脚本或者输出到文件或者表中。假定的SQL SERVER默认安...
  • php_boy
  • php_boy
  • 2010年12月14日 17:10
  • 1152

tablediff

tablediff是SQLServer自带的一款表数据对比工具,主要针对复制场景,比对两个不同服务器上某个数据库下的表数据是否一致。 使用命令如下 tablediff.exe -sourc...

tablediff工具实用

1. tablediff 是什么? tablediff 实用工具用于比较两个非收敛的表中的数据,它对于排除复制拓扑中的非收敛故障非常有用。  2. tablediff 用哪些用法? 1) . ...

SQL SERVER tablediff比较表是否一致工具

1. tablediff 是什么? tablediff 实用工具用于比较两个非收敛的表中的数据,它对于排除复制拓扑中的非收敛故障非常有用。  2. tablediff 用哪些用法? 1) . 在充当复...

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

事务复制是数据同步中常用的一种手段,复制过程难免会遇到不少问题,就笔者遇到的问题而言,一般有两大类:一类是通过重新启动Distributor Agent即可解决的问题,另一类是因为Subscriber...

ADOBE实用工具,解决注册问题

  • 2009年12月24日 14:23
  • 1019KB
  • 下载

SQL Server大容量复制实用工具bcp下载安装及使用

1.下载安装 SQL Server2008点击这里,SQL Server2016点击这里 2.使用说明 2.1导出全表 2.2用语句导出 2.3文件导入 2.4存储过程 参考文...

JAVA 创建文件和文件夹,删除文件和文件夹,复制文件和文件夹,移动文件和文件夹的实用工具类

JAVA 创建文件和文件夹,删除文件和文件夹,复制文件和文件夹,移动文件和文件夹的实用工具类...

Ubuntu LTS 系统学习使用体会和实用工具软件汇总 6.04 8.04 10.04 12.04 14.04 16.04

Ubuntu LTS 系统学习体会和工具软件汇总 6.04 8.04 10.04 12.04 14.04最早接触Ubuntu是在10年前(6.04),之前用过Red Hat(fedora),Debia...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:使用TableDiff实用工具解决事务复制中的问题
举报原因:
原因补充:

(最多只允许输入30个字)