Transactional Replication - Insert after Delete not working

[shivkumarm]

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

[#2]

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.