Tuesday, December 04, 2012 10:26 PM
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.
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.
Wednesday, December 05, 2012 2:43 AMModeratorDoes the conflict viewer shed any light on these missing rows?
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
Wednesday, December 05, 2012 10:27 PMThanks for your reply; it's an honor. I'm not familiar with that tool. Our replication setup is transactional, and not merge. Would it still be useful?