Actiknow
Custom Software Development

Custom Software Database Migration: How to Change the Data Layer Without a Risky Big-Bang Cutover

Learn a phased application database migration strategy covering schema conversion, backfills, change capture, validation, cutover and rollback.

Software architect presenting a phased database migration strategy to cto and engineering leaders in a modern office
Contents hide

The real migration risk is changing the system of record while the business keeps moving

Replacing an application database sounds like an infrastructure project. In a live business system, it is a change to how orders are accepted, invoices are calculated, identities are resolved, integrations are triggered and historical facts are preserved. A database may be fully copied and still be unfit for production because the application interprets its data differently, transactions arrive during the copy or downstream consumers still depend on the old schema.

The safer objective is not simply to move rows from database A to database B. It is to move responsibility for the data through controlled, observable stages while keeping business operations correct. That usually means separating schema design, historical backfill, ongoing synchronization, read validation, write ownership and final cutover into distinct decisions. It also means acknowledging that a short, planned write pause can be safer than a poorly designed promise of zero downtime.

For CTOs, the executive question is straightforward: Can we prove that the new database produces the same business outcomes, that no committed changes are lost, and that we know exactly what to do if the new system misbehaves?

When a database migration is worth doing

Common triggers include an unsupported database version, escalating licensing costs, a cloud migration, performance limits, inconsistent schemas accumulated through years of product changes, or a move from a monolith toward clearer service boundaries. Other projects aim to consolidate tenant databases, reduce dependence on one vendor, or enable a new application architecture.

Not every trigger justifies a database-engine change. A slow report might need better indexes, query plans or read replicas. A fragile integration might need a reliable event stream rather than a new operational store. Before approving migration, compare the least disruptive alternative with the proposed target and document what the new design will materially improve.

This evaluation belongs in the same discovery process as the application itself. Actiknow’s custom software and database integration services describe web applications, APIs, data storage and automation as connected areas of work. That is the right scope for an assessment: the database cannot be designed in isolation from the business workflows it supports.

Choose the migration pattern before choosing the tools

Offline copy with a maintenance window

Stop application writes, export a consistent snapshot, transform and import the data, verify it, then redirect the application. This is often the simplest reliable approach for small datasets or systems with an acceptable maintenance window. Its limitation is the time needed for copying, indexing, validation and recovery. A rehearsal must establish the actual window, not an optimistic estimate based on raw transfer speed.

Backfill plus change data capture

Copy the historical dataset while the source continues serving production. Capture subsequent inserts, updates and deletes from a database log or another reliable change feed. Apply them to the target in a defined order, measure replication lag and perform a controlled cutover after the target catches up. This pattern is well suited to large databases with limited downtime tolerance, provided the source and target technologies support a trustworthy change stream.

The PostgreSQL logical replication documentation explains a publisher/subscriber model that starts with an initial data copy and then streams changes. Its details and limitations are product-specific; it should not be treated as a universal cross-engine migration solution.

Application dual writes

The application writes each business change to both old and new stores during a transition. This can help when database-log replication is unavailable or data must be transformed at the application boundary. It is not automatically safer: one write can succeed while the other fails, and two databases do not share an atomic transaction by default. If dual writes are required, define a primary system of record, durable retry or outbox handling, idempotent operations, reconciliation and a clear rule for partial failure.

Incremental domain migration

Move one bounded capability at a time, such as product catalog, customer preferences or reporting read models, while the core transactional system remains on the original database. A stable application API or repository layer hides the routing decision from clients. This can reduce blast radius, but shared tables, cross-domain transactions and duplicated business rules may make the first slice harder than it appears.

Software architects comparing offline migration, change data capture, dual write and incremental domain migration approaches

A practical decision rule: use offline migration when downtime is affordable; use backfill plus change capture when the data volume is large and the change stream is dependable; use dual writes only when failure handling is engineered explicitly; and use domain slices when boundaries are genuinely separable. Many projects combine more than one pattern.

