Manual repair of fields added in the release and subscription for replication of SQL Server against schema changes

Manual repair of fields added in the release and subscription for replication of SQL Server against schema changes

Due to previous requirements, the replication architecture changes are prohibited, and even adding a field to the release will not be synchronized to the subscription. Now, a field with the same name is added to the subscription, how can we establish a synchronization relationship between the publish and subscribe fields?

The following is a test change: the release type isUpdatable subscription for transaction Replication, Other types of release are not tested.

First, create a updatable subscription for transaction replication.

Create a test table in the release:

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

Add a table for publishing and initialize the table:

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

The sub-tables are tested in both publish and subscribe, and the synchronization is normal:

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

Disable "Copy schema change" now"

You can also use a script to disable it:

-- Disable "replication schema change" EXEC sp_changepublication @ publication = N 'publication ', @ property = n' replicate _ ddls', @ value = 0

Currently, fields are added to the Publishing Database and subscription database respectively. Because the architecture is not synchronized, there is no conflict.

ALTER TABLE dbo.[DemoTab] ADD TEST INT NULL

After adding the columns, the replication will still work normally if the test operation is not performed. However, you are prohibited from performing operations on the table because The objects involved in the change must be changed. An error occurred in this case!

First, we will introduce the three stored procedures for reference, all of which are executed in the release database. (Note: You can execute and view them, but do not execute the generated scripts)

-- All synchronization stored procedures subscribed. execute EXEC sp_scriptpublicationcustomprocs N 'publication 'in the subscription database to generate a conflict table in the publication. execute EXEC sp_scriptsubconflicttable @ publication = 'publication ', @ article = 'demotab' in the production database -- subscription trigger. execute EXEC sp_script_synctran_commands @ publication = 'publication ', @ article = 'demotab' in the subscription library'

Add new columns to the release project (executed in the release database)

-- View the 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

As you can see above, the id of the field [TEST] is 8, and the release project number artid of the table [DemoTab] Is 1044. The current field [TEST] is not added to the release table, add this column to release

insert into sysarticlecolumns select 1044,8,0,0,0

After the preceding statement is executed, you can see that the column has been added in the interface.

Now execute the three stored procedures mentioned above and execute them in the published database. This stored procedure generates a subscription synchronization stored procedure, and the generated script is executed in the subscription database (manual Snapshot Application)

EXEC sp_scriptpublicationcustomprocs N'publication'

---- Custom process of transaction replication from database 'herherdb' and publishing 'publication': ---------- custom process of copying project 'demotab': ---- if object_id (n' [sp_MSins_dboDemoTab] ', 'P')> 0 drop 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 [Folder] @ 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) endendendgoif 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 ''platformsyn'', n''public'', n''demotab'', ''p') ') goif object_id (n' [sp_MSupd_dboDemoTab]', 'P')> 0 drop 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 [Folder] @ 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 endgoif 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 ''platformsyn'', n''public'', n''demotab'', ''p') ') goif object_id (n' [sp_MSdel_dboDemoTab]', 'P')> 0 drop proc [sp_MSdel_dboDemoTab] goif object_id (N 'dbo. MSreplication_objects ') is not nulldelete from dbo. MSreplication_objects where object_name = n' sp _ category' gocreate procedure [empty] @ 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

This stored procedure generates the tracking project information and synchronization trigger applied to the subscription. The generated script is executed on the subscription database of the subscription server. This stored procedure is executed in the Publishing Database of the Publishing Server.

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

If @ microsoftversion <0x07320000 raiserror ('when the Publishing Server is SQL Server 2000 or later, you must upgrade the subscription Server to SQL Server 2000 to create updatable subscriptions. ', 16,-1) if (@ microsoftversion> = 0x09000000) begin exec sp_addqueued_artinfo 1044, N 'demotab', N 'her her', 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'her her ', n'publicationdb', n'publication', n'demotab', n'dbo', n'conflict _ publication_DemoTab ', else 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'her her ') 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'her her ') and publisher_db = n' publicationdb' and publication = n' publication 'if @ agent_id_1044 is not null begin insert into (agent_id, artid, colid) values (@ agent_id_1044, 1044, 1) insert into (agent_id, artid, colid) values (@ agent_id_1044, 1044, 2) insert MSsubscription_articlecolumns (agent_id, artid, colid) values (@ agent_id_1044, 1044, 3) insert into (agent_id, artid, colid) values (@ agent_id_1044, 1044, 4) insert MSsubscription_articlecolumns (agent_id, artid, colid) values (@ agent_id_1044, 1044, 8) end if (@ microsoftversion> = 0x080002C0) begin exec sp_addsynctriggers N 'demotab', N 'dbo', N 'her her ', N 'publicationdb', N 'publication ', N 'SP _ MSsync_ins_DemoTab_4 ', N 'SP _ bucket', N 'SP _ MSsync_del_DemoTab_4', N 'SP _ MScft_publication_DemoTab ', N 'dbo', N 'null ', N 'null', N 'null', 0x03, N 'her her', 2 end if (@ microsoftversion <0x080002C0) begin exec sp_addsynctriggers N 'demotab ', N 'dbo', N 'her her ', 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', 0x03, 0, 1, 1, n' her 'end

