SqlServer 禁止架構更改的複製中手動修複使發布和訂閱中分別增加的欄位同步，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]GOALTER TABLE [dbo].[DemoTab] ADD  CONSTRAINT [DF_DemoTab_Guid]  DEFAULT (newsequentialid()) FOR [Guid]GOALTER 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') > 0drop proc [sp_MSins_dboDemoTab]goif object_id(N'dbo.MSreplication_objects') is not nulldelete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboDemoTab'gocreate procedure [sp_MSins_dboDemoTab]   @c1 uniqueidentifier,@c2 varbinary(85),@c3 nvarchar(100),@c4 uniqueidentifier,@c5 intas begin if not exists (select * from [dbo].[DemoTab]  where  ( [SID] = @c2 and [Guid] = @c1 ) )begininsert into [dbo].[DemoTab](  [Guid],[SID],[Title],[msrepl_tran_version],[TEST] )values (  @c1,@c2,@c3,@c4,@c5 ) endendgoif 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'')')goif object_id(N'[sp_MSupd_dboDemoTab]', 'P') > 0drop proc [sp_MSupd_dboDemoTab]goif object_id(N'dbo.MSreplication_objects') is not null delete from dbo.MSreplication_objects where object_name = N'sp_MSupd_dboDemoTab'gocreate 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)asbeginif ( substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,1) & 2 = 2 )beginupdate [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] endwhere [Guid] = @pkc1 and [SID] = @pkc2 and msrepl_tran_version = @old_msrepl_tran_version endelsebeginupdate [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] endwhere [Guid] = @pkc1 and [SID] = @pkc2 and msrepl_tran_version = @old_msrepl_tran_version endendgoif 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'')')goif object_id(N'[sp_MSdel_dboDemoTab]', 'P') > 0drop proc [sp_MSdel_dboDemoTab]goif object_id(N'dbo.MSreplication_objects') is not nulldelete from dbo.MSreplication_objects where object_name = N'sp_MSdel_dboDemoTab'gocreate 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 goif 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,namefrom 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',0UPDATE [DemoTab] SET test = 10DELETE 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=

為什麼要這樣做？有時候有的表沒有同步、有時某些對象被刪除、或者原本不行同步，現在又同步了（如開始說明）。

這裡還沒完全測試，比如在發布和訂閱的欄位都有資料時，會不會也是同樣處理？