Best way to convert a legacy database that's full of data?

Maurice,

Which DBMS ?

Each DBMS has different facilities available and different capabilities, especially when it comes to linking two databases together. If you are migrating everything, you'd probably be a lot easier putting it all into one database which will save you have to use "federated" support to join across databases.

You also have a chance to think whether you want to change DBMS.

Are you talking about modifying the structures ?

If so, the book "Refactoring Databases : Evolutionary Database Design" by Scott Ambler and Pramodkumar J Sadalage (Addison-Wesley, ISBN-10: 0321293533, ISBN-13: 978-0321293534) is a great primer for the types of changes you will have to make.

The biggest challenge you are going to face is making modifications while not breaking existing applications (I assume you have existing apps).

If I can be any help then drop me a line : especially if you wish to consider DB2 as the DBMS of choice going forward. DB2 has good Rails support, and getting better by the day, and the free DB2 Express-C has no limit on database size, only on memory (4 gig) and processors (2 sockets, not cores).

HTH

Phil (teamdba@scotdb.com)

Hi Maurice,

How are you connecting to Access? I hope this isn't too OT, but there are a lot of threads in this and other forums saying it's a mess/not worth it.

Ron

It is a mess - so we don't! :smiley: We use the mdb-tools package to extract the data as CSV, and the schema as SQL. We then convert the schema to MySQL compatible SQL, and dump it into MySQL. From there we run a mysqlimport on the CSV files to populate the DB with the old data.

It's a mess for sure, but once it's in the MySQL DB it's much nicer to work with, and once it's in the new structure we should be all happy smiling Railers...

/Jocke