The subscription object has been changed! Next we have to change some objects in the release !~

View the objects involved in the Publishing Database:

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

Corresponding to d object, all these objects need to be changed !~

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)

Six objects will be changed one by one! (Sp_MSsync_upd_trig_DemoTab_4 does not need to be changed)

1. Change the conflict table conflict_publication_DemoTab

Recreate the conflict table. Execute the statement in the published database and execute the generated script in the published database.

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)

After the table is rebuilt, The object_id is different. The conflict table id of sysarticleupdates needs to be changed.

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. Modify the stored procedure for updating conflicting tables

exec sp_helptext sp_MScft_publication_DemoTab

3. Change the stored procedure in the release

Three stored procedures: sp_MSsync_del_DemoTab_4, sp_MSsync_ins_DemoTab_4, sp_MSsync_upd_DemoTab_4
These three stored procedures are called by the trigger of the subscribed database table respectively. Therefore, pay attention to the parameter passing location. The new field [test] parameter is after [msrepl_tran_version ].

Trg_MSsync_del_DemoTab calls the Stored Procedure sp_MSsync_del_DemoTab_4.
Trg_MSsync_ins_DemoTab call the Stored Procedure sp_MSsync_ins_DemoTab_4
Trg_MSsync_upd_DemoTab calls the Stored Procedure sp_MSsync_upd_DemoTab_4.

There are many changes to the three storage, but they all change the corresponding columns. You need to add parameters, or assign values to fields. The field test can be referenced by other fields. The parameter only changes the number.

[Sp_MSsync_del_DemoTab_4] Add the passed parameter @ c5_old INT. Modify the following position.

[Sp_MSsync_ins_DemoTab_4] Add the passed parameter @ c5 INT

Change the same code at 2 points:

    insert into [dbo].[DemoTab](  [Guid] , [SID] , [Title] , [msrepl_tran_version] ,TEST )       values (  @c1 , @c2 , @c3 , @c4 ,@c5 )  

Change code 1:

Sp_MSsync_upd_DemoTab_4 added two parameters @ c6 int, @ c6_old int, and changed several scripts.

For parameter location information, see the location passed in the subscription trigger trg_MSsync_upd_DemoTab: @ c1, @ c2, @ c3, @ c4, @ c6, @ c1_old, @ c2_old, @ c3_old, @ c4_old, @ c6_old

Note: The above parameters should be the same as the name number in the trigger, instead of @ c5, but @ c6, to avoid confusion when you add columns later !~ The above should be changed! (If I forget it, I will not go back to change it)

Note: here, the first eight columns obtain the first byte substring (@ bitmap, 5th). Each column corresponds to a single position. The column 00010000 is, which is 16. Change it here.

(For more information, see the COLUMNS_UPDATED function in SQL Server 2005)

Release object changed !~

Test now, respectivelyRun the command again in the publishing and subscription databases. The data is synchronized normally !~

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 

The column "test" is finally deleted and automatically deleted in the subscription library !~ Synchronization normal !~ Test Complete !~

Summary:

Manual modification is troublesome !~ Especially in the release library, the related automatically generated stored procedures (I think there should be) are not found, so the released objects are changed one by one !~ At the beginning, I even changed the subscription objects one by one !~ Then I tested several times to summarize the order.

-- Execute the release library and add new fields to the release. 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 -- the repository is executed, and the generated script is executed in the subscription. EXEC sp_scriptpublicationcustomprocs N 'publication' EXEC sp_scriptsubconflicttable @ publication = 'publication', @ article = 'demotab 'EXEC sp_script_synctran_commands @ publication = 'publication ', @ article = 'demotab' -- execute the release database and modify the release object SELECT * FROM sysarticleupdates WHERE artid =

Why? Sometimes some tables are not synchronized, sometimes some objects are deleted, or some tables are not synchronized at the beginning, and now they are synchronized again (as described below ).

I haven't fully tested it yet. For example, Will data be processed in both published and subscribed fields?