Home Dashboard Directory Help

SQL Replication introduces audit data with invalid foreign keys by deleting rows by SQLUser722



Sign in
to vote
Type: Bug
ID: 773473
Opened: 12/4/2012 5:03:52 PM
Access Restriction: Public
User(s) can reproduce this bug



We are observing worrying behavior in which audit records are
being deleted from spoke servers in a transactional replication setup
with updatable subscriptions.

There are 3 audit tables. AuditEntries is the base table. The other
two audit tables, AuditEntryChanges and AuditEntryKeyValues, each have a
foreign key constraint that references the Ids of the AuditEntries

The business tables of the database all have triggers that write to
the audit tables whenever a record is added, deleted, or updated. These
triggers are not enabled for replication. Rows are NEVER deleted from
the audit tables, and any change to the business tables is documented in
the audit tables before being actually performed.

We are finding records in the dependent audit tables (i.e.
AuditEntryChanges and AuditEntryKeyValues) on the spoke servers that use
Ids that do not exist in the AuditEntries table. Thus, the spokes are
missing the Ids from the AuditEntries table. More strangely, the hub
server has neither the Ids from the AuditEntries table nor the rows in
the two dependent audit tables that use these non-existent Ids.

The foreign key constraint is enforced on on all the servers, but is marked as not for replication.

There are intermittent outages in replication that we believe have
something to do with it. Tellingly, non-replicating clients of ours do
not have this problem.

We suspect that there is a bug in replication and would greatly appreciate it if you would look into this. Thanks.
Sign in to post a comment.
Posted by Microsoft on 12/7/2012 at 2:36 PM
Hi SQLUser722,
thanks for taking the time to share your feedback, this is really important to us.
We will investigate the issue and get back to you.

Best regards
Jean-Yves Devant 
Program Manager Servicing and Lifecycle Experience of High Availability Technologies in SQL Server
Program Manager SQL Server Replication, Change Data Capture, Change Tracking
Sign in to post a workaround.