运行时间偏移延迟复制的最佳实践

Question

Wes

Asked: 2012-12-05 14:03:47 +0800 CST2012-12-05 14:03:47 +0800 CST 2012-12-05 14:03:47 +0800 CST

SQL Server 2005 合并复制失败

772

我们有一位客户在尝试将客户端（订阅者）同步到服务器（发布者）时收到以下错误：

2012-11-30 21:03:33.334 Percent Complete: 18
2012-11-30 21:03:33.334 Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.334 OLE DB Distributor 'EnvisionSQL\SQL2005': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-11-30 21:03:33.506 Percent Complete: 0
2012-11-30 21:03:33.506 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.521 Percent Complete: 0
2012-11-30 21:03:33.521 Category:NULL
Source:  Merge Process
Number:  -2147200953 
Message: Article 'TB_CORE_DAILY' failed data validation (rowcount only). Rowcount actual: 80367, expected: 0.
2012-11-30 21:03:33.537 Percent Complete: 0
2012-11-30 21:03:33.537 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_INSPECTION_VIOL' failed data validation (rowcount only). Rowcount actual: 19451, expected: 0.

服务器是2005，本地数据库引擎是2005 express。

什么可能导致实际/预期行数不匹配？

我正在尝试对此进行更多研究，所以我想我会重新点燃这个讨论，看看是否有其他人有一些意见。

我了解rowcount验证的工作原理，但为什么我会在具有已知数据的表上收到 0 的预期值？

3 个回答

Voted

Adel Khayata · Answer 1 · 2012-12-05T19:29:42+08:00

Adel Khayata

2012-12-05T19:29:42+08:002012-12-05T19:29:42+08:00

检查订阅者和发布者之间是否有任何冲突，并尝试解决这些冲突。

2

Marcello Miorelli · Answer 2 · 2019-07-25T11:17:49+08:00

我可以在您的错误消息中看到data validation无法完成的操作，尽管正如您所说的publisher和subscriber表格具有完全相同的rowcount.

我认为这不会发生冲突，尽管您可以检查一下。

could not be verified对于与sql 2005 中的合并复制相关的任何错误，如下所示：

错误消息： • 无法验证对发布“MERLIN_PUBLICATION”的订阅。确保正确指定了所有合并代理程序命令行参数并且正确配置了订阅。如果发布者不再有关于此订阅的信息，请删除并重新创建订阅。（来源：MSSQL_REPL，错误号：MSSQL_REPL-2147201019）获取帮助： http://help/MSSQL_REPL-2147201019对象名称“dbo.sysmergepublications”无效。（来源：MSSQLServer，错误号：208）获取帮助：http://help/208

这完全取决于您如何设置合并复制。例如，如果你把所有东西都切成小块，这意味着每个订阅者只得到自己的份额，并且为了检查他们需要连接到主服务器的全部内容，那么在大多数情况下运行快照并不是什么大问题。

然而，如果每个人都在他们的机器上拥有一切，那么快照运行可能需要一段时间。只要有可能，我尽量不去谈那件事。

我可以说几件事，可能我不得不发布不止一个答案。

在合并复制中，您需要查看的所有内容都在数据库内部。

无论你做什么，在你做之前备份所有的表。

例如：

select * into MSMerge_Replinfo_20130116 from MSMerge_Replinfo

假设我有一个名为的服务器ODS并且我的所有订阅者都被命名为MERLIN SOMETHING 我的数据库被称为concord_ord

看看这个：

-- #### ODS

use concord_ods
go

select top 10 
recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)
where subscriber_server = 'MERLIN026'


-- ODS the original after the reinitialisation
-- ODS recgen   recguid                                 sentgen sentguid    
-- ODS 71374    7D24ABFE-DC96-4C7C-BCC7-F48830F82493    6336565 7D24ABFE-DC96-4C7C-BCC7-F48830F82493    


-- ODS - I modified but it did not work
-- ODS recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- ODS 5933523  1530D5D0-61E9-4000-9BD5-F01F3D08601A    6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082    8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E


-- ### merlin026

--merlin026 bef.rei. recgen  recguid                                sentgen sentguid
--merlin026 bef.rei. 6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082   5933523 1530D5D0-61E9-4000-9BD5-F01F3D08601A

--merlin026 aft.rei. recgen  recguid                                sentgen sentguid
--merlin026 aft.rei. 6454923 621E577A-80D3-4177-AFF1-A226F912E779   6454674 16D59CEE-9191-44E5-B8A2-C6C45570A004

然后转到您的订阅者并检查那里的数据：例如：

use concord_ods
go


select top 10 
recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)
where subscriber_server = 'TCP-ODS01'

-- ODS - I modified but it did not work
-- ODS recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- ODS 5933523  1530D5D0-61E9-4000-9BD5-F01F3D08601A    6278697 1F0CBCD4-B959-44C9-B120-77FC09EC5082    8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E


-- 026 recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- 026 6278697  1F0CBCD4-B959-44C9-B120-77FC09EC5082    5933523 1530D5D0-61E9-4000-9BD5-F01F3D08601A    8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963

