Data migration can be a lot more complicated than you expect. Here's why, and what to do about it

Here's how you make your data migration a winner.jpg

If like most developers you are using a relational database as part of your digital landscape, then you will likely be required to manage the migration of data from a legacy system to a new platform at some time in your career.

Be aware, as many of these go wrong. Poor planning and lack of attention to detail, too many assumptions, and minimal testing are just some of the reasons why many data migrations fail and cause costly disruption to the business.

So here are a few tips to keep your data migration project on track.

Let's quickly start at the start

The first thing you need to do is understand what has driven the data migration project in the first place, whether you are migrating your data from one application to another, or actually across databases. Here are three familiar scenarios:

1. New Platform
Developing a new application is an excellent opportunity to review your database. You can make a change if you consider your license costs too high, or your performance outcomes too low. You might also build an application to support multiple database back ends, or develop in dev mode knowing you'll need to migrate to a new database before going live, fully aware that any change will require an amendment to the tables, views and indexes along the way.

2. Updating a Legacy Application
You might be required to update a legacy application with new features that meet evolving business demands. This is a chance to migrate both the data and the database too on the basis that you may be able to reduce total cost of ownership or improve performance.

3. Change in Vendor Arrangements
While your software applications may remain the same, your business arrangements may change. A business merger, cost reduction program, or vendor consolidation project can demand a change in databases, and as such, commence a data migration project.

You'll have to overcome some challenges

Each of these initiatives provides an opportunity to fix any bugs, resolve any long-term performance issues, or move to your favourite database management system. They also present potential catastrophe should anything go wrong as you migrate the data. Here are some challenges you may face:

Database-documentation.jpgPoorly Documented Legacy Systems
You've arrived and been asked to migrate data from the old system to a new application. It seems the legacy application has been managed by generations of DBAs all of whom have long gone, along with the documentation. You now have a knowledge gap around duplicates, missing information, misspellings and erroneous data. Mappings might be missing key values, and a disparity of data structure the size of the grand canyon between legacy data sources and the new target system may not be entirely understood. The danger here is that you start making assumptions that cause critical failures in user acceptance testing.

Poor Data Quality
On the other hand, you may be aware of the missing, invalid or inconsistent legacy data. Be careful though. It's easy to overlook anything that seems harmless now between the source and target systems. This could be field names with the same naming convention but different data meaning, or the opposite with different field names capturing the same data across multiple data systems. Other issues such as date and time formatting can also be overlooked to cause an unnecessary "own goal" in user testing.

Inability to Validate a Specification
There are times when you do have a solid understanding of your source data. Be careful once again. This does not always result in a robust specification for the target system because you are in the early stages of the migration and any misses here can have a critical impact later. Use actual data and avoid relying on documented aspirations alone.

Constant Changes
Your business requirements will almost certainly change once testing gets underway and the business sees how the new system performs. That's OK. After all, change is what drives data migration in the first place. Just be sure to apply changes quickly to maintain the schedule.

Poor Communication
As we outlined earlier, a data migration project is almost always part of a broader initiative. That means you cannot work in isolation. You need to coordinate all types of people who may be using different technologies and operating in-house or external to the organisation. Working collaboratively will improve efficiency and reduce the chance of assumptions and misunderstandings.

Poor Testing
Avoid corrupted data getting into your new system by having your developers and business users testing the new system throughout the project rather than wait until the very end. You need to be sure the correct data in the proper format is loading. We suggest exploring multiple scenarios with full volume data to cater for as many possibilities as possible.

Here are some suggested tips

You can help yourself achieve a successful outcome with any of the following suggestions:

  1. Engage a data assessment, in the beginning, to identify any issues, resolve data relationships, and ensure you have the facts at hand. Using a 3rd party for this can be helpful to create an "assumption-free" zone and manage communication across the group.

  2. Validate your data specifications with actual data, not lightweight sample data. You need to be dealing with the real world as early in the migration process as possible.

  3. Build error handling into the migration process to call out unexpected discrepancies between source and target data structures.

  4. Set up a cross-functional team that can be led by a 3rd party to connect and challenge your technical and business teams to ensure everyone id included, informed and all requirements are met in the end.

  5. Turn changes as quickly as possible to enable adequate time for testing and an ability to keep moving forward. Make sure you track all change requirements in a project management system, or with a 3rd party migration consultant, to keep everyone in the loop and be able to report on the project in the end.

  6. Conduct documented test cycles and shorter unit tests at every stage of the project starting as soon as possible. These tests will validate and confirm data migration progress throughout the project and identify any potential issues while you have time to resolve them without too much pressure.



Our Migration Assessments can help you gather the facts to ensure all requirements are possible from the start. We will analyse all data issues and help you make a plan to resolve. Error handling is designed and documented based on the unexpected discrepancies we find between the source and target data structures. We can then conduct the Database Migration itself if you prefer.

Our database experts are on hand to answer your questions and ensure peace of mind at every stage of the process. Call us on +612 9452 9191 if you would like to discuss how we can help you.

Topics: Database Migration, PostgreSQL 10, PostgreSQL