SQL Server updatable subscription upgrade field queue data loss reason

Original: SQL Server updatable subscription upgrade field queue data loss reason

A simple description of why data conflicts occur: One reasonSQL Server can update subscription data conflicts , but the specifics of how the internal principles are lost are unclear, add to today's notes. Updatable subscriptions, operating data in the subscription database, and data synchronization to the publication database in real time.

Tested, there are 3 scenarios that can cause data loss in the subscription queue:

1. Change the field type

2. Add Delete field

3. Table Object Publishing

Another: Add default value constraint, stored procedure, function OK! Subscription queue data is not lost! (Triggers are not set for synchronization)

The specific simulation steps are as follows:

1. Adding a table to a publication

2. Stop the Queue Reader Agent

3. DML operations on other synchronized tables on the subscription database at this time

4. Start the Snapshot Agent (new table synchronization)

5. Start the Queue Reader Agent

6. Check that the queue table in the subscription library has no data, but the publication database table has not changed.

That is, after the snapshot is enabled, the queue data disappears! ~

Open Profiler trace to, when creating a publication for a new table, the transactions and commands in the subscription queue will be emptied!

The stored procedure executed is: sp_MSreset_queued_ Reinit < Span style= "font-size:10pt" > < Span lang= "en-US" > sp_repldeletequeuedtran

execSp_msreset_queued_reinitN ' ServerName ',N ' subscriber_db ',@artid

execSp_repldeletequeuedtranN 'serverName',N ' publisher_db ',N ' publication ',N ' Tranid ',@orderkeylow,@orderkeyhigh

The subscription queue table data is deleted:dbo M Sreplication_queue and dbo. msrepl_queuedtraninfo

So when the queue reader is enabled, the queue already has no data! (After the queue reader is enabled, subsequent synchronizations are normal)

Data for subscription changes cannot be synced to the publication, which results in inconsistencies between subscription data and published data!

Although this example is a stop-queue reader simulation, in a production environment, the queue can say that there is always data, and each table has operations. If you want to upgrade the database structure, for this kind of architecture, you have to stop maintenance to upgrade! Or at night when the user is less, prohibit user access to upgrade the database structure.

SQL Server updatable subscription upgrade field queue data loss reason