Data Migration

Posted on 2012-09-18
Medium Priority
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

ID: 38409493
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 35

Expert Comment

ID: 38409564
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

by:Naveen Kumar
ID: 38412186
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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 28

Expert Comment

by:Naveen Kumar
ID: 38412192
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

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

Expert Comment

by:Naveen Kumar
ID: 38412260
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

ID: 38414315

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

Naveen Kumar earned 2000 total points
ID: 38416138
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 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