TexanDonnaP
asked on
Data Migration
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.
Future:
Different ways to validate than having apples to apples comparison.
Any tips will help.
So far:
I have both system open and I am comparing each form to validate the same data.
Future:
Different ways to validate than having apples to apples comparison.
Any tips will help.
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.
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.
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.
Thanks,
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.
Thanks,
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.
ASKER
How about a script or something I can run instantly?
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.
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.
ASKER
Nav_kum_v,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's a good start.
http://stackoverflow.com/questions/8810471/get-hash-values-from-sql-server-and-oracle-and-compare-them
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.