SQL Server updatable subscription data online schema change (add field) scenario

The reason for the inconsistency between conflicting and subscription data has been found before, and the number of database upgrades that have been discovered is the result of the addition of fields in the publication database and the automatic synchronization of subscriptions. At this point, if the subscription queue has data, the data will be lost. Refer to the previous note: One reason that SQL Server can update subscription data conflicts. When you add a field to the publication database, the system synchronization stored procedures and triggers are regenerated, which causes the data that is still in the queue to not synchronize properly. The commands in the subscription queue will disappear after synchronization, and the agent may go wrong, but it will automatically return to normal! ~

This week tested some of the methods that ultimately determine a solution that is feasible, albeit cumbersome and time-consuming.

First look at the simple architecture diagram:

, above is the publisher, and the following is the Subscriber. Take a table as an example, the normal release of the table to T1 updatable subscription publication, data bidirectional synchronization, the user is in the subscription database operation, is the Operation View . When adding a field, in order for the user to be able to work on the subscription, create a publish subscription, publish only one table, and the data too much initialization will be longer. Note: The new publication cannot be the same database! If it is the same database, in order to avoid the same as the original table name, change the table name of the new subscription, will cause the publication to the original subscription data is not synchronized! So the new subscription is on another database, keeping the table name consistent! Because the system calls the stored procedure table structure consistent. ~ For example, in a publication, two database subscriptions are required at the Subscriber, and one of the publications has only one table, which will be the table for the new field. At this point 3 tables are synchronously published in two-way synchronization.

Because the user is the operation view, the definition of the view is changed to the new database table, the user operation is still normal, and the data synchronization is normal. For newly added publications, disable schema synchronization, and when the publication database adds fields, it will not be synced to the new subscription table, but will be synced to the original subscription table. After the field synchronization is complete, the view definition changes back to the original table, where both the publication and the subscription have new fields. and the publication subscription created as a mediator can be deleted! ~

The main steps are as follows:

1. Ensure consistent publication and subscription data

2. Create 1 updatable subscriptions, publish only 1 sheets (Publish database execution)

3. Newly released conflict resolution policy: Keep Subscriber changes (publish property changes)

4. Newly released "Replication schema Change": False (publication property change)

5. Create a subscription (Subscriber new database)

6. Change the owner of the job; initialize the subscription; wait for completion .....

7. Publish Change View definition (synced to the original subscription), use a subscription table from another database (publication database execution, attention to subscription table permissions issues)

8. Confirm that the queue for the original subscription has been completed synchronously

9. Add fields to the table in the publication database (automatically synced to the original subscription, new subscriptions are not synchronized)

10. If the subscription queue does not accumulate, change the definition of the publication view, using the original database table (publication database execution)

11. Finally delete the newly created publication

--1. Ensure that the publication and subscription data are consistent--2. Create updatable Subscriptions (filter) publications, publish only 1 tables (Publish database execution)--3. Newly released conflict resolution policy: Keep Subscriber changes (publish properties) EXEC sp_changepublication @ Publication = n ' temp02 ', @property = N ' conflict_policy ', @value = N ' Sub wins ' EXEC sp_changepublication @publication = N ' te Mp03 ', @property = N ' conflict_policy ', @value = N ' Sub wins '--4. Newly released copy schema change: false (Publication property) EXEC sp_changepublication @ Publication = n ' temp02 ', @property = N ' replicate_ddl ', @value = 0EXEC sp_changepublication @publication = n ' temp03 ', @prop Erty = N ' Replicate_ddl ', @value = 0--5. Create a subscription (subscriber new database)--6. Change the job owner, initialize 4 subscriptions, wait for completion ...--7. Publish change View definition, use a subscription table from another database ( Publish database execution, note subscription table permissions issues) ALTER VIEW [dbo]. [Vtesttab]  As  SELECT identifier,name,value,info from  DemoDB.dbo.TestTab-- Table Go--8 of another subscription database. Determine that the queue for the original subscription has been synchronized--9. Add fields to the table in the publication database (automatically synced to the original subscription) ALTER table dbo. Testtab ADD TEST int--10. There are not too many subscription queues, change the publication view definition, use the original database table (publish database execution) ALTER view [dbo]. [Vtesttab]  As  SELECT identifier,name,value,info from  dbo. Testtab go--11. Deleting a newly created publication

Test done! ~ Personal tests are normal and not yet applied to the production library. The most troublesome of these is to initialize the table data, which is mostly time-consuming.

SQL Server updatable subscription data online schema change (add field) scenario