Phase 1: Build a dependency and data-contract inventory

Start by listing every application service, scheduled job, report, export, integration, administrative script and third-party process that reads or writes the current database. Hidden writers are a common reason migrations fail. Include support tools and manual processes, not only the primary application.

For each dependency record: owner, environment, tables or APIs used, read/write behavior, peak traffic, transaction criticality, authentication method, required data freshness and whether the consumer can tolerate a brief interruption. Classify critical paths such as payment capture, order submission and entitlement changes separately from noncritical analytics or notifications.

Engineering lead and business analyst documenting application dependencies, database access patterns and critical business workflows

Inventory the data itself: row counts and growth, large objects, partitioning, constraints, indexes, collations, time zones, sequences, triggers, stored procedures, generated columns, foreign keys, audit records and retention rules. Look for identifiers that are meaningful only within one tenant or source system. Identify which tables contain regulated or sensitive information so that test copies are masked or otherwise appropriately controlled.

The output should be a signed dependency map and a business-invariant register. Examples of invariants are: an invoice total equals the sum of its valid lines; each settled payment is linked to exactly one recognized transaction; a tenant cannot read another tenant’s records; and an order status transition cannot move backward without an authorized reversal.

Phase 2: Design the target schema and translation rules

A cross-engine migration is not a mechanical CREATE TABLE exercise. Differences in integer ranges, decimal precision, date/time semantics, case sensitivity, JSON behavior, unique constraints and isolation levels can change results even when imported values look identical. Translate business meaning, not just column types.

Create a mapping specification for each table and field: source type, target type, nullable behavior, default, transformation, ownership, constraint, and validation method. Where a legacy column combines multiple meanings, define explicit rules for splitting it. Where several source identifiers map to one target entity, preserve a durable mapping table rather than assuming a name or email is unique.

Plan the deployment with an expand-and-contract sequence. First add new structures and compatible application code; next populate and validate them; only later switch readers and writers; finally retire obsolete structures after the observation window. Avoid a release that simultaneously removes old columns, changes API contracts and changes database engines.

Schema changes need versioning and rollback semantics. A database rollback script cannot always reconstruct data lost through a destructive transformation. Keep irreversible operations separate from the initial cutover, and retain source backups and migration mapping artifacts according to the agreed retention policy.

Phase 3: Backfill history without corrupting live changes

A reliable backfill must define a consistent starting point. Depending on the engine, that may be a transactionally consistent snapshot paired with a log position, an exported snapshot plus CDC offset, or a carefully coordinated read boundary. Without such a boundary, a row updated during the copy can be overwritten later by an older value.

Partition the workload into restartable batches, usually by stable primary key ranges or another monotonic boundary. Record batch ID, source range, snapshot or change-stream position, start/end times, rows read, rows written, rejects and checksum results. Re-running a batch should not create duplicates or roll data backward.

Data engineering team monitoring historical database backfill batches and migration progress between source and target databases

Load parent and child relationships in an order compatible with constraints, or use a documented constraint strategy that is restored and verified afterward. Large imports can overwhelm transaction logs, storage I/O or replicas; throttle based on production latency and replication health rather than maximizing raw throughput.

Never silently discard records that fail conversion. Put them in a controlled exception queue with source identifier, error reason, owning team and resolution state. An import job that reports 99.9% success may still have lost the only record required to settle a high-value customer dispute.

Phase 4: Keep changes synchronized and measure the gap

The source remains authoritative while the historical copy runs. For log-based CDC, verify that the connector captures inserts, updates, deletes and transaction ordering as required by the application. Watch log retention, replication slots, schema-change compatibility and connector restarts. A connector showing ‘running’ is not proof that all relevant tables are current.

Define lag using both a technical measure and a business measure. Technical lag might be seconds behind the latest committed source transaction. Business lag might be the age of the newest successfully replicated order, payment or account change. Both matter: a small average lag can conceal a stuck high-priority table.

