August 12, 2009 at 8:06 am
Hi,
I have an old db and a new db. The table structures have changed alot since the new db has been normalised.
I need to use one tool to perform both data migration and data comparison. A few days after the migration completes i need to check if data in the old db has changed- if it's different to data in the new db.
If yes, then i have to write the changes into a log file and not update the new database.
What is the best tool to perform both data migration and data comparison?
Will SSIS help me achieve both results. If yes, how? Is there some sample that i can learn from?
Also, should i first import the data from the old db to a temp db with the new db structure and than to the new db and perform the data comparison on the temp db?
Thanks
August 12, 2009 at 10:12 am
Let me make myself more clear 🙂
The primary keys are different in the 2 dbs. So checksum won't do the trick here.
We don’t have control over the old application and old db. We will just have access to it for migration and comparison purposes.
When the old db is updated than the new db should also be updated. The team leader wants to compare the data to know that the new db gets the data correctly and we end up with the same results for the product. Later the old db won’t be used anymore.
The differences have to be logged into a readable file.
I think SSIS is fine for migration but will it also help in the comparison?
Thanks
August 12, 2009 at 1:19 pm
I'm not sure that there is a tool out there that will do what you want. Any comparison tool is going to be looking to compare the same schemas.
One thing you could do is to create views in the new db that match the tables in the old db then you could do a comparison using linked servers and EXCEPT.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2009 at 1:29 pm
now many columns and rows are in the tables you wish to compare?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 12, 2009 at 2:08 pm
There is not an out-of-the-box solution for you. You can build your migration out in SSIS or just script it in T-SQL. Regarless of the method, you'll be stuck with coding out your comparison with whichever tool you choose.
By the way, you said that your PK's are different between systems, but I'm sure you still have a way to uniquely identify an entity from one system to the next, right?
August 12, 2009 at 2:09 pm
Oh, and as far as auditing new changes in your old system, can't you run a SQL trace to capture the INSERTs/UPDATEs/DELETEs?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply