One reason that SQL Server can update subscription data conflicts

Source: Internet
Author: User
Tags unique id

Why are updatable subscriptions conflicting?


Updatable subscriptions, when the upgrade adds a field, typically increases in the publisher's publication database, and after the table is added, the publication is automatically synchronized to the subscription database (replication schema change =true). However, if DML operations are performed at the subscription database at this time, the data will not be synchronized to the publishing table, and the difference in the subscription table will not be synchronized to the publication again if the DML operation has not been performed.


Replication Configuration Environment:

Updatable Subscription transactional replication

Publication and subscription conflicts are subject to subscription

Using queued updating

In the subscription operation


Conflict test Results (the following: When the data is inconsistent, the re-operation of the subscription causes a conflict, and the conflict policy is resolved automatically):


Update subscription data at this time when the publication data does not exist and the subscription data is present: (Queued update conflict)

when the publication and subscription primary keys are the same, msrepl_tran_version when different, the subscription data is updated at this time: (Queued update conflict)

The results of the above two cases:

In the publish conflict, the conflicting table record values are up to date, the subscription data is updated or inserted into the publishing table.



When the publication data does not exist and the subscription data exists, the subscription data is deleted at this time: (Queued update conflict)

when the publication and subscription primary keys are the same, msrepl_tran_version when different, the subscription data is deleted at this time: (Queued update conflict)

The results of the above two cases:

The delete operation was synchronized to the publication when the conflict occurred.

Conflict Winner:

This line no longer exists in "[dbo]. [Testtab] "in. [[dbo]. [Testtab]]. The value of the unique ID in [Qcfttabrowid] is "8d335a44-36a0-432c-bba4-4979df3c804e".

Conflict loser:

The above error occurred when attempting to delete this data at this location, possibly because this delete operation violates one or more constraints. If you ignore this conflict, it should be resolved in other ways. You can log the details of this conflict and then send the log entries to the system administrator.




How does a schema prevent conflicts?

To make schema changes on a table in a publication that supports updating subscriptions, you must stop all activities on the table at the Publisher and Subscribers, and you must propagate the pending data changes to all nodes before you can make schema changes. This ensures that incomplete transactions do not conflict with pending schema changes. After the schema changes propagate to all nodes, you can resume the activity on the published table. How to Stop a replication topology (Replication Transact-SQL Programming)



Reference: Updatable subscriptions for transactional replication


One reason that SQL Server can update subscription data conflicts

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.