Use durable checkpoints, deduplication keys and idempotent upserts. For a conceptual change record containing entity_id, version, operation and committed_at, apply a change only if its version is newer than the version already present in the target. This requires a trustworthy ordering token; timestamps alone may collide or arrive out of order. Deletes need tombstones or explicit deletion events, not an assumption that missing rows will disappear automatically.

If you must use application dual writes, put the secondary delivery behind a durable outbox or equivalent retryable mechanism when possible. Log divergence as an incident, not as a harmless warning. Never allow two active writers to update the same logical entity without a documented conflict-resolution model.

Phase 5: Validate business behavior, not just row counts

Validation needs multiple layers because matching table counts can coexist with wrong balances, missing relationships and broken access controls. Establish baselines before migration and run the same checks repeatedly as the target approaches cutover.

Qa engineer and finance analyst comparing database migration validation reports and reconciling business transactions

Six validation layers

  1. Structural: compare tables, required columns, types, constraints, indexes and permissions against the approved target specification.
  2. Completeness: compare counts by table, tenant, date and status, then investigate every material discrepancy. Account for expected transformations and archived records.
  3. Content: compare deterministic hashes or field-level samples over stable, normalized representations. Normalize time zones, numeric precision and formatting before interpreting differences.
  4. Referential: check orphaned records, duplicate natural keys, sequence collisions and parent-child integrity, including relationships enforced in application code.
  5. Business invariants: reconcile orders, balances, tax calculations, refunds, entitlements and audit trails using independently defined queries.
  6. Operational: test critical user journeys, concurrency, error handling, latency, authorization, failover, backup restoration and downstream integrations.

A shadow-read strategy can help: serve the user from the old database while asynchronously querying the target for the same request and comparing normalized results. Keep shadow reads isolated from side effects, sensitive data leakage and production latency. Differences must be triaged by category, not hidden in a single ‘match rate’ dashboard.

Illustrative acceptance criteria, to be tailored to the application: zero unexplained discrepancies in settled financial transactions; zero cross-tenant authorization failures; no unprocessed CDC events at the cutover boundary; and target p95 latency within the product’s approved service objective under representative load. Do not adopt arbitrary global percentages for critical correctness checks.

Phase 6: Rehearse the cutover as an operational procedure

A migration runbook should be executable by a team that did not write the original design. It needs named owners, exact commands, decision gates, dashboards, communication templates and time budgets. Rehearse it against a recent production-sized copy, including the failure and recovery paths.

Engineering and it operations leaders reviewing database migration cutover readiness, recovery procedures and go or no go decisions

A typical sequence is: announce the change window; confirm backups and restore tests; verify application and connector health; restrict or queue new writes at the agreed boundary; record the final source change position; drain replication; run final reconciliation; switch application connection or routing; execute smoke tests; and authorize a measured traffic ramp. Keep the old environment protected from accidental writes.

For systems that support a safe canary, begin with a small, identifiable group of users or a low-risk workload. For a tightly coupled transactional database, partial traffic may be unsafe because two populations could write incompatible states. In that case a short write freeze and single authoritative switch may be the lower-risk design.

The AWS cutover and rollback guidance explicitly distinguishes phased and all-at-once approaches and emphasizes data synchronization, testing and preplanned rollback decisions. The relevant lesson is to choose based on actual architecture and business constraints, not on a slogan about zero downtime.

Rollback is a data strategy, not a DNS switch

Before the target accepts any new authoritative writes, returning to the source may be relatively simple. After the target accepts new orders, payments or account updates, the source becomes stale. Redirecting users to it without transferring those changes can cause data loss or duplicated business actions.

Define a rollback decision point and a point of no return. Before that point, a failed validation can stop the cutover and resume source writes. After it, the team may need reverse replication, a carefully designed write journal, compensating transactions or a fix-forward plan. Reverse replication is not safe merely because the forward pipeline works; schema differences and conflicting writes must be tested in advance.

