Safety critical app - Database row validation

This is perhaps a little bit vague, but 开发者_开发百科I'm hoping that in amongst all the people on SO there will be some who have ran into this type of issue before.

Background

Our application is a C# / .NET service that controls train orders. We use LINQ-to-SQL to store the state of the rail network and train orders in a SQL Server 2005 database.

We have a safety requirement that COTS software cannot be "trusted" per se.

Requirement

Thus the risk has been captured as: "SQL server or operating system modifies static or dynamic data."

Our mandate: "Data stored in the database shall be validated such that on read it can be confirmed by the data access code that it has not changed since the last commit."

Question

I would love to find an "automagic" way of fulfilling this requirement.

Failing that, a way to satisfy the condition without having to create columns in every table of the database to store computed hashes in (that we then have to validate against when reading.)

Maybe something like a MD5 Checksum that is saved in either the same or different table to the main data. The checksum would be generated by your C# application so if anyone did an update using raw SQL the checksum would be off.

on read it can be confirmed by the data access code that it has not changed since the last commit.

That is trivial. Under all non-dirty-read isolation levels except snapshot, the data read is the last committed data. By definition, it has not changed since the last commit (in other words, if it has changed then the new values are the last commit). Accidental changes (since the requirements mention 'OS modifies data') are called 'data corruption' and are captured by page checksum, or by TDE.

Perhaps the real requirement is that the value has not changed in the DB since read into the application? Then Optmistic Concurency Control, that's all there is to it. Simply add every 'old' value to your UPDATE WHERE clauses and it will take care of itself. If you didn't update any row (and you'll know that by either checking @@ROWCOUNT or by using an OUTPUT clause), then you know the row has changed. Proactive caching can also be deployed.