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.

  • Whatever "collects data continuously from geographically distributed 1500 odd servers" has to do that. If you use SQL Server or 3d party replication, this is what ensures integrity. If you roll your own, you roll your own.
    – mustaccio
    Jun 27, 2019 at 15:49
  • We use SQL Server Merge Replication. Although merge ensures data syncs, there could be some data discrepancy.
    – RaviLobo
    Jun 27, 2019 at 15:55
  • Do a hash on a SELECT important_field FROM shop_table; compared with SELECT important_field FROM global_table WHERE shop_id = X; every so often - obviously, you maintain historic data on the shop site. The odd reconciling of receipts with accounts would also help.
    – Vérace
    Jun 27, 2019 at 18:10
  • Thank you @Vérace. We have thought about hashing. However, hash will be on a sample set of table/data. Not the entire data set (that would be over engineering).
    – RaviLobo
    Jun 27, 2019 at 18:30
  • That was my idea too - I meant to do the hash over data from important_field (not entire tables) over, say, the last month, so that way you would pick up problems early! Best of luck with your project! :-)
    – Vérace
    Jun 27, 2019 at 19:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.