Document who can authorize rollback, what metrics trigger it, how new target writes are preserved, which integrations must be paused and how customers will be informed. A restored backup is useful only if its recovery point and recovery time meet the business requirement. Test restoration rather than assuming it.

A practical example: moving a subscription application

Consider a hypothetical subscription platform with customers, plans, invoices, payments and access entitlements. The current relational database is being replaced while the application remains live. This is an illustrative design exercise, not a client case study.

First, the team inventories all writers: checkout, payment webhooks, renewal jobs, support adjustments and an external billing export. It discovers that a nightly job updates entitlements directly in the database. That job must be moved behind an approved API or explicitly included in the migration plan.

Second, a mapping specification defines immutable customer and invoice IDs, decimal currency precision, effective dates and idempotency keys for payment events. A historical snapshot is copied, then a CDC stream catches ongoing changes. Reconciliation compares invoice line totals, settled payments and active entitlements by tenant and billing period.

Third, the application runs shadow reads for account history and entitlement checks. The team discovers that the target engine sorts case-insensitive plan codes differently, which would change uniqueness behavior. The schema and application normalization rules are corrected before cutover.

Finally, renewal jobs and payment processing are paused briefly, the final change position is drained, and a targeted set of test subscriptions is exercised. Only after the business invariants pass does the target become authoritative. The old database remains read-only during an agreed observation period; it is not presented as an instant rollback destination once new transactions begin.

How to budget and schedule the migration

Estimate effort by uncertainty and dependencies, not only database size. A terabyte of clean, append-only data may be easier to move than a small operational database with stored procedures, undocumented writers and financial rules. Budget for discovery, schema mapping, application compatibility, test environments, migration tooling, rehearsal, security review, cutover staffing and post-cutover support.

An indicative six-stage delivery plan

Stage 1, discovery and acceptance criteria: map dependencies, critical workflows, data classes, RTO/RPO expectations and ownership. Produce a migration decision record.

Stage 2, target design and proof of concept: validate data types, transactions, representative queries and the proposed replication method using real-shaped but appropriately protected data.

Stage 3, pipeline construction: build restartable backfill, CDC or outbox, exception handling, metrics and schema deployment automation.

Stage 4, parallel validation: reconcile datasets and business invariants, run shadow reads and conduct performance and security tests.

Stage 5, rehearsal and cutover: perform at least one full dress rehearsal, finalize the runbook and execute the approved change window.

Stage 6, stabilization and retirement: monitor correctness and latency, close exceptions, test backups, remove temporary dual-path complexity and decommission the old store only after formal sign-off.

These stages are sequencing guidance, not a promised six-week timeline. Actual duration depends on system complexity, access to source owners, data volume, change rate and the acceptable outage window. An application with many unowned integrations can spend more time on discovery than on copying data.

What executives should see on the migration dashboard

A useful steering dashboard should answer five questions:

  • What remains unmapped?
  • How complete is the historical copy?
  • How far behind are live changes?
  • Which business invariants fail?
  • Are we ready to switch and recover?

Track dependency coverage, schema mapping sign-off, backfill completion by entity, reject counts, CDC lag by critical table, reconciliation failures by severity, shadow-read differences, p95 latency, restore-test results and cutover rehearsal duration. Display the last successful validation timestamp. A green overall status must not override an unresolved critical discrepancy.

Assign a single accountable owner for go/no-go, with named owners for application, database, security, operations and business acceptance. Establish an escalation threshold for missing transactions or unauthorized access that immediately blocks cutover.

Common mistakes that turn a migration into an outage

Copying rows without migrating behavior: triggers, stored procedures, defaults and isolation semantics may carry business rules that were never documented.

Using timestamps as the only change cursor: equal timestamps, clock skew and delayed commits can cause missed or reordered updates.

Treating dual writes as atomic: independent databases can diverge unless failures are durably captured and repaired.

Testing only aggregate counts: totals can match while individual customers, permissions or financial relationships are wrong.

