5

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?

Improve this question
2
  • Do you have sufficient space and server resources to be able to retain the original? And can you query both after the upgrade? After the upgrade will the new system remain unchanged? If not how will you prove that differences aren't by users rather than the upgrade?
    – Sir Swears-a-lot
    Commented Dec 26, 2015 at 8:41
  • I agree that SQL compare is a great tool for this. However I think you need to consider how to keep copies of the database both immediately before and after the upgrade. So that you can prove they're consistent at a point in time and can rule out external interference/influence.
    – Sir Swears-a-lot
    Commented Dec 26, 2015 at 8:43

4 Answers 4

Reset to default
12

I'd buy Red Gate SQL Data Compare so 100% comparisons are possible.

Improve this answer
1
  • 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
    Commented Mar 30, 2009 at 10:51
11

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.

Improve this answer
2
  • 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
    Commented Mar 30, 2009 at 11:20
  • @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
    Commented Mar 30, 2009 at 12:07
0

MD5 checksum of full dump?!

Improve this answer
1
  • 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
    – cjk
    Commented Mar 30, 2009 at 11:14
0

Record the total row count for all tables before migration or upgrade, and compare these counts after migration.

Improve this answer
1
  • 2
    A COUNT doesn't guarantee the same data, only the same number of rows.
    – gbn
    Commented Feb 13, 2012 at 16:09

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.