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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 773473 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 12/4/2012 5:03:52 PM
Access Restriction Public



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