Ignoring background jobs: an unnoticed scheduler may continue writing to the old database after cutover.

Skipping restore drills: backups and rollback documents are not proof of recoverability.

Retiring the source too early: the old store may be needed for audit comparison, incident investigation or controlled recovery during stabilization.

Overpromising zero downtime: a bounded maintenance window with explicit controls can be preferable to a complex active-active design that nobody can validate.

Decision checklist for the CTO

Approve cutover only when the dependency inventory is complete enough to account for all critical writers; schema differences are resolved; the historical load is reproducible; live changes are captured without unexplained gaps; business invariants reconcile; representative workloads meet approved performance objectives; security controls pass; backup restoration has been demonstrated; and the rollback or fix-forward decision is operationally credible.

If any of these conditions are unknown, the next milestone is not cutover. It is evidence collection. The migration is successful when the application continues to produce trustworthy business outcomes, not when a connection string changes.

Frequently asked questions

Can a database be migrated with zero downtime?

Sometimes, but it depends on engine capabilities, transaction boundaries, write volume and application architecture. A near-zero-downtime design usually requires a consistent snapshot, ongoing change capture and a tightly controlled authority switch. It is not a universal guarantee.

What is the difference between backfill and CDC?

Backfill copies historical records. Change data capture tracks subsequent inserts, updates and deletes so the target can remain current while the original system operates. They solve different parts of the migration problem and often work together.

Should we use dual writes during migration?

Only when there is a clear failure-handling design. A direct write to two databases can partially succeed. Durable outbox delivery, idempotency, reconciliation and an explicit primary system of record are essential safeguards.

How do we decide between a big-bang and phased cutover?

Use business downtime tolerance, data coupling, transaction integrity and recovery complexity. A phased approach is attractive for separable domains; a single controlled switch may be safer for tightly coupled transactional state.

What should be reconciled besides row counts?

Compare business identifiers, relationships, monetary balances, status transitions, audit history, access boundaries and representative user journeys. Row counts are a starting signal, not proof of correctness.

Can we roll back after users start writing to the new database?

Not by merely switching the connection back. The old database will be missing new writes unless a tested reverse-sync or compensation strategy preserves them. Define the point of no return before cutover.

How should we handle failed records in the backfill?

Capture them with stable source identifiers, reasons and owners in an exception workflow. Repair and replay them deterministically. Do not hide failed rows inside a percentage-success metric.

Do we need to migrate stored procedures and triggers?

You need to preserve the business behavior they implement, but not necessarily the same implementation. Some logic may move into application services. Each change requires explicit regression tests.

What is a safe first migration slice?

Choose a bounded capability with a clear owner, limited shared transactions and measurable acceptance criteria. A read-only reference dataset or isolated workflow is often a better pilot than payment settlement.

How do we know CDC has caught up?

Use a reliable source change position, connector-applied position, lag metrics and a final reconciliation at a controlled write boundary. A dashboard showing zero seconds of lag alone is insufficient.

How long should the old database be retained?

Set the period according to audit, contractual, security, recovery and operational requirements. Keep it protected from writes when appropriate and decommission only after formal acceptance and retention review.

What should an external migration partner deliver?

Expect a dependency map, schema mappings, tested backfill and sync mechanisms, reconciliation evidence, a rehearsed cutover runbook, recovery procedures, monitoring and post-cutover handover, not merely a copied database.

Where Actiknow can help

A migration assessment should begin with the application, its integrations and the decisions its data supports. Actiknow’s custom web application development approach includes migration, integration, testing and ongoing support; its cloud application development and migration services cover cloud and hybrid application work. Those capabilities are relevant when the target data layer changes alongside application infrastructure or API contracts.

If you are planning a live-system database change, contact Actiknow to discuss a migration assessment. A useful first conversation is about current dependencies, required downtime, validation criteria and recovery options, before committing to a platform or a cutover date.

Editorial note: The example is hypothetical. The article does not claim a specific customer migration, zero-downtime outcome or measured cost reduction.