0

We recently experienced errors with SQL Server Transactional Replication on a client's system. Replication is running from a SQL 2017 server to an Azure DB.

Initially some replicated update commands failed as the rows to be updated didn't exist on the subscriber database. We added the rows in manually to save reinitialising.

Following that some replicated insert commands failed as the rows to be inserted were already present on the subscriber. We deleted the rows and replication carried on as normal.

The client insists that they have changed no data on the subscriber database, and is asking how this happened.

Our best guess is that an error in the replication process caused commands to be sent more than once to the subscriber, but we are unsure why or how that would come about.

Has anyone experienced anything similar, or does anyone know how this may happen?

Improve this question
8
  • 1
    How would "an error in the replication process caused commands to be sent more than once" explain missing rows? Clearly an application or a user connecting to the subscriber, possibly by mistake, and changing data there would be a more realistic explanation.
    – mustaccio
    Oct 4, 2022 at 20:57
  • What isolation level is the Publisher using? (And thus the isolation level used by the snapshot agent...) How busy is the Publisher database with regard to write activity?
    – AMtwo
    Oct 5, 2022 at 0:21
  • Question: "does anyone know how this may happen?" Answer: "We added the rows in manually to save reinitialising. Following that some replicated insert commands failed as the rows to be inserted were already present on the subscriber." - I've run into this very issue. Your best bet is to drop the table article from replication, drop the table at the Subscriber, re-add the table article to the Publication, re-initialize the Publication with a new snapshot.
    – J.D.
    Oct 5, 2022 at 0:30
  • @mustaccio Yes, very true! I was more thinking about the rows that were already present on the subscriber causing the inserts to fail. I agree that the update failures are far more likely to be due to a mistaken delete on the subscriber. The client insists this didn't happen, so taking them at face vaue I am exploring other possible causes.
    – Lectric
    Oct 5, 2022 at 9:28
  • @AMtwo The publisher Isolation Level is Read committed, and it is a high traffic system that is very busy with write activity. Something else that I should probably have mentioned is that replication is going from an on premise SQL 2017 server to an Azure DB.
    – Lectric
    Oct 5, 2022 at 9:38

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.