Forum Discussion

shivkumarm's avatar
shivkumarm
Copper Contributor
Jan 04, 2024

Transactional Replication - Insert after Delete not working

We wanted to have a DR setup for our SQL Server databases.

So for testing purpose we went ahead with Transactional Replication.

Setup

  • Server 1 (SQL Server 2019): Acting as Distributor and Publisher
  • Server 2 (SQL Server 2019): Acting as Subscriber
  • IP's are used for configuration, not Server Names (explicitly mentioning this point, since I have went through few blogs were the problems and issues are highlighted due to IP)

The system is working as expected, but following behaviours are observed:

Problem

  • Deleted all records in Server 1
  • Due to transactional replication all of the records are deleted in Server 2
  • Post delete inserted records in Server 1
  • Expected behaviour was Server 2 will have new records, but it did not
  • Re-initialize the subscription via Replication Monitor in SSMS
  • Then this worked and records are replicated

I wanted to take this same setup to Production, but this behaviour of Insert after Delete is stopping me to proceed ahead.

What the root cause of this issue?

Is there a way to overcome it or this is the natural behaviour?

How to avoid such situations?

 

Replication Monitor shows Error:

The row was not found at the Subscriber when applying the replicated DELETE command for Table '[dbo].[DP000043]' with Primary Key(s): [MGrCode] = 101, [EffDate] = 2017-09-11 00:00:00.000, [SrNo] = 1

  • shivkumarm's avatar
    shivkumarm
    Copper Contributor
    Jan 05, 2024

    Answer as per my investigation:

    Normally this error occurs when an UPDATE or DELETE statement is executed by the publisher for a primary key value and the record (against which UPDATE/DELETE executed) does not exist in the subscriber database.

     

    There are multiple reasons that could cause this:

    1. It could be someone or a process/apps deleted and inserted the data in subscriber db (while cmds are being applied). Review your subscriber security settings and check what process/apps are accessing the subscriber db.
    2. Multiple publications connected to subscriber db. pub1 (deleted the data first) then pub2 tried to UPDATE/DELETE the data which could cause an error 20598.
    3. Triggers on subscriber tables that could delete/insert/update the data.

Resources