Migrating legacy data to a new application can be intimidating. Your old data isn’t going to fit perfectly in your new model. By following the steps below, you can ensure a smooth transition to your new application and wave goodbye to the legacy structure that is weighing you down.
1. Review Column Mappings with the Client
As a software consultant, it can be tricky to get your non-technical clients deeply involved in the data migration process. However, this is necessary to really understand their legacy app.
In the most recent migration I did, the legacy app was an out-of-the-box solution that included around 900 empty tables and several tables that didn’t map one-to-one to any table in the new application. In one particularly annoying instance, a task was saved in one table if it had a deadline more than two weeks out, and another table if it was sooner.
In a situation like this, having meetings with the client to do non-technical walkthroughs of a technical problem can be helpful. One way to do this is by walking through screenshots of the old application (marked with database field names for your benefit) and drawing arrows to screenshots of the new application.
2. Use the Right Tool
A great method for migrating data is using data mapping tools. These allow a user to connect two data sources, map fields to each other, and embed logic and transformations into the migration tasks. I’ve had good experiences with Altova Mapforce, but many migration tools can do the trick.
3. Make It Repeatable
I did several pushes to the staging database to test the migration. While it can be difficult to include every little thing in a big migration, including as much logic as possible in the data mapping tool helps make the process repeatable.
A common mistake here can be including SQL scripts that must run in addition to the migration. Instead of transforming the data in the SQL script, include logic in the table mapping. This way, everything stays in one place.
Take notes on the deployment as you go along, so you have a step-by-step process by the time you’re ready to go to production. This is especially important if your tables need to be imported in a specific order because of foreign key constraints.
4. Agree on Dummy Data
New applications are, by definition, going to introduce new functionality. Not every field is going to have an equivalent in the legacy app. However, if your new application is expecting data, it can lead to exceptions that didn’t come up in testing. For these fields, it’s important to agree on defaults and dummy data you can use as a placeholder during the migration.
If you do create dummy data, create a plan to switch over / retire the data types you use to avoid confusion for future admins.
5. Regression Test the Site
As mentioned above, legacy data can often be incomplete and lead to errors that didn’t come up during your regular testing. Migrate the data to a staging environment as early as possible to give the client ample time to test it. A full regression test of the site’s functionality should be done on the legacy data to ensure that all Create/Read/Update/Delete functionally, validations, and API calls from third-party applications return the expected data.
6. Secure the Database
Unfortunately, things happen, and sometimes, you wake up one day to an empty database that’s been compromised. If you follow the “make it repeatable” step above, you’ll be able to have all your data back in place by the next day in the event of an issue.
This also reveals another important fact of the data migration process – lock down your staging database. This is best practice regardless of the project you’re working on. However, if you’re facing a tight deadline, securing your database may not be top of mind.
In the case that your database has been compromised, don’t forget to limit the IP range able to access the database, change the password on the accounts to be more complex, and limit permissions on what each account can do in the database.
These steps will help with your data migration process, but there’s a lot more you can and should do to bring your application up to date. Read more about legacy applications here and 
to the consultants at DragonSpears for expert help.
