Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are migrating our database to a new schema but want to validate the data has been moved correctly.

Traditional data comparison tools can compare two databases for differences if the schemas are the same. In our case there have been changes to the table designs but all the data from the old schema is in the new one, it's just been moved around a bit, and I need to make sure it is correct. We have tens of millions of rows so manual inspection is not an option.

Are there any tools which could aid in this type of comparison?

If not are there any libraries/frameworks which could help kick start the development of a custom solution?

I'm happy to use a database specific solution if necessary, in this case for SQL Server 2008.

My soluton: I'm comparing the two data sets by creating a VIEW of each table on the old database with the same fields as the new database table.

I then compare the data using the technique described here: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

I'm lucky in this migration as the overall table structure is similar to the old database, with fields moved from one table to another, dropped or added. In the case of the dropped and added there is nothing to compare, for the fields which have moved or been aggregated I do calculations in the view to provide the correct information for comparison.

The UNION comparison shows me only the rows with differences, so as soon as the data is correct I get an empty result set.

share|improve this question
1  
How will you know data is correct if schemas have changed? What if a table has been split, or combined etc? –  gbn Jun 13 '11 at 15:35

5 Answers 5

up vote 4 down vote accepted

It's not impossible to compare two different schemas, it's a calculation of how confident you are in the result. I've essentially borrowed from Bank Reconciliation techniques

Important: This reconciliation isn't about making sure the destination exactly matches the source in it's data context (there's a reason you're migrating to a new system), but you do need to explain any discrepancies!

Basis:

  1. Identify metrics that you will use to measure (ie: total number of users, sum of all their ages, list of user id's and their postcodes...) I try to use several metrics from: Totals, Averages & sample/detailed records
  2. Dump this data into a common location (via views/reports/whatever is reasonable)
  3. Compare your data and make sure any discrepancy can be explained

I've generally split the comparison into several methods (in particular for the detailed metrics):

  • By Table: Usually using the Source Table as the definition, creating a set of views on the destination DB to try and reproduce the Source Table Data
  • By Object: Depending on the DB and what you're storing it might make more sense to create a holistic view of the objects (ie: the user) across several tables, similar to above creating a set of views to return that object as a set of results
  • By Reports: If the Source DB has a good comprehensive set of Management Reports, another technique is to reproduce those reports with the exact same formatting

Techniques:

No matter what method I've used to produce what will be compared, I end up with a set of files/views/dbs that hold the reconcilable Source and Destination data, then depending on the medium I can use one of the commonly available tools to compare them. My own preferences are:

  1. File Comparison
    Create two different folders for SourceDB and DestinationDB (time/version stamped so I know when I did the reconciliation) and dump out the output of my metrics as relevantly named files, then use a comparison tool (such as CSDiff) to identify differences

  2. Excel Comparison
    In particular when dealing with Management reports, I'll create a set of Excel of workbooks to compare the reports (in essence making use of VLookups and comparing totals)

  3. Data Comparison
    Rather than outputting the reconciliation data to files or reports, output them to separate DBs, then use something like Redgate SQL Data Compare to compare the DBs

Other tools:

Not tried any of these, but a quick google search on 'Data reconciliation tools' gives:

There's others, but those were the ones that looked reasonable

share|improve this answer
    
Thanks for the suggestions. As you say, it's not impossible (I'm working on a solution at the moment) it's just difficult. I'll look in to the methods you mention. –  Tony Jun 15 '11 at 8:59
    
No worries @Tony, trick I found is to try and break it into smaller steps and then find existing tools for each step (you will need to write some custom stuff, it's just about minimizing that effort where possible) –  Andrew Bickerton Jun 15 '11 at 9:37

If you want to compare data in two different database designs then you'd have to write hand coded SQL to compare data.

  • What if a table has been split, or combined etc?
  • What if you had datetime, now you have smalldatetime = data will be different
  • ...

There is no library of framework for checking data is the same in two disparate databases.

Only you know what you'd changed or modified

share|improve this answer
    
I feared as much but thought I would ask in case someone had done such a comparison. I hoped there might be a tool to speed up the query writing but as you say, I'll probably have to have to write something from scratch. –  Tony Jun 13 '11 at 16:37
1  
@Tony: yep: it's because any tool can't separate "what is wrong" from "what we changed" –  gbn Jun 13 '11 at 16:39

It's going to be impossible for you to compare different schemas. To solve your problem I would figure out what data you need to compare. I would then create a view on each server that would pull back the data I wanted to compare (take in account for data types and such).

Once both views are the same, I'd use a third party tool like Red Gate Data Compare to see what rows are different.

Sounds like a pain. Good luck!

share|improve this answer
1  
You say it's impossible and then provide a possible solution :) In fact, the method I was working on is similar to your suggestion except I'm not comparing the result using an external tool but doing it all in SQL. Thanks. –  Tony Jun 15 '11 at 9:00

Couple of years ago I wrote a tool to do just that - a data comparison between two databases. Since then I converted it to a commercial software and published at

www.sql-server-tool.com

  • a single license costs 99$, but you can try it for free for 30 days.

The program - named SCT for "SQL Server Comparison Tool" (I never was good at naming things :) - has lots of fine-tuning options, like: ignoring selected columns or specifying comparison method (record-by-record or primary key/index comparison). Comparison "sessions" can be saved and re-played later without need of re-entering the parameters. Command line parameter can be used to fully automate comparisons.

For tens of millions of rows it may be a bit slow - in that case I'd recommend to start with a smaller subset of data - let's say compare only first 1,000 rows - and see if any fine-tuning of the process is needed.

Dariusz Dziewialtowski-Gintowt

share|improve this answer
    
Thanks for recommending your app, I tried it out but it does not compare different schemas, which is what I need to do. It failed while getting the data from the first table. I'm also unsure if the app will handle the quantity of data I have as there does not seem to be a way to limit the number of rows compared; the table I tested it on has 99 million rows. –  Tony Jun 14 '11 at 9:19

I suggest you to try free trial version of Devart's Data and Schema Compare Tools. If you like them in furute you can buy full professional version (it is not expensive). More about the tools here:

SQL Data Compare - http://www.devart.com/dbforge/sql/datacompare/

SQL Schema Compare - http://www.devart.com/dbforge/sql/schemacompare/

share|improve this answer
1  
Perhaps you could describe why the links you've provided here are worth someone investing time (and money) instead of just hitting a sales blurb. Perhaps you can discuss why the tools will do what they need and how. All you've done so far is "buy these tools". Additionally, we've got a great entry in our FAQ for how to do this sort of promotion, so we hope you'll take a moment to read that, and realize that what this site REALLY thrives on is great interaction between users. If you want to do some sort of ad-insertion you may want to talk to the SE Sales Dept? –  jcolebrand May 23 '13 at 17:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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