开发者社区> 杰克.陈> 正文

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

简介: 原文:SqlServer 禁止架构更改的复制中手动修复使发布和订阅中分别增加的字段同步 由于之前的需要,禁止了复制架构更改,以至在发布中添加一个字段,并不会同步到订阅中,而现在又在订阅中添加了一个同名字段,怎么使这发布和订阅的两个字段建立同步关系呢? 下面就测试更改:此次发布类型为事务复制的可更新订阅,其他类型的发布没有测试。
+关注继续查看
原文:SqlServer 禁止架构更改的复制中手动修复使发布和订阅中分别增加的字段同步

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


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


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


在发布创建一张测试表:

CREATE TABLE [dbo].[DemoTab](
	[Guid] [uniqueidentifier] NOT NULL,
	[SID] [varbinary](85) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	CONSTRAINT [PK_DemoTab] PRIMARY KEY CLUSTERED ([SID] ASC,[Guid] ASC)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DemoTab] ADD  CONSTRAINT [DF_DemoTab_Guid]  DEFAULT (newsequentialid()) FOR [Guid]
GO
ALTER TABLE [dbo].[DemoTab] ADD  CONSTRAINT [DF_DemoTab_SID]  DEFAULT (suser_sid()) FOR [SID]
GO

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

Exec sp_addarticle 
  @publication = 'publication'
, @article = N'DemoTab'
, @source_object = N'DemoTab'
, @source_owner = N'dbo'
, @schema_option = 0x0000000008037CDF
, @vertical_partition = N'true'
GO 
Exec sp_refreshsubscriptions 'publication' 
GO 
Exec sp_startpublication_snapshot 'publication'
GO

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

INSERT INTO [DemoTab](Guid,SID,Title) select NEWID(),SUSER_SID(),'test'
UPDATE [DemoTab] SET Title = 'KK' 
DELETE FROM [DemoTab]

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


也可以用脚本禁止:

--	禁止 "复制架构更改"
EXEC sp_changepublication @publication = N'publication', @property = N'replicate_ddl', @value = 0

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

ALTER TABLE dbo.[DemoTab] ADD TEST INT NULL

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


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

--订阅的所有同步存储过程.生成脚本在订阅库执行
EXEC sp_scriptpublicationcustomprocs N'publication'

--发布中的冲突表.生成脚本在发布库执行
EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'

--订阅触发器.生成脚本在订阅库执行
EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'

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

--查看发布列的信息
select name,column_id from sys.columns where object_id=object_id('DemoTab')
select artid from sysarticles where name='DemoTab'
select * from sysarticlecolumns where artid=1044

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

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



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

EXEC sp_scriptpublicationcustomprocs N'publication'

--
-- 来自数据库 'publisherdb'、发布 'publication' 的事务复制自定义过程:
--


----
---- 项目 'DemoTab' 的复制自定义过程:
----

if object_id(N'[sp_MSins_dboDemoTab]', 'P') > 0
drop proc [sp_MSins_dboDemoTab]
go
if object_id(N'dbo.MSreplication_objects') is not null
delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboDemoTab'
go
create procedure [sp_MSins_dboDemoTab] 
  @c1 uniqueidentifier,@c2 varbinary(85),@c3 nvarchar(100),@c4 uniqueidentifier,@c5 int
as 
begin 
if not exists (select * from [dbo].[DemoTab] 
 where  ( [SID] = @c2 and [Guid] = @c1 ) 
)
begin
insert into [dbo].[DemoTab]( 
 [Guid]
,[SID]
,[Title]
,[msrepl_tran_version]
,[TEST]
 )
values ( 
 @c1
,@c2
,@c3
,@c4
,@c5
 ) 
end
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null 
exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type)
 values (+ N''sp_MSins_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')
go

if object_id(N'[sp_MSupd_dboDemoTab]', 'P') > 0
drop proc [sp_MSupd_dboDemoTab]
go
if object_id(N'dbo.MSreplication_objects') is not null 
delete from dbo.MSreplication_objects where object_name = N'sp_MSupd_dboDemoTab'
go
create procedure [sp_MSupd_dboDemoTab] 
 @c1 uniqueidentifier,@c2 varbinary(85),@c3 nvarchar(100),@c4 uniqueidentifier,@c5 int,@pkc1 uniqueidentifier,@pkc2 varbinary(85)
,@old_msrepl_tran_version uniqueidentifier 
,@bitmap binary(1)
as
begin
if ( substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,1) & 2 = 2 )
begin
update [dbo].[DemoTab] set 
 [Guid] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [Guid] end
,[SID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [SID] end
,[Title] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Title] end
,[msrepl_tran_version] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [msrepl_tran_version] end
,[TEST] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [TEST] end
where [Guid] = @pkc1 and [SID] = @pkc2
 and msrepl_tran_version = @old_msrepl_tran_version 
