Katkat
Back

Migrate a Continuously Used Database

HadrianSoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

This question tests your ability to design a zero-downtime database migration strategy that handles heterogeneous database engines while maintaining strict data consistency across concurrent read and write operations.

Problem Statement

You are tasked with migrating a core production database from MS SQL Server to PostgreSQL. The current architecture features two primary clients:

  1. An internal service that performs continuous writes (transactions).
  2. A third-party, read-only analytics service that cannot be easily updated or modified to support multi-database querying.

Your migration plan must ensure zero downtime, maintain strict data consistency, and provide a rollback strategy. The total dataset size is approximately 2TB, with high-frequency write operations during business hours.

Requirements

Functional Requirements

  • Keep both database instances synchronized during the migration phase.
  • Support the internal write client without modifying its core business logic extensively.
  • Ensure the read-only third-party service eventually points to the new PostgreSQL instance without the service provider making code changes.

Non-Functional Requirements

  • Minimize data discrepancy (replication lag) between the source and target.
  • Implement a verification mechanism to compare data integrity between SQL Server and PostgreSQL.
  • Design a clear cutover mechanism with minimal impact on service availability.

Expected Deliverables

  1. A high-level architectural diagram or description of the migration path (e.g., CDC, dual-writes, or logical replication).
  2. A data mapping and transformation strategy to handle differences in data types and stored procedures.
  3. A step-by-step cutover procedure and a rollback strategy if the PostgreSQL instance fails post-cutover.

Hints

Hint 1

Consider using Change Data Capture (CDC) tools like Debezium to stream transactions from SQL Server to PostgreSQL in real-time.

Hint 2

Evaluate the feasibility of a database proxy or a translation layer to deceive the read-only service into thinking it is still connected to the original SQL Server instance.

Hint 3

Address the verification gap by implementing a background reconciliation job that samples random rows from both databases to confirm parity.

Follow-up questions

  • How would you handle schema migrations if the internal write client requires a schema change that is incompatible with the legacy SQL Server database?
  • What specific metrics would you monitor during the synchronization phase to determine if you are ready for cutover?
  • If the third-party service caches its database connections, how would you force a switchover without restarting that service?