使用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 三、 使用说明 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. 未进行的测试 未进行下面的测试: n 包含sql_variant、text、ntext、varchar(max)、nvarchar(max)、image、varbinary(max)类型之一的列 n -strict、-bf、-rc、-ri、-t 选项测试 五、 应用TableDiff修复事务复制中的数据差异 1. 事务复制中的故障 对于事务复制,导致复制出错最主要有下面两个故障: Ø 网络或者服务器故障 这种故障,一般在网络或者服务器恢复后,重新启动Distributor Agent就可以解决。 注: 如果Distributor Agent失败,Distributor Agent会停止, SQL Server 2005会自动重启该Distributor Agent,而SQL Server 2000则不会。 Ø 直接修改订阅者的数据,导致发布冲突 对于这类故障,必须修复冲突的数据才能解决问题,一般来说,解决这类问题有几种方法: n 重新初始化(重建发布/订阅) 如果一个发布中只有一个项目,并且数据量小,则通过重新初始化订阅的数据来解决数据冲突的问题比较适合。 使用重新初始化来解决数据冲突,要求在定义发布项目的时候,对于“名称冲突”的处理方式,不能选择“保持现有表不变”。 注: SQL Server 2005可以重新生成初始化快照并且初始化订阅,但SQL Server 2000只能用删除重新建立的方法。 n 在订阅的表中,修改数据来解决冲突 如果知道冲突的数据是怎么样的,则可以在订阅的表上手工改写数据来解决问题。不过,一般来说,会比较难于知道冲突的数据是怎么样的,所以这种方法可使用性并不太高 n 在订阅表中,修复数据差异,并且跳过冲突的事务序列 如果数据变化的频率不太高,并且数据量大,发布在包含多个项目时,这种方法比较适用。 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可以用于非复制的表,因此,有时也可以用该工具来实现表的数据同步,或者是生成表的数据插入脚本。 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/zjcxc/archive/2006/12/27/1464953.aspx |
|
来自: Mike Lee > 《SQL Server》