How is database migration done?
I remember in my previous job, I needed to migrate data. in this case I needed to migrate to a new system, I needed to evolve, so it has a different table schema. i think 1st i should know:
-
in general, how data is transferred (with the same schema) to another DB engine. eg. MySQL → MSSQL. in my case, my destination database was MySQL and I was using MySQL Migration Toolkit
-
I think that procedures, triggers can be stored in an enterprise application, which also need to be imported.
-
if the table schema is different, how would I do it then? in my previous job, I did a data import (in my case, from Access) to my destination (MySQL), leaving the table structures. then use SQL to select data and manipulate as needed on the target destination tables.
-
in my case where I have no documentation for the old db and the columns were named incorrectly eg. it uses say 'field1', 'field2' etc. I need to trace from the application code what the columns mean. any better way? or sometimes columns contain multiple values in delimited data, is reading the code the only way?
source to share
I am really addicted, but from your question I am assuming that you want to hear what other people are doing. So here is what I am doing in my current project.
I need to go from Oracle to Oracle, but to a completely different scheme. The old system was two-tier (old customer, old database), the new system was three-tier (new customer, business logic, new database). We have over 600 tables in the new schema.
After a lot of thinking, we figured out the idea of moving from an old database to a new database in SQL. We decided that in our case it would be much easier for me:
old database -> old client -> business logic -> new database
In the old database, most of the data is stored in strange ways, and the old client manages it in complex ways. We have access to the source code of the old client, but this is a very large system.
We wrote a migration tool that sits above the old client and business logic. We have some SQL before and some SQL after that, but most of the data is migrated through old client and business logic.
The downside is that it is slow, full migration takes over 190 hours in our case, but otherwise it works well.
UPDATE
Regarding stored procedures and triggers: Even when we use the same DBMS on the old and new system (like Oracle), procedures and triggers are written from scratch for the new system.
source to share
When I was doing the database migration, I was using an application, not a regular database migration tool. 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 that there is still not everything that is handled in the application, the same as what happens when you install the application in the first place.
Of course, this may not help you if your application does not support it. But if you are writing an application, I highly recommend doing it this way.
source to share
I recommend the wikipedia article for a good overview and link to major commercial tools (and some non-commercial ones). Stored procedures (and cousins like UDF), if plentiful, will become migration hotspots requiring rare and expensive human skills - once you get away from the "declarative" mood of mainstream SQL and into procedural code, you can't expect that automatic tools will do a decent job (Turing's theorem says that in fact they cannot in a fairly general case ;-). So, you need engineers with a good understanding of the procedural attributes of BOTH engines - the one you are moving from, the one you are moving from. You can buy this - this is one of the niches,where consultants make REALLY good money! -)
source to share
If you are using MS SQL Server you can use SSMS to script from schema and all data in one go: SQL Server 2008: script Data as Inserts .
If you are not using any / non-standard SQL constructors, you can manually edit this script without much effort.
source to share