 |  |  |
| In over 20 years of software development, I designed and implemented over a dozen projects where migration of data and metadata from one database system to another was the main concern. Here are the guidelines of what to expect and how to handle migration of an obsolete legacy desktop database to Microsoft Access. | ||
 | |  |
Overview
Database migration is rarely a trivial task in the real world. You may look at all modern Database Management Systems and the tools available on the market: they all come with some import/export utilities, from one database to another. It is still a difficult task because developers have to frequently deal with legacy obsolete databases, where data loss and unenforced integrity rules are potential risks. Because employers and clients want to redesign their database tables and user interfaces. There will be new business rules and user requirements.
Most modern DBMSs (Database Management Systems) have some import/export utilities, but they all favour the import from one other database to their own. Import/export utilities deal with both metadata and actual data. Intermediate common formats include delimited text or fixed-width fields.
Desktop and Client-Server Databases
Most database migration projects fall in one of the following two scenarios:
- Desktop --> Desktop. Old legacy desktop database (dBase, FoxPro, Clipper, Paradox) into a more modern desktop database, such as Microsoft Access.
- Desktop --> Client-Server. Desktop database to a client-server DBMS, such as SQL-Server, Oracle, Sybase, Informix or MySQL.
I will present here a small 3-4 weeks project of type (1) that I executed in 2007 for one of my clients. They had a legacy Paradox database, stored in multiple files, and needed a transfer and reengineering to Microsoft Access. This included a new redesigned user interface and new business rules.
Database Redesign
If import in Microsoft Access fails - and it frequently does - or if your client wants new business rules and requirements, you may need to redesign the database tables, indexes, views, stored procedures etc. In my case, this was about a Budget database, where data was separately kept for each financial year. The old database used to collect information in the same tables, so at some point millions of records slowed down the process. A first decision was to automatically replicate the same tables for each financial year. Every new year will start now with empty tables and very fast processing. Here are three of the database tables, for the fiscal year 2005-2006:
Initial table columns may have more relaxed business rules and restrictions. If data still cannot be transferred directly from one database to another, create an intermediate format. Some Paradox tables were protected by password, some legacy code was not accessible, and we were lucky to find one free tool on the Internet, to help us recover lost Paradox passwords.
Some tables were partially corrupted and we had to recover the loss. We looked in the data files in binary format and fortunately they were not encrypted. Paradox was a wonderful database when sold by Borland, by at the present time it is no longer updated and supported. There are also differences between supported column names in one database and another, in the supported data types.
Once data successfully transferred, we tried to enforce new business rules and data type restrictions. We went one by one, because at almost every step we encountered exceptions, that we had to detect and fix. Imported valid data was immediately backed up in Microsoft Access.
User Interface
Many companies still have legacy applications, developed by someone longtime ago, with no documentation and a mess in what relates to the code and technologies used at that moment. And in most cases the user requirements and business rules must be extracted through hacking, reverse engineering and pure guessing from what you can. We did this also with our Paradox front-ends, we tried to read the code in ObjectPAL (PAL = Paradox Application Language) and create similar code in Microft Access' VBA (Visual Basic for Applications).
The new required user interface design was centered around a common Search Mask for all forms and reports of the application:
The Data Entry Forms included lists and one-record-at-a-time detailed views. Reports were required in Legal and Letter print format.
We also built a new security system, using Microsoft Access users and roles. The database was supposed to be shared, from a protected network drive, by up to 10 people in the department, within the secured corporate Intranet.
In conclusion: building the new user interface, in Microsoft Access and VBA, was a relatively easy task, once data was successfully migrated from Paradox. And once we could almost guess old business rules from the legacy code and validate them with the client.
Subscribe and Share: 
