SQL Server prevents schema changes from being manually fixed in replication to increase field synchronization in publications and subscriptions separately

As a result of previous needs, the replication schema change was forbidden, and a field was added to the publication and not synced to the subscription, and now a field with the same name was added to the subscription, so how do you make the two fields of the publication and subscription a synchronous relationship?

The following is a test of the change: the publication type is a updatable subscription for transactional replication , and other types of publications are not tested.

The updatable subscription to the transactional replication is established first.

Create a test sheet in the publication:

CREATE TABLE [dbo]. [Demotab] ([Guid] [uniqueidentifier] not NULL,[SID] [varbinary] (+) not null,[title] [nvarchar] (+) 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 to the publication 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, confirming that synchronization is normal:

INSERT into [Demotab] (guid,sid,title) Select NEWID (), Suser_sid (), ' Test ' UPDATE [demotab] SET Title = ' KK ' DELETE from [Demo TAB]

"Copy schema Changes" is now disabled

You can also use scripting to disable:

--Prohibit "copy schema change" EXEC sp_changepublication @publication = n ' publication ', @property = N ' replicate_ddl ', @value = 0

Additional fields are now executed in the publication database and subscription database, because the schema is not synchronized and therefore does not conflict.

ALTER TABLE dbo. [Demotab] ADD TEST INT NULL

when added, replication is still normal as long as the new column test operation is not done. But to prevent customers from working on the table, because the objects involved in the change are changed, the error is not met!

The first 3 stored procedures that refer to the primary reference are performed in the publication database. ( Note: Executable view, but generated scripts do not execute )

--All the synchronization stored procedures for the subscription. The build script executes exec sp_scriptpublicationcustomprocs N ' publication '--in the subscription library. The build script executes exec sp_ in the publishing library scriptsubconflicttable @publication = ' publication ', @article = ' demotab '--subscription trigger. The build script executes exec sp_script_synctran_ in the subscription library Commands @publication = ' publication ', @article = ' Demotab '

Add new columns to the publishing project (executed at the publication database)

--View the information for the publishing column 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 can be known from above, the field [test] of the id=8, the table [Demotab] of the publication of the project number ArtID = 1044, the current field [test] is not added to the publishing table, the following to add the column to the publication

Insert INTO Sysarticlecolumns Select 1044,8,0,0,0

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

Now execute the 3 stored procedures described above in the publication database. The stored procedure generates a synchronized stored procedure for the subscription, and the generated script executes at the subscription database (the snapshot is applied manually)

EXEC sp_scriptpublicationcustomprocs N ' publication '

----Transactional replication customization process from database ' PublisherDB ', publish ' publication ':----------project ' demotab ' Replication customization Process:----If object_id (N ' [sp_msins_ Dbodemotab] ', ' P ') > 0drop proc [sp_msins_dbodemotab]goif object_id (N ' dbo. MSreplication_objects ') is not the nulldelete from dbo. msreplication_objects where object_name = N ' sp_msins_dbodemotab ' gocreate procedure [Sp_msins_dbodemotab] @c1 uniqueidentifier, @c2 varbinary, @c3 nvarchar, @c4 uniqueidentifier, @c5 Intas begin if not EXISTS (SELECT * FROM [DB O]. [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 a null delete from dbo. msreplication_objects where object_name = N ' sp_msupd_dbodemotab ' gocreate procedure [Sp_msupd_dbodemotab] @c1 uniqueidentifier, @c2 varbinary, @c3 nvarchar, @c4 uniqueidentifier, @c5 int, @pkc1 uniqueidentifier, @pkc2 varbinary, @old_msrepl_tran_version uniqueidentifier, @bitmap binary (1) asbeginif (substring (@bitmap,) & 1 = 1 or substring (@bitmap, () & 2 = 2) beginupdate [dbo]. [Demotab] set [GUID] = case substring (@bitmap, 1) & 1 to @c1 else [GUID] end,[sid] = case substring (@bitmap , & 2 When 2 then @c2 else [SID] end,[title] = case substring (@bitmap,) & 4 at 4 then @c3 else [Title] en  D,[msrepl_tran_version] = case substring (@bitmap,) & 8 While 8 then @c4 else [msrepl_tran_version] end,[test] = case SUBSTRING (@bitmap, &) @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, 4) & 4 and then @c3 else [title] end,[msrepl_tran_version] = case SUBSTRING (@bitmap, 8) & 8 then @c4 else [msrepl_tran_version] end,[test] = case substring (@bitmap,) & When @c5 else [TEST] endwhere [Guid] = @pkc1 and [SID] = @pkc2 and msrepl_tran_version = @old_msrepl_tran_versi On 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 the nulldelete from dbo. msreplication_objects where object_name = N ' sp_msdel_dBodemotab ' gocreate procedure [Sp_msdel_dbodemotab] @pkc1 uniqueidentifier, @pkc2 varbinary ($), @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 (obj ect_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 tracking project information and synchronization triggers that are applied to the subscription. The generated script executes at the subscriber's subscription database. This stored procedure is executed at the publisher's publication database.

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

If @ @microsoftversion <0x07320000 raiserror (' When the publisher is SQL Server 2000 or later, you must upgrade the Subscriber to SQL Server 2000 to create an updatable subscription. ', +,-1) if (@ @microsoftversion >= 0x09000000) begin exec Sp_addqueued_artinfo 1044, n ' demotab ', n ' publisher ', N ' publi Cationdb ', 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_d b = 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, CO Lid) VALUES (@agent_id_1044, 1044, 1) Insert Mssubscription_articlecolumns (agent_id, ArtID, colid) VALUES (@agent_id_104 4, 1044, 2) Insert Mssubscription_articlecolumns (agent_id, ArtID, colid) VALUES (@agent_id_1044, 1044, 3) Insert Mssubscr Iption_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_ad Dsynctriggers 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 ' Publ Ication ', 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

Subscription related objects have been changed to complete! Then you have to change some of the objects in the release! ~

To view the objects involved in the publication database:

SELECT * from Sysarticleupdates where artid= (select ArtID from sysarticles where name= ' Demotab ')

corresponding to the D object, these objects need to be changed! ~

Select Case when object_id=427877337 and 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 objects, will change individually! (Sp_mssync_upd_trig_demotab_4 does not need to be changed)

1. Change the conflict table Conflict_publication_demotab

Recreate the conflict table. In the publication database execution, the generated script executes at the publication database.

EXEC sp_scriptsubconflicttable @publication = ' publication ', @article = ' Demotab '

If object_id (N ' [dbo].[ Conflict_publication_demotab] ') is a NOT NULL begin DROP TABLE [dbo]. [Conflict_publication_demotab] End   CREATE TABLE [dbo].[ Conflict_publication_demotab] (    [Guid] uniqueidentifier NOT null    , [SID] varbinary (*) NOT null    , [Title] nvarchar (+) 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 (+) 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 rebuilding the table, object_id is different, and the sysarticleupdates conflict table ID 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 the Update conflict table

EXEC sp_helptext Sp_mscft_publication_demotab

3. Change a stored procedure in a publication

Publish library These 3 stored procedures: Sp_mssync_del_demotab_4,sp_mssync_ins_demotab_4,sp_mssync_upd_demotab_4
These 3 stored procedures are called by the trigger that subscribes to the database table, so note the location of the arguments. The new field [test] parameter is after [msrepl_tran_version]

Trg_mssync_del_demotab Call stored procedure Sp_mssync_del_demotab_4
Trg_mssync_ins_demotab Call stored procedure Sp_mssync_ins_demotab_4
Trg_mssync_upd_demotab Call stored procedure Sp_mssync_upd_demotab_4

There are more than 3 storage changes, but the corresponding columns are changed. In order to add parameters, or field assignments, the field test references the other fields, and the parameters only change the number.

[Sp_mssync_del_demotab_4] Increment the passed parameter @c5_old INT, modify a location as follows.

[Sp_mssync_ins_demotab_4] Increases the passed parameter @c5 INT

Change the same code at 2:

    INSERT INTO [dbo]. [Demotab] (  [Guid], [SID], [Title], [msrepl_tran_version], TEST)       values (  @c1, @c2, @c3, @c4, @c5)  

Change the code at 1:

Sp_mssync_upd_demotab_4 Add 2 parameters @c6 int, @c6_old int, change several scripts

Parameter location reference 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, not @c5, but @c6, to avoid causing confusion when adding columns later! ~ The above should be changed! (just forget, will not go back to change)

Note here that the first 8 columns get the initial byte substring (@bitmap, 1, 1), each column corresponds to a bit, 5th column 00010000, 16. Notice to change here.

(More references in-depth understanding of columns_updated functions in SQL Server 2005)

Publish Object Changes complete! ~

Now to test, in the publishing library and the subscription library to perform the same time, the data normal synchronization! ~

Finally delete the column "Test", the subscription library also automatic synchronization is deleted! ~ Sync OK! ~ The test is over! ~

Summarize:

Manual changes are troublesome! ~ Especially in the publishing library, no related auto-generated stored procedures are found (think it should be), so the published objects changed one by one! ~ Just at the beginning, I even subscribed to the object is a change! ~ Later tests summed up this order several times.

--Publish library execution, adding new fields to the publication. 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--Publishing library executes, the generated script executes on the subscription. EXEC sp_scriptpublicationcustomprocs N ' publication ' exec sp_scriptsubconflicttable @publication = ' publication ', @ Article = ' Demotab ' EXEC sp_script_synctran_commands @publication = ' publication ', @article = ' demotab '--Publish library execution, Modify publishing related objects select * from Sysarticleupdates WHERE artid=

Why do you do this? Sometimes some tables are not synchronized, sometimes some objects are deleted, or they are not synchronized, and are now synchronized (such as starting instructions).

It is not fully tested here, for example, if there is data in both the published and subscribed fields, will that be the same process?

SQL Server prevents schema changes from being manually fixed in replication to increase field synchronization in publications and subscriptions separately