How is Database Migration done?

Question

i remember in my previous job, i needed to do data migration. in that case, i needed to migrate to a new system, i was to develop, so it has a different table schema. i think 1st, i should know:

• in general, how is data migrated (with the same schema) to a different DB engine. eg. MySQL -> MSSQL. in my case, my destination DB was MySQL and i used MySQL Migration Toolkit

• i am thinking, in an enterprise app, there may be stored procedures, triggers that also need to be imported.

• if table schema is different, how will i then go abt doing this? in my prev job, what i did was import data (in my case, from Access) into my destination (MySQL) leaving table structures. then use SQL to select data and manipulate as required into final destination tables.

• in my case, where i dont have documentation for the old db, and the columns was not named correctly, eg. it uses say 'field1', 'field2' etc. i needed to trace from the application code what the columns mean. any better way? or sometimes, columns contain multiple values in delimited data, is reading code the only way?

Answer 1

I really depends, but from your question I assume you want to hear what other people do. So here is what I do in my current project.

I have to migrate from Oracle to Oracle but to a completely different schema. The old system was 2-tier (old client, old database) the new system is 3-tier (new client, business logic, new database). We have more than 600 tables in the new schema.

After much pondering we scraped the idea of doing a migration from old database to new database in SQL. We decided that in our case i would be much easier to go:

old database -> old client -> business logic -> new database

In the old database much of the data is stored in strange ways and the old client mangles it in complex ways. We have access to the source code of the old client but it is a very large system.

We wrote a migration tool that sits above the old client and the business logic. We have some SQL before and some SQL after that but the bulk of data is migrated via old client and business logic.

The downside is that it is slow, a complete migration taking more than 190 hours in our case but otherwise it works well.

UPDATE

As far as stored procedures and triggers are concerned: Even as we use the same DBMS in old and new system (both Oracle) the procedures and triggers are written from scratch for the new system.

Answer 2

When I've performed database migrations, I've used the application instead a general tool to migrate the database. The application connects to two databases and copies objects from one to the other. You don't have to worry about schema or permissions or whatnot since all that is handled in the application, just like what happens when you set up the application in the first place.

Of course, this may not help you if your application doesn't support this. But if you're writing an application, I strongly recommend doing it this way.

Answer 3

If you are using MS SQL Server, you can use SSMS to script out the schema and all data in one go: SQL Server 2008: Script Data as Inserts.

If you are not using any/many non-standard SQL constructs, then you might be able to manually edit this scipt without too much effort.

Answer 4

I recommend the wikipedia article for a good overview and links to the main commercial tools (and some non-commercial ones). Stored procedures (and kin, e.g. user-defined function), if abundant, are going to be the "hot spots" in the migration, requiring rare abd costly human skills -- as soon as you get away from the "declarative" mood of mainstream SQL, and into procedural code, you cannot expect automated tools to do a decent job (Turing's Theorem says that they actually can't, in a sufficiently general case;-). So, you need engineers with a good understanding of the procedural trappings of BOTH engines -- the one you're migrating from, the one you're migrating to. You can buy that -- it's one of the niches where consultants make REALLY good money!-)