We are a retail shop that has a central data repository which collects data continuously from geographically distributed 1500 odd servers (each store has one SQL Server instance). Like a one to many architecture.
Our auditing team wants to know how we are ensuring data integrity between any given store and the central repository. Although row-count comparison seems like a plausible option; implementing it is a herculean task: 1500+ stores. 200+ replicated tables. Continuous incoming data, etc.
Even if we implement row-count comparison, just because two tables have the same number of rows, doesn’t mean that the data in them should match.
Appreciate any suggestions and help. Thank you so much.