PK violation in replication but only one subscriber

  • We have a publication with three subscribers; it's basically a part list with around 1.2 million rows.  The local table is on a SQL Server 2019 instance, and is updated nightly via a MERGE statement from a larger part list.  This table and several smaller ones are replicated via Transactional Replication to three different Azure SQL instances - one for a public website, one for a production application, and one for a DEV version of the same application.

    The issue is that on the test subscriber, we have gotten primary key violations about four different times in the past couple of weeks.  It's been on seemingly random parts.  The odd thing is that this has *only* happened on the test instance.  The website and production subscribers keep humming along as normal.  The only workaround we've been able to find is to reinitialize the subscriber - which works, but is not ideal.

    Not expecting anyone here to have a fix just pop into their head (though if that is the case, I'd be grateful to hear it!) but was wondering if anyone could suggest how we might go about tracking this down.  Any logs we can look at for example?  It just seems really strange that we have three identical subscribers and only one is throwing PK errors.

    The three Azure databases are identical - the two app databases were restored from a copy of the website database.  And in all three cases, only the account that does the replication can INSERT or UPDATE the tables in question.

  • Create a DML audit on that and double check the data cant be inserted from outside. Next time when the issue pops up , check the DML audit log to rule out the insert\update was not from external.

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply