Data Migration

Posted on 2012-09-18
Last Modified: 2012-09-26
A third party software company provided us with a java utility to migrate data from a oracle database interface to a Microsoft SQL database interface. In other words, two applications, "Express to N4". All the bugs in the utility have been fixed and it runs properly. However, I want to make sure their is data integrity. Guys, what recommendations do you all have to make sure the same data in Express goes into N4?

So far:

I have both system open and I am comparing each form to validate the same data.


Different ways to validate than having apples to apples comparison.

Any tips will help.
Question by:TexanDonnaP
    LVL 12

    Expert Comment

    Look at this post regarding commands to compare hash values from SQL Server and Oracle.

    It's a good start.

    SQL Server has a checksum() command that will give you the hash value for the entire row - but I don't know of the Oracle equivalent.
    LVL 34

    Expert Comment

    I think hashing or checksumming rows in both databases would work if the structure of the tables is the same.  If you have gone through a conversion from one software package to another, my guess is the layouts aren't the same.

    Your best option may be to do a random sampling of both systems.  Bring up the same record in both and visually verify they are the same.  Then hope for the best.  Definitely keep the old system running for a while.  That way if you notice a problem, you can go back to it later.
    LVL 28

    Expert Comment

    I had some experience similar to this few years ago and i would like to give some additional comments as well here...

    1) Random checks to be done both by the application support teams / Business owners for any inconsistencies. Application support teams checks will generally help to point out if something is not working as expected , values matching are not etc but this alone will not suffice. Business folks has to do their checks for workflows/processes/data flows between interfaces of the application/package etc and should also confirm back if they are seeing any issues.

    2) Not sure whether this is allowed in your apps/system but we had got this done. In the existing system, Business had setup a TEST user/TEST transaction was created/posted in the live production environment which would trigger the required workflows etc and then test the completeness/accuracy/end to end verification was done and again another test transaction was posted in the reverse direction to negate the impact to the data / figures in the live production environment. Similar thing was done in the new application/package environement during the pilot run and again verified for completeness. The results from existing production environment and the new upcoming production system were compared. Infact, we had a report with timestamps etc to track the performance as well in both the systems before the new application/package was successfully signed off.

    LVL 28

    Expert Comment

    Just forgot to mention that random checks which should be done was collated as a list from different stakeholders including ( IT support teams, Business users, Operations managers, Business analysts and IT Development team leads ) - so that it can be made complete with the best of the knowledge pool from all the required shareholders.

    Author Comment

    How about a script or something I can run instantly?
    LVL 28

    Expert Comment

    you can or you might come up with scripts which look at aggregated numbers in the table data before and after migration but if there is a mismatch you need to make sure that whether that is due to the bug you had coded in your script or that is really a data integrity break. Also it would be difficult to nail down to the record / column data which is creating the break if we go with the aggregated data checks script.

    But again on the other side, to compare each and every table for all the records is a time consuming task and will take lot of efforts as well from resources.

    But i would say that you can come with up the script additionally apart from the comments all had given.

    Author Comment


    Thank you for all your feedback. I can see what it takes to have a successful data migration.

    Our company is small structured so departments are really low in numbers. From the top of my head, I only have IT and Operations able to help in the process. What do you recommend?

    - I have 41 tables migrating from the old system to the new one
    - 4 of those tables carry at least 4 years of data (working data)

    Should I split the work after I migrate? Should I ask operations to perform certain task in the old system and also the new system and compare them?

    I need a clear view
    LVL 28

    Accepted Solution

    Ok. i see.

    Get your IT ( support and development folks ) as well to review the plan and sign off . Do the same thing for your operations folks as well. That would help to find if any gaps or something is missing which is very much required to be taken care as part of this data migration exercise.

    Fine, it should be ok for the 41 tables data to be migrated. Get everyones (IT & OPS ) sign off for the old data in the old system first. some might think why is this required and question you but that would help us to be a in a safe position to report/analyze if any issues reported later.. there could be some data issues in the old system itselt and no one might be aware of it until now. So that should not be reported as a data migration issue to you after the migration is completed.

    yes, you are right split the work ( i mean prepare a list of things of what IT needs to check and what OPS need to check before and after the migration ) and go with it. Yes, they could do the checks in both systems and also some random checks which are not written down in the prepared list but they can document what all random checks they did so that will act as a proof for the testing happened post migration. That would help everyone if at all after few months ( not immediately after migration ) someone identifies any migration issues.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now