Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Checksum database

Author  Topic 

Geremore
Starting Member

2 Posts

Posted - 2011-08-22 : 06:25:06
Hi,

Got a historical database with delicate information. At any time, I want to be able to check that the data in the database is unchanged. The database is huge, more than 1 billon records, more than 1K tables. Tried to compare checksums of different dumps (backups), which doesn't work. Is there another way?
Thanks in advance for any help.

Geremore

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-08-22 : 08:58:02
Make the database READ-ONLY.
Make a Golden-Copy onto a CD and re-install same on a nightly basis.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-08-22 : 11:23:17
Just in case you need a broader solution:

You can have UpdateDate columns in tables with a TRIGGER which will set the UpdateDate column on each insert/update. SELECT MAX(UpdateDate) FROM MyTable will then tell you when it was last changed. This won't help with deleted records.

You could create an audit table, and a trigger, that logs changes to the Audit table. This will include deletions. Then a query of AuditTable will tell you what has changed (since date X).

You could have a trigger that explicitly disallows INSERT/DELETE/UPDATE. Then users can only select. You can control permissions to only allow SELECTs, but the Trigger would be a LongStop in case something with SysAdmin permissions accidentally did an Update.

But for a straightforward fix using Andrew's suggestions.
Go to Top of Page

Geremore
Starting Member

2 Posts

Posted - 2011-08-23 : 05:20:06
Thanks everybody! The database is read-only (of course). The fact that someone finds out the administrator password, adjusts data, makes incorrect reports is a risk. Guess the best is randomly check the data reported.
The scripting of all data (1 file per table) in order to generate a checksum fails: Cannot access properties or methods... because it has been dropped.
Go to Top of Page
   

- Advertisement -