Data migration process for an application whose architecture is changing?

Question

Hi community,

After having used an application for over 10 years, and been constantly limited by its lack of extensibility, we have decided to rewrite it fully from scratch. Because the new architecture differs from the old application, the database is also different. Here comes the problem: Is there any industrial process for migrating the data from the previous database to the new one? Some tables are alike, others are not. Overall, we need a process that will help us make sure that no data or logical constraints is lost during the migration.

Thanks

PS: The old and new database are both Oracle databases.

Answer 1

I've heard of several different ways to attack problems such as this. The most simple solution I've seen is to use a Microsoft Access database and use ODBC connection to connect to both the new and old Oracle databases. You can then use Access to migrate and transform the data as you need.

The more elegant solution involes installing Microsoft SQL Server Development tools. You can use Business Intelligence Development Studio to create a SSIS package with two Oracle endpoints. SSIS can handle the heavy lifting of transforming the data between the databases and you can run the package locally so you don't have to have an instance of SQL Server running anywhere.

There's a tutorial series for SSIS at:

http://www.developerdotstar.com/community/node/364

You might also want to check out Oracle Warehouse Builder (OWB). The name is a little confusing, but it's Oracle's ETL (Extract, Transform, and Load) package. I've never used it personally, but it might do what you're looking to do as well.

Answer 2

Although you don't specify this in your question, I assume that you're going to develop the new version of your application/database, and then at some switchover point you need to migrate all of the live data from your old database into your new database.

If this is the case, then you're really asking about two distinct processes: the migration (with some modifications) of the database structure, followed later by the migration of the data itself.

For the first process, the best tool is you, the developer (I don't mean you're a "tool" - you know what I mean). You could bring over the structure of the old database and then change it as necessary for the new version; however, this approach in general tends to leave too much of the old structure behind. I think it's better to take advantage of the situation and rebuild the database from the ground up, using the original database just as a general reference.

For the second process, I would treat the data migration as a separate task requiring a separately-written and -tested application. This application could be a set of scripts or a compiled application or whatever is most convenient for you. Because your old and new databases will not have the same structure (and may in fact be very different), there are no commercial tools out there that will handle this task for you automagically. By treating this as a distinct application that you write yourself, you can test the data conversion process many times before your "go live" date.