I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-migration.
Any ideas?
Normally I wouldn't vote this up (due to the price mostly), but seeing as how this requires the utmost accuracy, I need to +1 this.
TheTXI
2009-03-30 10:51:03
This *could* fail based on the way the differing system would dump the data, but if it did match then could be used to say they are the same. However, given the volume of data, two differing sets could produce the same hash because of the low number of hash potentials compared to the data size
ck
2009-03-30 11:14:57
+5
A:
In a court of law (at least the ones I'm familiar with, which is, admittedly, one), you have to be able to prove the source of your proof as well as the proof itself. In other words, demonstrate how you are certain that your information, proving the data are identical, is true.
Unless a third party vendor is willing to stand up in a court of law and testify under oath that their comparison solution is perfect, it won't be a very reliable method, legal-wise.
The best solution in that case is the simplest - write your own script which dumps the data in the tables to a series of text files (i.e., select statements with order by clauses, or post-sorting, to ensure identical order) and compare the two versions of text files. The author of the script (you) will be happy to testify on the accuracy of your script whereas a third-party vendor may not.
Note that the contents of your data tables may not be enough - you may need the contents of system tables to ensure that indexes, stored procedures and so on are converted correctly.
As always, legal advice on the web is worth every cent you paid for it, so you should consult a real lawyer if it's as important as you indicate.
paxdiablo
2009-03-30 11:16:42
I would say, combine this answer with the one of Lennart: You do not need full dumps to compare. You could split up your dumps into smaller ones (per table/per year) and only store the MD5 checksums to compare.
GvS
2009-03-30 11:20:29
@GvS, I don't believe MD5 checksums would add anything to this method. This method already detects with perfect accuracy if the data has or hasn't changed, and MD5s on their own would not be as reliable a way due to the possibility, however small, of an identical hash value from different data.
paxdiablo
2009-03-30 12:07:31
A:
write a script in a such a way that take the count from all the tables before migration or upgrade and take the count after migration. then compare the count's. i think this will help u
Anoop
2009-05-06 02:22:50