end
else
begin
update [dbo].[DemoTab] set 
 [Title] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Title] end
,[msrepl_tran_version] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [msrepl_tran_version] end
,[TEST] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [TEST] end
where [Guid] = @pkc1 and [SID] = @pkc2
 and msrepl_tran_version = @old_msrepl_tran_version 
end
end
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null 
exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) 
values (+ N''sp_MSupd_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')
go

if object_id(N'[sp_MSdel_dboDemoTab]', 'P') > 0
drop proc [sp_MSdel_dboDemoTab]
go
if object_id(N'dbo.MSreplication_objects') is not null
delete from dbo.MSreplication_objects where object_name = N'sp_MSdel_dboDemoTab'
go
create procedure [sp_MSdel_dboDemoTab] 
  @pkc1 uniqueidentifier,@pkc2 varbinary(85)
,@msrepl_tran_version uniqueidentifier 
as 
begin 
delete [dbo].[DemoTab]
where [Guid] = @pkc1 and [SID] = @pkc2
 and msrepl_tran_version = @msrepl_tran_version 
end 
go
if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null 
exec ('insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) 
values (+ N''sp_MSdel_dboDemoTab'' , N''SZ1CARD1-DB'' , N''PlatformSync'' , N''publication'' , N''DemoTab'' ,''P'')')
go

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

EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'

if @@microsoftversion<0x07320000 
raiserror('当发布服务器为 SQL Server 2000 或更高版本时,必须将订阅服务器升级到 SQL Server 2000 才能创建可更新订阅。',16, -1)


if (@@microsoftversion >= 0x09000000) 
begin 
	exec sp_addqueued_artinfo 1044
	, N'DemoTab'
	, N'publisher'
	, N'publicationDB'
	, N'publication'
	, N'DemoTab'
	, N'dbo'
	, N'conflict_publication_DemoTab' 
end 

if (@@microsoftversion < 0x09000000) 
begin 
	exec sp_addqueued_artinfo 1044
	, N'DemoTab'
	, N'publisher'
	, N'publicationDB'
	, N'publication'
	, N'DemoTab'
	, N'dbo'
	, N'conflict_publication_DemoTab'
	, 0x000000000000000000000000000000000000000000000000000000000000008f 
end 

if (@@microsoftversion >= 0x09000000) 
begin 
	delete from MSsubscription_articlecolumns 
	where artid = 1044 
	and agent_id = (
		select id from MSsubscription_agents 
		where update_mode > 0 
		and UPPER(publisher) = UPPER(N'publisher') 
		and publisher_db = N'publicationDB' 
		and publication = N'publication' 
	) 
end 

if (@@microsoftversion >= 0x09000000) 
begin 
	declare @agent_id_1044 int 
	select @agent_id_1044 = id 
	from MSsubscription_agents 
	where update_mode > 0 
	and UPPER(publisher) = UPPER(N'publisher') 
	and publisher_db = N'publicationDB' 
	and publication = N'publication' 
	
	if @agent_id_1044 is not null 
	begin 
		insert MSsubscription_articlecolumns (agent_id, artid, colid) 
		values (@agent_id_1044, 1044, 1) 
		insert MSsubscription_articlecolumns (agent_id, artid, colid) 
		values (@agent_id_1044, 1044, 2) 
		insert MSsubscription_articlecolumns (agent_id, artid, colid) 
		values (@agent_id_1044, 1044, 3) 
		insert MSsubscription_articlecolumns (agent_id, artid, colid) 
		values (@agent_id_1044, 1044, 4) 
		insert MSsubscription_articlecolumns (agent_id, artid, colid) 
		values (@agent_id_1044, 1044, 8)  
	end 
end 

if (@@microsoftversion >= 0x080002C0)
begin 
	exec sp_addsynctriggers 
	 N'DemoTab'
	, N'dbo'
	, N'publisher'
	, N'publicationDB'
	, N'publication'
	, N'sp_MSsync_ins_DemoTab_4'
	, N'sp_MSsync_upd_DemoTab_4'
	, N'sp_MSsync_del_DemoTab_4'
	, N'sp_MScft_publication_DemoTab'
	, N'dbo'
	, N'null'
	, N'null'
	, N'null'
	, 0x03
	, 0
	,1
	,N'publisher'
	, 2 
end 

if (@@microsoftversion < 0x080002C0) 
begin 
	exec sp_addsynctriggers 
	 N'DemoTab'
	, N'dbo'
	, N'publisher'
	, N'publicationDB'
	, N'publication'
	, N'sp_MSsync_ins_DemoTab_4'
	, N'sp_MSsync_upd_DemoTab_4'
	, N'sp_MSsync_del_DemoTab_4'
	, N'sp_MScft_publication_DemoTab'
	, N'dbo'
	, N'null'
	, N'null'
	, N'null'
	, 0x03
	, 0
	,1
	,N'publisher' 
end 


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


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

SELECT * FROM sysarticleupdates WHERE artid=(select artid from sysarticles where name='DemoTab')

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

select 
case 
	when object_id=427877337 then 'sync_ins_proc'
	when object_id=443877394 then 'sync_upd_proc'
	when object_id=459877451 then 'sync_del_proc'
	when object_id=475877508 then 'sync_upd_tri'
	when object_id=491877565 then 'conflict_tableid'
	when object_id=539877736 then 'ins_conflict_proc'
	else '' end as sysarticleupdates
,object_id,name
from sys.objects 
where object_id in(427877337,443877394,459877451,475877508,491877565,539877736)


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

1. 更改冲突表 conflict_publication_DemoTab

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

EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'
if object_id(N'[dbo].[conflict_publication_DemoTab]') is not null 
begin DROP TABLE [dbo].[conflict_publication_DemoTab] end   
CREATE TABLE [dbo].[conflict_publication_DemoTab](
    [Guid] uniqueidentifier NOT NULL
    ,[SID] varbinary(85) NOT NULL
    ,[Title] nvarchar(100) NOT NULL
    ,[msrepl_tran_version] uniqueidentifier NOT NULL
    ,[TEST] int NULL
	,origin_datasource nvarchar(255) NULL
	,conflict_type int NULL
	,reason_code int NULL
	,reason_text nvarchar(720) NULL
	,pubid int NULL
	,tranid nvarchar(40) NULL
	,insertdate datetime NOT NULL
	,qcfttabrowid uniqueidentifier DEFAULT NEWID() NOT NULL
)       
CREATE UNIQUE INDEX [cftind_publication_DemoTab] 
ON [dbo].[conflict_publication_DemoTab]([SID], [Guid], tranid, qcfttabrowid)

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

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

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处相同代码:

    insert into [dbo].[DemoTab](  [Guid] , [SID] , [Title] , [msrepl_tran_version] ,TEST )   
    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函数 )


发布对象更改完成!~



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

INSERT INTO [DemoTab](Guid,SID,Title) select NEWID(),SUSER_SID(),'test'
UPDATE [DemoTab] SET Title = 'KK' 
DELETE FROM [DemoTab]

INSERT INTO [DemoTab](Guid,SID,Title,test) select NEWID(),SUSER_SID(),'test',0
UPDATE [DemoTab] SET test = 10
DELETE FROM [DemoTab]

ALTER TABLE dbo.[DemoTab] DROP COLUMN TEST 

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



总结:

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

--发布库执行,将新增的字段添加到发布.
select name,column_id from sys.columns where object_id=object_id('DemoTab')
select artid from sysarticles where name='DemoTab'
select * from sysarticlecolumns where artid=1044

--发布库执行,生成的脚本在订阅执行.
EXEC sp_scriptpublicationcustomprocs N'publication'
EXEC sp_scriptsubconflicttable @publication = 'publication', @article = 'DemoTab'
EXEC sp_script_synctran_commands @publication = 'publication', @article = 'DemoTab'

--发布库执行,修改发布相关对象
SELECT * FROM sysarticleupdates WHERE artid=


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

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



本文出自“Hello.KK (SQL Server)”的博客,转载请务必保留此出处http://blog.csdn.net/kk185800961

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【LeetCode138】复制带随机指针的链表(dfs+哈希unordered_map)
0 <= n <= 1000 -10000 <= Node.val <= 10000 Node.random 为空(null)或指向链表中的节点。
9 0
8.11 Linux修改系统用户信息(usermod命令)
前面章节介绍了如何利用 useradd 命令添加用户,但如果不小心添错用户信息,后期如何修改呢?
14 0
Linux - 修改系统的max open files、max user processes(附ulimit的使用方法)
Linux - 修改系统的max open files、max user processes(附ulimit的使用方法)目录 1 问题说明2 修改max open files3 修改max user processes4 附录: ulimit命令说明1 问题说明Linux 系统默认的max ope...
3569 0
mysql配置完半同步复制之后报错[ERROR] The server quit without updating PID file
修改配置,MySQL启动报:[ERROR] The server quit without updating PID file     [root@localhost mysql]# /etc/init.
1602 0
如何使用BOL接口set_property修改SAP CRM BO实例的某个属性
如何使用BOL接口set_property修改SAP CRM BO实例的某个属性
21 0
用SQL语句完成SQL Server数据库的修复
  使用数据库的过程中,由于断电或其他原因,有可能导致数据库出现一些小错误,比如检索某些表特别慢,查询不到符合条件的数据等。   出现这些情况的原因,往往是因为数据库有些损坏,或索引不完整。   在Access中,有个修复数据库的功能可以解决这个问题,在SQL企业管理器,没有这个功能,要用语...
799 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载