Planning Data Migration from Legacy Databases

Establish the live position before planning change

Data migration from a legacy database is not a technical task that gets handed to a developer at the end of a project. It is a business-critical activity that determines whether the new system starts with reliable information or inherits the problems of the old one. The planning stage is where the majority of migration risk is either contained or created.

The core purpose of planning is to answer three questions before any data moves: what exactly lives in the legacy database, which of it needs to exist in the new system, and what has to change for that data to fit. These questions sound straightforward, but legacy databases rarely cooperate. Schemas evolve over years without consistent documentation. Business rules end up embedded in application logic rather than database constraints. Fields get repurposed, deprecated, or left populated by processes that no longer run.

Understanding the Source Before Planning the Move

Planning begins with a data inventory, not a migration script. Someone needs to examine every table, every column, and every relationship in the legacy database and document what it actually contains, not what the original schema diagram suggested. This means reviewing sample records, checking for orphaned rows, identifying tables that appear unused, and confirming which fields are genuinely populated versus mostly null.

At this stage, the business side of the team has work to do as well. A column labelled status might contain values that only long-serving staff can interpret. A table with no foreign-key relationships might still represent a critical business concept that the new system must preserve. The planning process needs to bring together people who understand the data's meaning with people who understand its structure.

Defining the Scope of What Migrates

Not everything in a legacy database deserves to move. Over time, databases accumulate test records, abandoned drafts, historical snapshots that serve no operational purpose, and data belonging to processes that were discontinued years ago. Migrating all of it increases cost, extends timelines, and imports noise into a clean system.

Planning requires explicit decisions about what to migrate, what to archive separately, and what to discard entirely. These decisions need business ownership, not technical assumption. A developer cannot reliably determine whether a ten-year-old order record is required for compliance, useful for reporting, or simply dead weight.

Mapping Source to Target

Once the scope is clear, the next planning step is a field-level mapping between the legacy schema and the new system's data model. This is rarely one-to-one. A single legacy table might split into several new entities. Multiple legacy fields might merge into one. Data types, formats, and reference values will differ. Currency fields stored as strings might need to become decimal values. Date formats might need normalisation. Free-text fields might need to map to structured dropdowns, which means deciding what happens to values that do not match any new option.

The mapping document becomes the primary reference for the migration itself. If it is incomplete or ambiguous, the execution stage will stall with constant clarifications, or worse, it will proceed with assumptions that corrupt the data.

CRM Migration

When replacing a legacy CRM, the planning focus is usually on contacts, organisations, interaction histories, and custom fields. A common complication is that custom fields in the old system were added ad hoc over years, often with overlapping purposes. Planning needs to identify which custom fields carry distinct information and which are duplicates under different names. The new CRM's data model may not support the same number of custom attributes, forcing consolidation decisions early.

Another frequent consideration is related records. If the legacy CRM stored emails, notes, and task histories in separate tables with inconsistent linking, the plan must specify how those records map to the new system's activity or timeline model. Gaps in the relationship data will mean lost history, which the business needs to accept explicitly rather than discover after go-live.

Customer Portal Migration

Legacy customer portals often hold a mix of user accounts, documents, support tickets, and transaction records. Planning a portal migration means deciding which users should be carried forward. Dormant accounts from years ago may not warrant migration, but regulatory requirements might prevent their deletion. The plan needs to distinguish between active users who need seamless access and historical users whose data must be preserved but who do not need login credentials in the new system.

Document migration adds another layer. If the legacy portal stored documents as files on a server with paths recorded in the database, the plan must cover how those files transfer, whether the storage structure changes, and what happens to documents whose database references are broken or missing.

Internal Admin System Migration

Internal systems often contain operational data that is poorly understood outside a single team. A legacy admin system might track workflow states using a status field whose values were never formally defined. Planning requires the team that uses the system to document what each status means in practice, which ones are still active, and how they map to the new system's workflow stages.

Reference data is another practical concern. Internal systems frequently rely on lookup tables, configuration settings, or hardcoded values that exist only in the database. If these are not identified during planning, the new system may launch without the configuration it needs to function correctly.

Choosing a Migration Approach

Planning must also settle the overall migration strategy. A big-bang approach moves everything in a single window, which simplifies the process but creates a concentrated point of risk. A phased approach migrates data in stages, perhaps by entity type or by business unit, which spreads risk but requires the new and old systems to coexist temporarily, often with synchronisation between them.

The right choice depends on how tightly the data is coupled, whether the business can operate with split data during transition, and how much complexity synchronisation would introduce. This is a decision with operational implications that should be made during planning, not left until the migration is underway.

Treating Migration as an Afterthought

The most damaging mistake is treating data migration as a final step that gets planned once the new system is nearly built. By that point, the new data model is fixed, and any mismatches with the legacy data require either expensive rework or awkward workarounds. Migration planning should run in parallel with the new system's design, so data realities can inform schema decisions before they become costly to change.

Assuming the Legacy Documentation Is Accurate

Legacy databases almost never match their documentation. Schemas change without diagram updates. Columns get repurposed without name changes. Constraints get relaxed or removed. Planning based on documentation alone will miss discrepancies that only emerge when actual data is inspected. Any plan that does not include direct examination of the data itself is incomplete.

Underestimating Data Quality Issues

Legacy data often contains duplicates, incomplete records, inconsistent formatting, and values that violate the old system's own rules because those rules were added after the data was created. Planning needs to account for how these issues will be handled, whether through cleansing before migration, transformation during migration, or rejection and manual review. Ignoring quality issues does not make them disappear; it guarantees they will surface in the new system.

Key Checks Before Proceeding

• Completeness of the data inventory: Has every table been examined, or only the ones that seem obviously important?
• Business sign-off on scope: Have specific decisions been recorded about what migrates, what archives, and what discards, with business owners named against each decision?
• Field-level mapping coverage: Does the mapping document cover every field in scope, including edge cases like null values, empty strings, and default values?
• Handling of unmappable values: Is there a defined process for legacy values that have no direct equivalent in the new system?
• Reference data identification: Have all lookup tables, configuration records, and system-level data been included in the plan, not just business entities?
• Rollback definition: If the migration fails or produces unacceptable results, is there a clear, tested path back to the legacy system's pre-migration state?
• Rehearsal plan: Does the plan include at least one full rehearsal migration against a copy of the data, with enough time to address what the rehearsal reveals?

Limitations of Planning Alone

Thorough planning reduces risk substantially, but it cannot eliminate uncertainty entirely. A legacy database may contain behaviours that only manifest under specific conditions, such as particular date ranges, unusual character encodings, or records created by discontinued processes. No amount of planning can guarantee discovery of every anomaly before the migration runs. What planning can do is ensure that when surprises appear, there is a clear process for handling them rather than ad hoc decisions made under pressure.

Turn the assessment into a controlled next step

The next stage, covered separately, addresses how to verify that the migrated data is accurate and complete through systematic validation. Planning sets up the conditions for that validation to succeed, but the two activities remain distinct and should not be conflated.