-- After the reinitialisation
-- 026 recgen   recguid                                 sentgen sentguid                                pubid                                   subid                                   replnickname
-- 026 6454923  621E577A-80D3-4177-AFF1-A226F912E779    6454674 16D59CEE-9191-44E5-B8A2-C6C45570A004    8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963


select top 10 
subscriber_server ,recgen,recguid,sentgen,sentguid,pubid,subid,replnickname 
 from sysmergesubscriptions with (nolock)

--on MERLIN026
--three subscriptions?
--subid:
--85A04E38-78AA-41F6-9956-3538656F0912
--A27948E7-54C3-4EBB-9D3F-DFF46D723313
--534EFA1C-3CE7-4C93-9943-F580CD7364F4

-- none of these match to ODS subid:
--9F26E61E-AF33-4F83-95E6-32AD98155048

-- hypothesis: mismatched subid is causing synchronisation failure

-- proposed solution: update to subid to bring it in line with ODS subid
-- this should allow the two databases to connect

-- check to see where the subid appears in the database
select * from sys.columns
where name = 'subid'

select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'subid'
    )


-- check these tables for data    
select * from MSmerge_identity_range -- 51 rows
select top 10 * from MSmerge_altsyncpartners -- 0
select top 10 * from MSmerge_supportability_settings -- 0
select * from MSmerge_sessions -- 10 rows
select top 10 * from MSmerge_log_files -- 0


-- keep data, just in case it all goes wrong and we need to roll back the change
select * into  dbo.MSmerge_sessions_20130116 from MSmerge_sessions 

select * from MSmerge_sessions 

--I spotted that over 8 records had a different subid, and the rest 
--rest of the records had the correct subid, therefore I deleted those
--which had a wrong subid
delete from MSmerge_sessions where subid <> '9F26E61E-AF33-4F83-95E6-32AD98155048'

-- we also need to align the replnickname
-- so make sure this isn't used anywhere else

select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'replnickname'
    )
-- just in sysmergesubscriptions

-- the subid is repid in MSMerge_Replinfo
select * from MSMerge_Replinfo

-- I found 2 rows on MSMerge_Replinfo
-- where the repid is not '9F26E61E-AF33-4F83-95E6-32AD98155048'
-- I will remove them

select * into MSMerge_Replinfo_20130116 from MSMerge_Replinfo

select * from MSMerge_Replinfo

delete from MSMerge_Replinfo
where repid <> '9F26E61E-AF33-4F83-95E6-32AD98155048'


-- make sure this doesn't appear anywhere else
select * from sys.tables
where object_id in (
    select object_id from sys.columns
    where name = 'repid'
    )
-- just in MSMerge_Replinfo


    select status, replnickname from sysmergesubscriptions
    where subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'

-- use a transaction so that we can make further checks post-update
begin transaction
select @@trancount

    -- update tables
    update sysmergesubscriptions
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
        , status = 1
        , replnickname = 0xAF339F26E61E
    where subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'

    update  MSMerge_Replinfo
    set repid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
    where repid = '85A04E38-78AA-41F6-9956-3538656F0912'

    update MSmerge_sessions 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'

    update MSmerge_identity_range 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'

    -- check result
    select * from sysmergesubscriptions with (readuncommitted)
    select * from MSMerge_Replinfo with (readuncommitted)
    select * from MSmerge_sessions with (readuncommitted)
    select * from MSmerge_identity_range with (readuncommitted)

-- result is good, commit
--rollback transaction
commit transaction
select @@trancount -- 0



select subscriber_server,pubid, subid,replnickname,recgen,sentgen,recguid,sentguid 
from sysmergesubscriptions with (readuncommitted)
where subscriber_server = 'MERLIN026'
   or subscriber_server = 'MERLIN864'


--merlin026 - not working
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  6278697 5933523 1F0CBCD4-B959-44C9-B120-77FC09EC5082    1530D5D0-61E9-4000-9BD5-F01F3D08601A
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    85A04E38-78AA-41F6-9956-3538656F0912    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL
--MERLIN026         2F0867E6-4580-4DB9-9405-9C836413B7D2    A27948E7-54C3-4EBB-9D3F-DFF46D723313    0x78AA85A04E38  NULL    NULL    NULL                                    NULL
--MERLIN026 7       C2C1785-0FE1-4717-9E53-BAAFD889B89A     534EFA1C-3CE7-4C93-9943-F580CD7364F4    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL



--merlin864 - working fine
--sysmergesubscriptions 
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6456716 2857356 18607229-F77A-4768-89D3-DEF82775340A    6A93A5F5-AC77-4A96-9472-4B64EAFF4435
--MERLIN864         8E72F963-2C85-4005-BC5F-8494A1BE23BE    776E13B8-6997-4ABD-9F55-C277EF86A3B0    0xEFBB4CCC71C0  NULL    NULL    NULL                                    NULL

