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

• Tuesday, December 04, 2012 10:26 PM

   

   
  

  0

  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
  table.

  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.

  Does anyone have any suggestions as to what could be causing this
  behavior, or how we can go about trying to recreate or analyze this
  problem? I would greatly appreciate any advice that anyone can provide. Thanks.

  • Reply
  • Quote