SqlServer 禁止架构更改的复制中手动修复使发布和订阅中分别增加的字段同步

由于之前的需要,禁止了复制架构更改,以至在发布中添加一个字段,并不会同步到订阅中,而现在又在订阅中添加了一个同名字段,怎么使这发布和订阅的两个字段建立同步关系呢?


下面就测试更改:此次发布类型为事务复制的可更新订阅,其他类型的发布没有测试。


首先建立事务复制的可更新订阅,建立好之后。


在发布创建一张测试表:

[sql]  view plain  copy
  1. CREATE TABLE [dbo].[DemoTab](  
  2.     [Guid] [uniqueidentifier] NOT NULL,  
  3.     [SID] [varbinary](85) NOT NULL,  
  4.     [Title] [nvarchar](100) NOT NULL,  
  5.     CONSTRAINT [PK_DemoTab] PRIMARY KEY CLUSTERED ([SID] ASC,[Guid] ASC)  
  6. ON [PRIMARY]  
  7. GO  
  8. ALTER TABLE [dbo].[DemoTab] ADD  CONSTRAINT [DF_DemoTab_Guid]  DEFAULT (newsequentialid()) FOR [Guid]  
  9. GO  
  10. ALTER TABLE [dbo].[DemoTab] ADD  CONSTRAINT [DF_DemoTab_SID]  DEFAULT (suser_sid()) FOR [SID]  
  11. GO  

将表添加发布,并初始化该表:

[sql]  view plain  copy
  1. Exec sp_addarticle   
  2.   @publication = 'publication'  
  3. , @article = N'DemoTab'  
  4. , @source_object = N'DemoTab'  
  5. , @source_owner = N'dbo'  
  6. , @schema_option = 0x0000000008037CDF  
  7. , @vertical_partition = N'true'  
  8. GO   
  9. Exec sp_refreshsubscriptions 'publication'   
  10. GO   
  11. Exec sp_startpublication_snapshot 'publication'  
  12. GO  

分表在发布和订阅都测试,确认同步正常:

[sql]  view plain  copy
  1. INSERT INTO [DemoTab](Guid,SID,Title) select NEWID(),SUSER_SID(),'test'  
  2. UPDATE [DemoTab] SET Title = 'KK'   
  3. DELETE FROM [DemoTab]  

现在禁用 "复制架构更改"


也可以用脚本禁止:

[sql]  view plain  copy
  1. --  禁止 "复制架构更改"  
  2. EXEC sp_changepublication @publication = N'publication', @property = N'replicate_ddl', @value = 0  

现在分别在发布数据库和订阅数据库执行增加字段,因为架构不同步,所以并不会冲突。

[sql]  view plain  copy
  1. ALTER TABLE dbo.[DemoTab] ADD TEST INT NULL  

增加后,只要不对新增的列 test 操作,复制仍正常。但要禁止客户对该表进行操作,因为更改过程中涉及的对象要更改,别面出错!


首先介绍主要参考的3个存储过程,都在发布数据库执行.(注:可执行查看,但生成的脚本不要执行

[sql]  view plain  copy
  1. --订阅的所有同步存储过程.生成脚本在订阅库执行  
  2. EXEC sp_scriptpublicationcustomprocs N'publication'  
  3.   
  4. --发布中的冲突表.生成脚本在发布库执行  
  5. EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'  
  6.   
  7. --订阅触发器.生成脚本在订阅库执行  
  8. EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'  

将新增的列添加到发布项目中(在发布数据库执行)

[sql]  view plain  copy
  1. --查看发布列的信息  
  2. select name,column_id from sys.columns where object_id=object_id('DemoTab')  
  3. select artid from sysarticles where name='DemoTab'  
  4. select * from sysarticlecolumns where artid=1044  

从上面可以知道,字段 [TEST]  的id=8 , 表 [DemoTab] 的发布项目编号 artid = 1044,当前字段[TEST]并未添加到发布表中,下面将该列添加到发布

[sql]  view plain  copy
  1. insert into sysarticlecolumns select 1044,8,0,0,0  
上面的语句执行后,可以在界面中看到该列已经添加进去。



现在执行上面所说的3个存储过程,在发布数据库执行。该存储过程生成订阅的同步存储过程,生成的脚本在订阅数据库执行(手动应用快照)

[sql]  view plain  copy
  1. EXEC sp_scriptpublicationcustomprocs N'publication'  

[sql]  view plain  copy
  1. --  
  2. -- 来自数据库 'publisherdb'、发布 'publication' 的事务复制自定义过程:  
  3. --  
  4.   
  5.   
  6. ----  
  7. ---- 项目 'DemoTab' 的复制自定义过程:  
  8. ----  
  9.   
  10. if object_id(N'[sp_MSins_dboDemoTab]''P') > 0  
  11. drop proc [sp_MSins_dboDemoTab]  
  12. go  
  13. if object_id(N'dbo.MSreplication_objects'is not null  
  14. delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboDemoTab'  
  15. go  
  16. create procedure [sp_MSins_dboDemoTab]   
  17.   @c1 uniqueidentifier,@c2 varbinary(85),@c3 nvarchar(100),@c4 uniqueidentifier,@c5 int  
  18. as   
  19. begin   
  20. if not exists (select * from [dbo].[DemoTab]   
  21.  where  ( [SID] = @c2 and [Guid] = @c1 )   
  22. )  
  23. begin  
  24. insert into [dbo].[DemoTab](   
  25.  [Guid]  
  26. ,[SID]  
  27. ,[Title]  
  28. ,[msrepl_tran_version]  
  29. ,[TEST]  
  30.  )  
  31. values (   
  32.  @c1  
  33. ,@c2  
  34. ,@c3  
  35. ,@c4  
  36. ,@c5  
  37.  )   
  38. end  
  39. end  
  40. go  
  41. if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article''AllowsNull'is not null   
  42. exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)  
  43.  values (+ N''sp_MSins_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')  
  44. go  
  45.   
  46. if object_id(N'[sp_MSupd_dboDemoTab]''P') > 0  
  47. drop proc [sp_MSupd_dboDemoTab]  
  48. go  
  49. if object_id(N'dbo.MSreplication_objects'is not null   
  50. delete from dbo.MSreplication_objects where object_name = N'sp_MSupd_dboDemoTab'  
  51. go  
  52. create procedure [sp_MSupd_dboDemoTab]   
  53.  @c1 uniqueidentifier,@c2 varbinary(85),@c3 nvarchar(100),@c4 uniqueidentifier,@c5 int,@pkc1 uniqueidentifier,@pkc2 varbinary(85)  
  54. ,@old_msrepl_tran_version uniqueidentifier   
  55. ,@bitmap binary(1)  
  56. as  
  57. begin  
  58. if ( substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,1) & 2 = 2 )  
  59. begin  
  60. update [dbo].[DemoTab] set   
  61.  [Guid] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Guid] end  
  62. ,[SID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [SID] end  
  63. ,[Title] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Title] end  
  64. ,[msrepl_tran_version] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [msrepl_tran_version] end  
  65. ,[TEST] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [TEST] end  
  66. where [Guid] = @pkc1 and [SID] = @pkc2  
  67.  and msrepl_tran_version = @old_msrepl_tran_version   
  68. end  
  69. else  
  70. begin  
  71. update [dbo].[DemoTab] set   
  72.  [Title] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Title] end  
  73. ,[msrepl_tran_version] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [msrepl_tran_version] end  
  74. ,[TEST] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [TEST] end  
  75. where [Guid] = @pkc1 and [SID] = @pkc2  
  76.  and msrepl_tran_version = @old_msrepl_tran_version   
  77. end  
  78. end  
  79. go  
  80. if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article''AllowsNull'is not null   
  81. exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)   
  82. values (+ N''sp_MSupd_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')  
  83. go  
  84.   
  85. if object_id(N'[sp_MSdel_dboDemoTab]''P') > 0  
  86. drop proc [sp_MSdel_dboDemoTab]  
  87. go  
  88. if object_id(N'dbo.MSreplication_objects'is not null  
  89. delete from dbo.MSreplication_objects where object_name = N'sp_MSdel_dboDemoTab'  
  90. go  
  91. create procedure [sp_MSdel_dboDemoTab]   
  92.   @pkc1 uniqueidentifier,@pkc2 varbinary(85)  
  93. ,@msrepl_tran_version uniqueidentifier   
  94. as   
  95. begin   
  96. delete [dbo].[DemoTab]  
  97. where [Guid] = @pkc1 and [SID] = @pkc2  
  98.  and msrepl_tran_version = @msrepl_tran_version   
  99. end   
  100. go  
  101. if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article''AllowsNull'is not null   
  102. exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)   
  103. values (+ N''sp_MSdel_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')  
  104. go  

此存储过程生成应用于订阅的跟踪项目信息和同步触发器。 生成的脚本在订阅服务器的订阅数据库上执行。 此存储过程在发布服务器的发布数据库中执行。

[sql]  view plain  copy
  1. EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'  

[sql]  view plain  copy
  1. if @@microsoftversion<0x07320000   
  2. raiserror('当发布服务器为 SQL Server 2000 或更高版本时,必须将订阅服务器升级到 SQL Server 2000 才能创建可更新订阅。',16, -1)  
  3.   
  4.   
  5. if (@@microsoftversion >= 0x09000000)   
  6. begin   
  7.     exec sp_addqueued_artinfo 1044  
  8.     , N'DemoTab'  
  9.     , N'publisher'  
  10.     , N'publicationDB'  
  11.     , N'publication'  
  12.     , N'DemoTab'  
  13.     , N'dbo'  
  14.     , N'conflict_publication_DemoTab'   
  15. end   
  16.   
  17. if (@@microsoftversion < 0x09000000)   
  18. begin   
  19.     exec sp_addqueued_artinfo 1044  
  20.     , N'DemoTab'  
  21.     , N'publisher'  
  22.     , N'publicationDB'  
  23.     , N'publication'  
  24.     , N'DemoTab'  
  25.     , N'dbo'  
  26.     , N'conflict_publication_DemoTab'  
  27.     , 0x000000000000000000000000000000000000000000000000000000000000008f   
  28. end   
  29.   
  30. if (@@microsoftversion >= 0x09000000)   
  31. begin   
  32.     delete from MSsubscription_articlecolumns   
  33.     where artid = 1044   
  34.     and agent_id = (  
  35.         select id from MSsubscription_agents   
  36.         where update_mode > 0   
  37.         and UPPER(publisher) = UPPER(N'publisher')   
  38.         and publisher_db = N'publicationDB'   
  39.         and publication = N'publication'   
  40.     )   
  41. end   
  42.   
  43. if (@@microsoftversion >= 0x09000000)   
  44. begin   
  45.     declare @agent_id_1044 int   
  46.     select @agent_id_1044 = id   
  47.     from MSsubscription_agents   
  48.     where update_mode > 0   
  49.     and UPPER(publisher) = UPPER(N'publisher')   
  50.     and publisher_db = N'publicationDB'   
  51.     and publication = N'publication'   
  52.       
  53.     if @agent_id_1044 is not null   
  54.     begin   
  55.         insert MSsubscription_articlecolumns (agent_id, artid, colid)   
  56.         values (@agent_id_1044, 1044, 1)   
  57.         insert MSsubscription_articlecolumns (agent_id, artid, colid)   
  58.         values (@agent_id_1044, 1044, 2)   
  59.         insert MSsubscription_articlecolumns (agent_id, artid, colid)   
  60.         values (@agent_id_1044, 1044, 3)   
  61.         insert MSsubscription_articlecolumns (agent_id, artid, colid)   
  62.         values (@agent_id_1044, 1044, 4)   
  63.         insert MSsubscription_articlecolumns (agent_id, artid, colid)   
  64.         values (@agent_id_1044, 1044, 8)    
  65.     end   
  66. end   
  67.   
  68. if (@@microsoftversion >= 0x080002C0)  
  69. begin   
  70.     exec sp_addsynctriggers   
  71.      N'DemoTab'  
  72.     , N'dbo'  
  73.     , N'publisher'  
  74.     , N'publicationDB'  
  75.     , N'publication'  
  76.     , N'sp_MSsync_ins_DemoTab_4'  
  77.     , N'sp_MSsync_upd_DemoTab_4'  
  78.     , N'sp_MSsync_del_DemoTab_4'  
  79.     , N'sp_MScft_publication_DemoTab'  
  80.     , N'dbo'  
  81.     , N'null'  
  82.     , N'null'  
  83.     , N'null'  
  84.     , 0x03  
  85.     , 0  
  86.     ,1  
  87.     ,N'publisher'  
  88.     , 2   
  89. end   
  90.   
  91. if (@@microsoftversion < 0x080002C0)   
  92. begin   
  93.     exec sp_addsynctriggers   
  94.      N'DemoTab'  
  95.     , N'dbo'  
  96.     , N'publisher'  
  97.     , N'publicationDB'  
  98.     , N'publication'  
  99.     , N'sp_MSsync_ins_DemoTab_4'  
  100.     , N'sp_MSsync_upd_DemoTab_4'  
  101.     , N'sp_MSsync_del_DemoTab_4'  
  102.     , N'sp_MScft_publication_DemoTab'  
  103.     , N'dbo'  
  104.     , N'null'  
  105.     , N'null'  
  106.     , N'null'  
  107.     , 0x03  
  108.     , 0  
  109.     ,1  
  110.     ,N'publisher'   
  111. end   


订阅相关对象已经更改完成!接下来还得改发布中的一些对象!~


查看发布数据库中涉及的对象:

[sql]  view plain  copy
  1. SELECT * FROM sysarticleupdates WHERE artid=(select artid from sysarticles where name='DemoTab')  

对应d 对象,这些对象都需要更改!~

[sql]  view plain  copy
  1. select   
  2. case   
  3.     when object_id=427877337 then 'sync_ins_proc'  
  4.     when object_id=443877394 then 'sync_upd_proc'  
  5.     when object_id=459877451 then 'sync_del_proc'  
  6.     when object_id=475877508 then 'sync_upd_tri'  
  7.     when object_id=491877565 then 'conflict_tableid'  
  8.     when object_id=539877736 then 'ins_conflict_proc'  
  9.     else '' end as sysarticleupdates  
  10. ,object_id,name  
  11. from sys.objects   
  12. where object_id in(427877337,443877394,459877451,475877508,491877565,539877736)  


6 个对象,将逐个更改!(sp_MSsync_upd_trig_DemoTab_4 不需要更改)

1. 更改冲突表 conflict_publication_DemoTab

重新创建冲突表.在发布数据库执行,生成的脚本在发布数据库执行.

[sql]  view plain  copy
  1. EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'  
[sql]  view plain  copy
  1. if object_id(N'[dbo].[conflict_publication_DemoTab]'is not null   
  2. begin DROP TABLE [dbo].[conflict_publication_DemoTab] end     
  3. CREATE TABLE [dbo].[conflict_publication_DemoTab](  
  4.     [Guid] uniqueidentifier NOT NULL  
  5.     ,[SID] varbinary(85) NOT NULL  
  6.     ,[Title] nvarchar(100) NOT NULL  
  7.     ,[msrepl_tran_version] uniqueidentifier NOT NULL  
  8.     ,[TEST] int NULL  
  9.     ,origin_datasource nvarchar(255) NULL  
  10.     ,conflict_type int NULL  
  11.     ,reason_code int NULL  
  12.     ,reason_text nvarchar(720) NULL  
  13.     ,pubid int NULL  
  14.     ,tranid nvarchar(40) NULL  
  15.     ,insertdate datetime NOT NULL  
  16.     ,qcfttabrowid uniqueidentifier DEFAULT NEWID() NOT NULL  
  17. )         
  18. CREATE UNIQUE INDEX [cftind_publication_DemoTab]   
  19. ON [dbo].[conflict_publication_DemoTab]([SID], [Guid], tranid, qcfttabrowid)  

重建表之后,object_id 不一样,sysarticleupdates 的冲突表id需要更改。
[sql]  view plain  copy
  1. update sysarticleupdates   
  2. set conflict_tableid = (select object_id from sys.objects where object_id=object_id('conflict_publication_DemoTab'))  
  3. where artid=(select artid from sysarticles where name='DemoTab')  

2.修改更新冲突表的存储过程

[sql]  view plain  copy
  1. exec sp_helptext sp_MScft_publication_DemoTab  


3. 更改发布中的存储过程

发布库这 3 个存储过程:sp_MSsync_del_DemoTab_4,sp_MSsync_ins_DemoTab_4,sp_MSsync_upd_DemoTab_4
这3 个存储过程分别被订阅数据库表的触发器调用,因此注意传参的位置.新字段[test]参数在[msrepl_tran_version]之后

trg_MSsync_del_DemoTab 调用存储过程 sp_MSsync_del_DemoTab_4
trg_MSsync_ins_DemoTab 调用存储过程 sp_MSsync_ins_DemoTab_4
trg_MSsync_upd_DemoTab 调用存储过程 sp_MSsync_upd_DemoTab_4


这3个存储改动的地方较多,但是都是更改相应的列。里面要加入参数,或者字段赋值等,字段test参考其他字段就行,参数只是改变个编号。

 [sp_MSsync_del_DemoTab_4]  增加传递的参数 @c5_old  INT ,修改一处位置如下。


[sp_MSsync_ins_DemoTab_4] 增加传递的参数 @c5  INT 

更改2处相同代码:

[sql]  view plain  copy
  1. insert into [dbo].[DemoTab](  [Guid] , [SID] , [Title] , [msrepl_tran_version] ,TEST )     
  2. values (  @c1 , @c2 , @c3 , @c4 ,@c5 )    
更改1处代码:

sp_MSsync_upd_DemoTab_4 增加2个参数 @c6 int  ,,@c6_old int  ,更改几处脚本

 参数位置参考订阅触发器 trg_MSsync_upd_DemoTab 中传递的位置 :@c1,@c2,@c3,@c4,@c6  ,@c1_old,@c2_old,@c3_old,@c4_old,@c6_old  

注: 上面的参数应该都以触发器中的名称编号一样,不是@c5, 而是 @c6 ,避免以后再添加列时导致错乱!~上面的应该改!(刚忘记 了,就不回去改了)


注意这里,前8列获取第一个字节 substring(@bitmap,1,1) ,每列对应一个位,第5列为 00010000, 为16.注意改这里。

(更多参考 深入理解SQL Server 2005 中的 COLUMNS_UPDATED函数 )


发布对象更改完成!~



现在进行测试,分别在发布库和订阅库执行一遍,数据正常同步!~

[sql]  view plain  copy
  1. INSERT INTO [DemoTab](Guid,SID,Title) select NEWID(),SUSER_SID(),'test'  
  2. UPDATE [DemoTab] SET Title = 'KK'   
  3. DELETE FROM [DemoTab]  
  4.   
  5. INSERT INTO [DemoTab](Guid,SID,Title,test) select NEWID(),SUSER_SID(),'test',0  
  6. UPDATE [DemoTab] SET test = 10  
  7. DELETE FROM [DemoTab]  
  8.   
  9. ALTER TABLE dbo.[DemoTab] DROP COLUMN TEST   

最终删除该列 【test】,订阅库中也自动同步被删除!~同步正常!~试验结束!~



总结:

手动更改比较麻烦!~尤其是在发布库,没有找到相关自动生成的存储过程(觉得应该是有的),所以发布的对象一个个更改!~刚开始的时候,本人连订阅的对象都是一个个更改!~后来测试几次总结出这个顺序。

[sql]  view plain  copy
  1. --发布库执行,将新增的字段添加到发布.  
  2. select name,column_id from sys.columns where object_id=object_id('DemoTab')  
  3. select artid from sysarticles where name='DemoTab'  
  4. select * from sysarticlecolumns where artid=1044  
  5.   
  6. --发布库执行,生成的脚本在订阅执行.  
  7. EXEC sp_scriptpublicationcustomprocs N'publication'  
  8. EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'  
  9. EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'  
  10.   
  11. --发布库执行,修改发布相关对象  
  12. SELECT * FROM sysarticleupdates WHERE artid=  


为什么要这样做?有时候有的表没有同步、有时某些对象被删除、或者原本不行同步,现在又同步了(如开始说明)。

这里还没完全测试,比如在发布和订阅的字段都有数据时,会不会也是同样处理?


========================

复制分发:架构变更,如何处理,自动同步。问题

always on自动同步,与mysql是一致的。


https://blog.csdn.net/kk185800961/article/details/47614779

相关文章
相关标签/搜索