-- ODS
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  5933523 6278697 1530D5D0-61E9-4000-9BD5-F01F3D08601A    1F0CBCD4-B959-44C9-B120-77FC09EC5082
--MERLIN864         8E72F963-2C85-4005-BC5F-8494A1BE23BE    776E13B8-6997-4ABD-9F55-C277EF86A3B0    0xEFBB4CCC71C0  2857356 6456716 6A93A5F5-AC77-4A96-9472-4B64EAFF4435    18607229-F77A-4768-89D3-DEF82775340A

--merlin026 - after the reinitialisation
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6454923 6454674 621E577A-80D3-4177-AFF1-A226F912E779    16D59CEE-9191-44E5-B8A2-C6C45570A004
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  NULL    NULL    NULL                                    NULL


-- MERLIN026 after update below
--subscriber_server pubid                                   subid                                   replnickname    recgen  sentgen recguid                                 sentguid
--TCP-ODS01         8E72F963-2C85-4005-BC5F-8494A1BE23BE    8E72F963-2C85-4005-BC5F-8494A1BE23BE    0x2C858E72F963  6278697 5933523 1F0CBCD4-B959-44C9-B120-77FC09EC5082    1530D5D0-61E9-4000-9BD5-F01F3D08601A
--MERLIN026         8E72F963-2C85-4005-BC5F-8494A1BE23BE    9F26E61E-AF33-4F83-95E6-32AD98155048    0xAF339F26E61E  NULL    NULL    NULL                                    NULL

--MERLIN026         2F0867E6-4580-4DB9-9405-9C836413B7D2    A27948E7-54C3-4EBB-9D3F-DFF46D723313    0x78AA85A04E38  NULL    NULL    NULL                                    NULL
--MERLIN026         7C2C1785-0FE1-4717-9E53-BAAFD889B89A    534EFA1C-3CE7-4C93-9943-F580CD7364F4    0xBD244C8F11E7  NULL    NULL    NULL                                    NULL

-- ## MERLIN026
use concord_ods
go

select @@servername

begin tran T01

    update sysmergesubscriptions 
    set subid = '8E72F963-2C85-4005-BC5F-8494A1BE23BE'
      ,replnickname = 0x2C858E72F963
    where subscriber_server = 'TCP-ODS01'

    update sysmergesubscriptions 
    set subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'
      ,replnickname = 0xAF339F26E61E
    where subscriber_server = 'MERLIN026'
      and subid = '85A04E38-78AA-41F6-9956-3538656F0912'

    -- make sure that the status is 1 otherwise it does not verify
    update sysmergesubscriptions 
    set status = 1
    where subscriber_server = 'MERLIN026'
      and subid = '9F26E61E-AF33-4F83-95E6-32AD98155048'


    select subscriber_server,pubid, subid,replnickname,recgen,sentgen,recguid,sentguid 
    from sysmergesubscriptions with (readuncommitted)


--rollback tran T01
--commit tran T01

select @@trancount

我知道要承担很多事情。但如果您想避免运行快照，这是值得的。

在上面的这种情况下，一台服务器得到修复，一台需要重新订阅。

Marcello Miorelli · Answer 3 · 2019-07-25T11:25:29+08:00

只是为了补充或添加到其他答案

有时您会收到一条非常毛茸茸的消息

我做的第一件事是打开冗长的：

Turn on verbose history (Replication monitor->Agent Profile->Verbose History) and use the -output parameter and select a destination for the output file (Subscriber Replication Job -> Edit Run Agent Job -> In the command window add -output C:\MyFilePath\FileName.log).

和：

查看下面命令的最新部分，您会看到输出参数。

-Publisher [TCP-ODS01] -PublisherDB [concord_ods] -Publication [MERLIN_PUBLICATION] -Subscriber [MERLIN771] -SubscriberDB [ConCORD_ODS]   -Distributor [TCP-MERGE01] -DistributorSecurityMode 1 -HostName [MERLIN771]  -output C:\Replication\MERLIN771_1132.log

现在我们可以查看日志文件。

现在您可以查看生成的日志并从那里继续。示例（见下图）：

作业上的命令是：

--This is the MERLIN307 subscriber, I am troubleshooting, outputting to the log at c:\replication and using the - VerbatimTextObjectScripting 0 option.
-Publisher [TCP-ODS01] -PublisherDB [concord_ods] -Distributor [TCP-MERGE01] -Publication [MERLIN_PUBLICATION] -ReplicationType 2 -DistributorSecurityMode 1  -DynamicFilterHostName [MERLIN307] -DynamicSnapshotLocation [\\TCP-MERGE01\REPLDATA\\unc\TCP-ODS01_CONCORD_ODS_MERLIN_PUBLICATION\MERLIN307_9\] -PartitionId 9  -VerbatimTextObjectScripting 0 -output C:\Replication\MERLIN307-983.log

对于您的情况，我真的不能直接给您一个明确的答案，但我绝对可以使用上述这些步骤帮助您找出问题所在。

如果这听起来令人困惑，请在下面发表评论，我会尝试更具体一些。

SQL Server 2005 合并复制失败

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

SQL Server 2005 合并复制失败

3 个回答

相关问题