登录
Microsoft.com
 
Microsoft
 
 
 

none
SQL 2008 R2与SQL 2014之间,使用复制功能,复制的数据不正确. RRS feed

 > 

  • 问题

  • Question
    登录进行投票
    0
    登录进行投票

    三台服务器,使用SQL的复制(事务发布)功能来复制表.

    环境

    • A服务器: SQL 2008 R2 (Microsoft SQL Server Enterprise Edition (64-bit)) 
    • 版本:10.50.6220.0  (刚刚打过SP3补丁)
    • 操作系统 : 6.1.7601
    • B服务器:SQL 2014 (Microsoft SQL Server Enterprise (64-bit))
    • 版本:12.0.4213.0(打过SP1补丁)
    • 操作系统:Microsoft Windows NT 6.3 (9600)
    • C服务器:SQL 2014 (Microsoft SQL Server Enterprise (64-bit))
    • 版本:12.0.4213.0(打过SP1补丁)
    • 操作系统:Microsoft Windows NT 6.3 (9600)

    备注:B和C服务器硬件和软件配置相同.

    SQL复制设置

    1. B和C服务器中都有表T,使用B和C的SQL复制功能中的本地发布,发布表T,发布表T的设置中,都为默认设置,只有"目标对象名称"更改过,B服务器的设置内容为"T_B",C服务器的设置内容为"T_C".(因为B和C都复制各自的T到A中,为了A中表不重名,所以B和C发布的表T都改了目标对象名称)
    2. A服务器分别设置B和C的本地订阅.

    问题

    1. A服务器中的表T_B和T_C经常会比发布源的B和C服务器中的表T的数据条数要多,发现是B的某些数据复制到了表T_C中,C的数据也有些复制到了T_B中.
    2. A服务器中的表T_B和T_C经常会比发布源的B和C服务器中的表T的数据条数要少一些.

    查看复制监视器,没有任何错误.

    刚刚创建本地订阅或者重新初始化所有订阅后,数据是正确的,但过段时间(1小时以内),数据条数就不正确了

    现在我每次要读取A服务器的数据的时候,只能操作B和C的SQL中的复制->本地发布->对应的发布名->右键->重新初始化所有订阅->使用新快照 这个方法来使用.我都要崩溃了,呵呵.求教MS大神,这个有什么解决方法?

    • 已编辑 MaskJeff 2015年8月24日 5:51
    2015年8月24日 5:44
    |

