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.