CODEWITHSUNDEEP
sqlsql-serverdatabase-replication
Dimitri
Dimitri

Reputation: 7013

Database replication stops if data is changed on the subscriber

Should the SQL database replication stop if i delete one record in a replicated table on the subscriber end?

I remember having a replication running where the delete on subscriber would be overwritten from publisher effectively preventing you from deleting the data.

But in our new configuration it crashed the replication when we deleted one record.

Upvotes: 0

Views: 968

Answers (1)

Ben Thul
Ben Thul

Reputation: 32697

It depends. If you deleted a row at the subscriber that is subsequently updated at the publisher, replication will break when the update is propagated. Why is this? If you look at how the command is replicated, it calls a stored procedure with the PK column(s), a bit mask of what columns changed, and then the list of new values for columns that changed (I'm glossing over some detail, but you can look for yourself in the subscriber database; the procedures are all there and pretty accessible). Because it doesn't re-replicate the whole row again, if it doesn't find the row indicated by the PK, replication assumes (correctly) that the subscriber is no longer in sync with the publisher and stops. As far as I know, replication has never worked in the way you describe.

TL;DR: you should treat the subscriber database(s) as read-only except by the replication process itself.

Upvotes: 1

Related Questions