答案

  • Question
    登录进行投票
    0
    登录进行投票

    原因:

    当A本地订阅时,会在A上建立三个存储过程在订阅库的存储过程集中.分别是

    • sp_MSins_dbo表名
    • sp_MSupd_dbo表名
    • sp_MSdel_dbo表名

    而问题就出在这,这三个存储过程的默认名字没有服务器名,没有库名.只有表名.当我的A本地订阅B时,在A服自动创建了这三个SP.当我再次用A订阅C时,SQL再次创建了这三个同名但内容不同的SP(原SP覆盖掉了)!这样B和C向A复制数据时,都调用了最后一次进行订阅操作的C的SP,也就是B和C都对T_C进行复制操作!!

    避免方法,在B和C创建本地发布时,发布表的属性中,要把最下面"语句传递"中的INSERT存储过程,UPDATE存储过程,DELETE存储过程的默认属性值改名.这样来避免A订阅时,因为SP名重复而产生的问题.

    我觉得这应该算是SQL的一个BUG吧?看SQL的本地订阅列表就能看到,在创建本地订阅时,SQL会把订阅的名称按 "本地库名 - 源服务器名.库名:表"名来设置([POS] - [SVR001].[POS]:T_C),也就是说MS其实意识到订阅不同服务器时同名表的问题,那么在发布复制时,为什么不把这3个SP的命名规则也按这个来处理呢?或者表名使用"目标对像名称"的T_B,T_C组成sp_MSins_dboT_X也不会出现问题,又或者当A订阅B后再次订阅C时有个提示....完全静默的处理了......本地订阅的A服在建立本地订阅时,只可以选择订阅的库,其它属性不能设置(也许是我没找到).

    假如A,B,C是不同的公司,在A订阅时,是看不到发布服务器做了怎样的设置,A在不了解的情况下很容易出现我遇到的这个问题.

    解决方法 要不让B或者C改发布属性,要不就B和C的表放在不同的库中.

    摘录sp_MSins_dboT的内容:

    • A第一次订阅B时

    ALTER procedure [dbo].[sp_MSins_dboT]

    ...

    begin  
    insert into [dbo].[T_B](

    ...

    • A第二次订阅C时

    ALTER procedure [dbo].[sp_MSins_dboT]

    ...

    begin  
    insert into [dbo].[T_C](

    ...

    当第二次订阅C时,原sp_MSins_dboT的内容被新的C的sp_MSins_dboT覆盖.






    2015年8月28日 6:51
    |

全部回复

  • Question
    登录进行投票
    0
    登录进行投票

    你可以在 T_b 和 T_c 分别建立 trigger （或者用数据审核），记录一下对 两个表做 insert/update/delee 的是哪些程序，如果都是复制的程序，那么说明你的复制配置得有问题

    如果除了复制，还有其他，那么就是其他程序导致的了

    2015年8月24日 7:04
    |
  • Question
    登录进行投票
    0
    登录进行投票

    我确认了一下数据.

    现在A服务器中T_C表中比C服务器中多了几条数据.我查了一下,多的这几条数据,确实是B服务器中的表T中的数据,但不知道为什么,B服中表T大部分的数据都正常复制到了A服的T_B中,只有几条复制到了T_C中.

    程序上应该没有问题,因为A服务器只是访问数据,没有UPDATE等操作.而且就算有其它的问题,那么T_C中多了这几条数据,T_B也应该有,因为毕竟是从B服务器过来的.

    现状: 

    B服T表中主键的值

    1

    2

    3

    4

    5

    C服T表中主键的值

    101

    102

    103

    104

    105

    正确的情况应该是复制到A服后,表T_B的数据应该为

    1

    2

    3

    4

    5

    表T_C的数据应该为

    101

    102

    103

    104

    105

    但现在是,A服的T_B为

    1

    2

    3

    4

    A服的T_C为

    101

    102

    103

    104

    105

    5          <- 5不应该出现在T_C中,而且为什么T_B中没有5 ?

    2015年8月24日 7:46
    |
  • Question
    登录进行投票
    0
    登录进行投票

    补充一下,我测试了,复制的配置是没有问题的

    我在A服务器上新建了一个TEST库,也新建了同样的本地订阅,唯一区别就是复制到TEST库中.现在TEST库中的数据,然后重新初始化所有订阅,让数据复位,过一小时,当A服和正式库T_B,T_C出比源表条数多时,TEST库条数正常.....所以基本排除了复制的设置问题.

    但好像也不是复制以外的问题.因为我大概排查了一下,没有程序更新表T_B和T_C......

    现在的问题是某条数据,本应出现在T_B中,但出现在了T_C中.这个很奇怪..

    2015年8月24日 7:54
    |
  • Question
    登录进行投票
    0
    登录进行投票
    Is there trigger in any involved table?
    2015年8月24日 13:01
    |
  • Question
    登录进行投票
    0
    登录进行投票
    没有任何触发器在ABC服务器中的相关表T上.
    2015年8月25日 0:40
    |
  • Question
    登录进行投票
    0
    登录进行投票
    刚刚又重新初始化所有订阅,发现了一个问题.B服中复制监视器中报错,提示B服向A服复制表T_C时出现主键冲突!! B服我又确认了一下,复制的表名是T_B,没有T_C,我想这个就是T_B,T_C中数据经常少或者多的原因吧?我正在确认中.
    2015年8月25日 0:44
    |
  • Question
    登录进行投票
    0
    登录进行投票

    发布服务器上，查下 sysarticles

    SELECT * FROM 发布数据库..sysarticles WHERE dest_table IN('T_C', 'T_B')

    2015年8月25日 1:15
    |
  • Question
    登录进行投票
    0
    登录进行投票

    违反了 PRIMARY KEY 约束“PK_T”。不能在对象“dbo.T_C”中插入重复键。重复键值为 (115602)。

    这是T_B的复制监视器中报的错,看起来B服复制表T_B时,确实部分数据复制到了表T_C中.原因不明...

    2015年8月25日 3:49
    |
  • Question
    登录进行投票
    0
    登录进行投票

    发布服务器上，查下 sysarticles

    SELECT * FROM 发布数据库..sysarticles WHERE dest_table IN('T_C', 'T_B')

    返回了一行数据,看del_cmd,upd_cmd,ins_cmd的值都正确.
    • 已编辑 MaskJeff 2015年8月25日 3:59
    2015年8月25日 3:53
    |
  • Question
    登录进行投票
    0
    登录进行投票

    是不是还有其他触发器？

    表属性里面，不用于复制属性是什么

    Love SQL

    2015年8月25日 6:25
    |
  • Question
    登录进行投票
    0
    登录进行投票

    是不是还有其他触发器？

    表属性里面，不用于复制属性是什么


    Love SQL

    看截图,是表字段的属性是吧?除了有一个自增长字段的"不用于复制"属性为是,其它字段均为否.

    现在感觉,A订阅的B和C的发布时候,B和C的发布之间有污染,造成了A服T_B中混入了C服发布的数据,反之A服的T_C中也混入了B发布的数据.

    为了测试是否有"污染",我下午关闭了一个订阅,只单独订阅B服的表T.到现在暂时还没有出现数据不匹配的现象,按这几天的经验,一般1小时内肯定会出现问题.

    2015年8月25日 6:51
    |
  • Question
    登录进行投票
    0
    登录进行投票

    继续报告测试状况.

    今天早上再次确认,A服只订阅B服的情况下,复制的数据完全没有问题.

    出问题时的SQL复制的设置情况:

    • B服的表T本地发布(事务发布) ->  发布属性中,表T的"目标对象名称"为 T_B  -> A服本地订阅B服的复制(T_B)
    • C服的表T本地发布(事务发布) ->  发布属性中,表T的"目标对象名称"为 T_C  -> A服本地订阅C服的复制(T_C)

    问题现象:

    A服同时订阅B的表T和C的表T的时候,数据确实有污染的情况,B服中表T的数据偶尔会有几条更新到了T_C中.反之同样C也复制到了T_B中,已经排除了触发器,或者其它程序更新了表T_B,T_C等原因.并且在B服复制监视器中,捕捉到了一条出错信息"违反了 PRIMARY KEY 约束“PK_T”。不能在对象“dbo.T_C”中插入重复键。重复键值为 (115602)。".确实是B向A事务复制时向T_C插入了数据.

    这是SQL的BUG么?

    2015年8月26日 1:02
    |
  • Question
    登录进行投票
    0
    登录进行投票
    我觉得不可能吧

    Love SQL

    2015年8月26日 2:14
    |
  • Question
    登录进行投票
    0
    登录进行投票

    B服务器捕捉到错误，错误提示是插入 T_C 的， 也就是你的应该同步到 T_B， 出错的是提示的T_C 的表?

    如果是这样的话，出错的时候，根据错误信息中的 xact seqno， 在分发服务器分发库下通过 sp_browsereplcmds  查下复制的命令是什么， 如果是调用的存储过程，你再在订阅服务器订阅数据库查下对应存储过程写的表是否有问题

    2015年8月26日 5:30
    |
  • Question
    登录进行投票
    0
    登录进行投票
    Do you share distributor?
    2015年8月26日 17:04
    |
  • Question
    登录进行投票
    0
    登录进行投票

    B服务器捕捉到错误，错误提示是插入 T_C 的， 也就是你的应该同步到 T_B， 出错的是提示的T_C 的表?

    如果是这样的话，出错的时候，根据错误信息中的 xact seqno， 在分发服务器分发库下通过 sp_browsereplcmds  查下复制的命令是什么， 如果是调用的存储过程，你再在订阅服务器订阅数据库查下对应存储过程写的表是否有问题

    为了正常工作,已经关闭了A与C间的复制,只保留了A与B..现在暂时没有出错信息.

    可以确定的是,事务复制的INS,DEL,UPD这三个都是调用复制功能自建的存储过程,而且理论上表名也没有问题,因为大部分的数据还是正确的进入了自己对应的表,只是小部分会有问题.


    2015年8月28日 1:15
    |
  • Question
    登录进行投票
    0
    登录进行投票
    我觉得不可能吧

    Love SQL

    神奇吧,我也觉得不可能,当查出T_C表中多出来几条应该属于T_B的数据的时候,我首先也是想到是不是其它方面的原因.

    直到复制监视器中捕捉到了出错信息,B向A的T_C插入数据时违反的主键冲突(这个很巧合,因为B和C主键只有小概率的时候是冲突的)才确定是"污染"的问题.

    2015年8月28日 1:19
    |
  • Question
    登录进行投票
    0
    登录进行投票
    Do you share distributor?
    已经共享了复制的目录,而且A服可以正常复制B和C的数据,只是其中的1%的数据会有问题.大部分数据复制是没有问题的.所以应该不是共享目录这个问题.
    2015年8月28日 1:24
    |
  • Question
    登录进行投票
    0
    登录进行投票

    老是猜测问题在哪里，不如 trigger，看看到底谁动了问题数据

    建议你还是弄个 trigger， 把每次操作的 iserted/deleted 和 @@spid 对应的 sysprocess 和 dbcc inputbuffer 的数据都存起来，出异常的时候，根据主键查对应的 sysprocess 和 dbcc inputbuffer 数据

    不过复制异常报错的表名是另一个表，如果没有触发器，那应该是复制存储过程有问题了

    2015年8月28日 3:29
    |
  • Question
    登录进行投票
    0
    登录进行投票

    老是猜测问题在哪里，不如 trigger，看看到底谁动了问题数据

    建议你还是弄个 trigger， 把每次操作的 iserted/deleted 和 @@spid 对应的 sysprocess 和 dbcc inputbuffer 的数据都存起来，出异常的时候，根据主键查对应的 sysprocess 和 dbcc inputbuffer 数据

    不过复制异常报错的表名是另一个表，如果没有触发器，那应该是复制存储过程有问题了

    恩,看来要查出明确的错误原因,只能用你的方法了.

    现在为了保证工作,关闭了A与C的复制,暂时没有再出现错误.等我有机会再测试一下.

    2015年8月28日 5:28
    |
  • Question
    登录进行投票
    0
    登录进行投票

    找出了"污染"的原因.整理资料中,一会回复上来,大家一起了解一下,希望可以帮到未来可能会遇到的朋友.


    • 已编辑 MaskJeff 2015年8月28日 7:06
    2015年8月28日 6:14
    |
  • Question
    登录进行投票
    0
    登录进行投票

    原因:

    当A本地订阅时,会在A上建立三个存储过程在订阅库的存储过程集中.分别是

    • sp_MSins_dbo表名
    • sp_MSupd_dbo表名
    • sp_MSdel_dbo表名

    而问题就出在这,这三个存储过程的默认名字没有服务器名,没有库名.只有表名.当我的A本地订阅B时,在A服自动创建了这三个SP.当我再次用A订阅C时,SQL再次创建了这三个同名但内容不同的SP(原SP覆盖掉了)!这样B和C向A复制数据时,都调用了最后一次进行订阅操作的C的SP,也就是B和C都对T_C进行复制操作!!

    避免方法,在B和C创建本地发布时,发布表的属性中,要把最下面"语句传递"中的INSERT存储过程,UPDATE存储过程,DELETE存储过程的默认属性值改名.这样来避免A订阅时,因为SP名重复而产生的问题.

    我觉得这应该算是SQL的一个BUG吧?看SQL的本地订阅列表就能看到,在创建本地订阅时,SQL会把订阅的名称按 "本地库名 - 源服务器名.库名:表"名来设置([POS] - [SVR001].[POS]:T_C),也就是说MS其实意识到订阅不同服务器时同名表的问题,那么在发布复制时,为什么不把这3个SP的命名规则也按这个来处理呢?或者表名使用"目标对像名称"的T_B,T_C组成sp_MSins_dboT_X也不会出现问题,又或者当A订阅B后再次订阅C时有个提示....完全静默的处理了......本地订阅的A服在建立本地订阅时,只可以选择订阅的库,其它属性不能设置(也许是我没找到).

    假如A,B,C是不同的公司,在A订阅时,是看不到发布服务器做了怎样的设置,A在不了解的情况下很容易出现我遇到的这个问题.

    解决方法 要不让B或者C改发布属性,要不就B和C的表放在不同的库中.

    摘录sp_MSins_dboT的内容:

    • A第一次订阅B时

    ALTER procedure [dbo].[sp_MSins_dboT]

    ...

    begin  
    insert into [dbo].[T_B](

    ...

    • A第二次订阅C时

    ALTER procedure [dbo].[sp_MSins_dboT]

    ...

    begin  
    insert into [dbo].[T_C](

    ...

    当第二次订阅C时,原sp_MSins_dboT的内容被新的C的sp_MSins_dboT覆盖.






    2015年8月28日 6:51
    |
  • Question
    登录进行投票
    0
    登录进行投票
    That's why I asked did you share distributor. If shared, should have this issue. 
    2015年8月28日 23:50
    |
  • Question
    登录进行投票
    0
    登录进行投票
    之前我也遇到过类似的问题：1个服务器数据库中的1张表，做了2个发布，订阅都是同一个数据库，但是一个发布设置新的订阅表名称，避免同一个订阅库中的表名一样。确实有系统存储过程重新创建了，导致另一个订阅的 object_id 没对上
    2015年8月29日 1:16
    |
  • Question
    登录进行投票
    0
    登录进行投票
    That's why I asked did you share distributor. If shared, should have this issue. 
    不好意思,我英文不好,所以你的意思我没有完全看懂.呵呵.谢谢了
    2015年8月30日 23:55
    |
  • Question
    登录进行投票
    0
    登录进行投票
    之前我也遇到过类似的问题：1个服务器数据库中的1张表，做了2个发布，订阅都是同一个数据库，但是一个发布设置新的订阅表名称，避免同一个订阅库中的表名一样。确实有系统存储过程重新创建了，导致另一个订阅的 object_id 没对上
    那咱俩的问题一样,我觉得这个应该算是SQL设计的失误.
    2015年8月30日 23:58
    |
  • Question
    登录进行投票
    0
    登录进行投票

    前面有让你查复制存储过程有没有问题，结果你说没问题，现在还直是这个有问题

    默认的那个存储过程命名是考虑不周的，schema 也是不包含的，表名我记得用的是源表，不是订阅表，用订阅表名就可以避免

    2015年9月1日 2:10
    |
  • Question
    登录进行投票
    0
    登录进行投票

    前面有让你查复制存储过程有没有问题，结果你说没问题，现在还直是这个有问题

    默认的那个存储过程命名是考虑不周的，schema 也是不包含的，表名我记得用的是源表，不是订阅表，用订阅表名就可以避免

    确认是存储过程的问题.呵呵,当初 你说存储过程的时候,我没太在意,因为复制的SP是可以自己来写的,我用的是默认的,所以就忽略了这个问题.

    表名是用的源表.这个问题很坑人.

    2015年9月1日 7:45
    |