Cloud Migration Strategy and Execution Questions

Direct answer: For an OLTP (online transaction processing) workload migrating via AWS DMS (Database Migration Service), the configuration choices that matter most are full-load-vs-change-data-capture (CDC) mode selection, correct LOB (large object) handling, deliberate table mapping (not defaults), tuned parallelism, scoped IAM permissions, and monitoring specifically for replication latency and task health, since a misconfiguration in any of these can silently produce an incomplete or lagging target rather than an obvious failure.

Structured elaboration. Full load vs CDC: "full load" alone copies a point-in-time snapshot with no ongoing sync (fine only if downtime for the copy duration is acceptable); "full load + CDC" does the initial snapshot AND then continuously replicates ongoing changes (the standard choice for a near-zero-downtime OLTP migration); "CDC only" (no initial full load) is used when the target already has the historical data via another means and only ongoing changes need replicating from a defined starting point. LOB handling: DMS has different strategies for large object columns (full LOB mode transfers LOBs completely but is slower; limited LOB mode caps LOB size and truncates anything larger, which is FAST but can silently truncate data if the configured limit is set too low for the actual data); for an OLTP workload with LOB columns, this needs deliberate sizing based on actual observed LOB sizes in the source, not a default guess. Table mapping: explicit include/exclude rules and any needed transformation rules (renaming, filtering) should be deliberately configured rather than relying on a broad "migrate everything" default, which risks pulling in tables that shouldn't be in scope or missing transformation needs for heterogeneous target schemas. Tuning for throughput (workers/parallelism): DMS parallelism settings (number of tables loaded concurrently, parallel load threads per table for large tables) need tuning against both the source database's capacity to handle concurrent read load and the target's capacity to absorb concurrent writes, since over-aggressive parallelism can overload either side. IAM permissions: DMS needs specific source and target permissions (read access with sufficient privilege to read change data on the source, write access on the target); scope these to exactly what's needed rather than granting broad database-admin-level access to the DMS service role. Monitoring metrics indicating replication health/latency: DMS exposes metrics like CDC latency (source and target), throughput, and task status; specifically watch for CDC latency trending upward (an early warning that the replication task can't keep pace with source write volume) rather than only checking for outright task failures, since a slowly-falling-behind task is a more common and more silently dangerous failure mode than a hard crash.

Worked example. For an OLTP database with a moderate-size LOB column (e.g., document attachments averaging a few MB but occasionally larger): configure full LOB mode if data integrity for those larger attachments matters (accepting slower throughput) rather than limited LOB mode with a size cap set from a guess; explicitly map and validate the table list rather than trusting a default "migrate the whole schema" setting, especially if the target uses a different schema organization; monitor CDC latency continuously through the replication period, treating a sustained upward trend as an actionable signal to investigate (source write-volume spike, target capacity constraint) well before it becomes a blocking issue at cutover time.

Trade-offs & pitfalls. Using limited LOB mode with a default or guessed size cap is a common way this kind of migration silently truncates data: the migration APPEARS to succeed (no errors, task shows complete) while some large-object values are quietly cut off, which is only discovered later when someone notices a corrupted or incomplete document, well after the DMS task reported success.

Direct answer: For legacy databases using features unsupported on managed cloud platforms (proprietary stored-procedure dialects, specific extensions, non-standard indexing), the three real options are modernize (rewrite the dependent logic to standard SQL or application code), emulate (find a compatibility layer or feature-equivalent on the target), or isolate (keep the unsupported piece on a self-managed instance while migrating everything else), and the right choice depends on how much logic is entangled with the unsupported feature.

Structured elaboration. Modernize: rewrite the stored procedures/triggers/extensions in standard SQL or move the logic into the application layer; highest upfront cost, but pays off in maintainability and full access to the managed service's benefits (automatic patching, scaling, no more vendor-specific expertise required to operate it). Best when the unsupported logic is a small, well-understood surface area (a handful of stored procedures with clear business logic) rather than deeply pervasive. Emulate: use a compatibility feature on the target (some managed database services offer partial compatibility modes, or a proxy/shim layer that translates the unsupported calls) or run the workload on a self-managed VM-based database in the cloud that DOES support the feature, gaining infrastructure-migration benefits (off the physical data center) without the managed-service constraint; moderate cost, faster than a full rewrite, but doesn't get the operational benefits of a fully-managed service and may carry its own compatibility risk if the emulation layer has edge-case gaps. Isolate: keep the specific unsupported component on a self-managed or even on-prem/colocated instance while migrating everything else; lowest immediate migration risk for that piece, but defers the modernization work indefinitely and adds a permanent hybrid-architecture operational burden (a special case the team has to remember exists).

Trade-offs for maintainability and operational overhead. Modernize reduces long-term operational overhead the most (fully managed, no special-cased infrastructure) but has the highest short-term project risk (a rewrite of business logic nobody may fully understand anymore is where subtle bugs hide). Emulate is a middle ground: moderate overhead reduction, moderate risk. Isolate has the LOWEST short-term risk but the HIGHEST long-term overhead, since it leaves a permanent exception in an otherwise-modernized environment, and "we'll modernize it later" isolated components have a strong tendency to never actually get revisited.

Worked example. A legacy application depends on a database engine's proprietary full-text-search extension for one reporting feature, with the rest of the schema being standard SQL. Isolate would mean keeping the whole database on a self-managed instance just for that one feature, which is disproportionate. The pragmatic path: migrate the core database to the managed service (modernize away from the vendor-specific extension) and replace the full-text-search feature specifically with the cloud provider's native search service or the managed database's own full-text capability if it exists, isolating ONLY the smallest possible surface area rather than the whole database.

Trade-offs & pitfalls. The common mistake is choosing Isolate for convenience (it feels like the safest, fastest path) without setting an actual date or trigger to revisit modernizing it; without that, isolation becomes permanent architecture by default, and the org ends up operating a special-cased legacy instance indefinitely alongside an otherwise-modern estate.

Direct answer: For a globally-sharded relational database with cross-shard transactions, migrate shard-by-shard using per-shard replication while preserving the existing sharding/consensus scheme (don't try to also change the sharding topology in the same migration), and gate cutover of cross-shard-transaction-bearing shards on demonstrating the distributed transaction protocol behaves identically end-to-end on the new infrastructure.

Structured elaboration. Replication strategy: replicate each shard independently using the same technique used for a single-region database migration (change-data-capture (CDC) / logical replication per shard), but coordinate the CUTOVER across shards rather than each shard's replication setup, since replication itself can run shard-by-shard on independent timelines. Transactional guarantees to preserve: if the system relies on a 2-phase-commit (a coordinator asks every participating shard to confirm it CAN commit -- the 'prepare' phase -- before telling all of them to actually commit, so a cross-shard transfer can't be double-counted or half-applied) or a distributed consensus protocol (e.g., Paxos/Raft-based coordination -- different algorithms solving the same underlying problem, getting a set of nodes to agree on one value or ordering, even if some of them fail) for cross-shard transactions and global ordering, that coordination layer needs to keep working correctly THROUGHOUT the migration, including during any period where some shards have cut over and others haven't; the safest approach is to migrate all shards that participate in any given cross-shard transaction TOGETHER in one wave, rather than spreading cross-shard-transaction partners across different migration waves, which would require the coordination protocol to span old and new infrastructure simultaneously: the prepare-phase confirmation from a shard on the new infrastructure would need to reliably reach a coordinator that may still be running against the old infrastructure (or vice versa), and any confirmation that gets lost or delayed across that boundary leaves the transaction stuck half-committed -- a much harder and riskier problem than keeping the whole protocol on one side of the migration at a time. Conflict resolution: during the replication (pre-cutover) period, no conflict resolution is needed since the new shards are read-only replicas; the risk window is narrow (the final cutover moment), so the main protection is pausing writes across ALL shards being cut over simultaneously, not resolving conflicts after the fact. Cutover approach to avoid data loss/inconsistency: pause writes globally (or at minimum, across every shard involved in any cross-shard transaction being cut over together) briefly, drain replication lag on every shard in the wave, verify parity per shard AND verify no cross-shard transaction was left in a partially-committed state at the pause moment, then repoint and resume.

Worked example. A globally-sharded ledger system with 4 shards, where transactions between users on shard A and shard B require 2-phase commit: migrate shard A and shard B TOGETHER in the same cutover wave (never split transaction partners across waves), even if shards C and D (which don't participate in A-B transactions) migrate independently on their own schedule. During the A+B cutover: pause new cross-shard transactions specifically involving A or B, let any in-flight 2PC transactions either commit or abort cleanly (never leave one in the prepare phase across the pause), drain replication, verify, then resume.

Trade-offs & pitfalls. The single biggest risk is treating shards as independently migratable units when cross-shard transactions exist: migrating shard A this week and shard B next month looks parallelizable and schedule-friendly, but leaves the distributed transaction coordinator spanning old and new infrastructure for the gap, which is a much harder failure mode to reason about than a same-wave joint cutover.

Direct answer: For a database with strict referential integrity and compliance requirements, change-data-capture (CDC) with a controlled cutover is generally the strongest default: it minimizes downtime while preserving transactional order, and unlike dual-write, it doesn't require the application itself to coordinate two simultaneous writers.

Structured elaboration

Homogeneous replication (native database replication, e.g., MySQL replication or PostgreSQL streaming replication to a cloud target): strongest integrity guarantees since it's the database engine's own replication mechanism, but only works when source and target are the same engine, and typically requires network connectivity and version compatibility between on-prem and cloud that can be operationally awkward to set up securely.

Dual-write with reconciliation (application writes to both old and new databases simultaneously during a transition period): gives the most control over cutover timing (no replication lag to drain since both are being written in real time), but is the riskiest for referential integrity: if one write succeeds and the other fails, the two databases diverge, and reconciling that divergence for a system with strict FK constraints and compliance requirements (where an inconsistency isn't just a data-quality issue but a potential audit finding) is genuinely hard to get right; this approach should generally be avoided for exactly this workload profile unless there's no alternative.

Change-data-capture (CDC) with cutover: reads the source database's transaction log and replays changes to the target, preserving transaction order (critical for maintaining referential integrity across related tables) without requiring the application to write twice; the cutover is a short window to drain the final replication lag, stop writes to the source, verify parity, and repoint the application. This is the strongest option for this workload: it preserves ordering and integrity guarantees close to the database's own consistency model, without dual-write's coordination risk.

Offline migration with an application freeze: simplest and safest for integrity (nothing is changing during the migration, so there's no divergence risk at all), but the downtime is the migration duration itself, which is often unacceptable for a system with real-time compliance obligations (audit logging, transaction processing) that can't simply pause.

Justifying the recommendation. CDC with cutover: preserves integrity via ordered replay (foreign-key-related changes replay in the same order they were committed, so referential integrity is never violated mid-replication); handles FKs correctly because it's replaying actual committed transactions, not independently-computed writes that could race; minimizes downtime to the final lag-drain window (typically seconds to low minutes for a well-tuned CDC pipeline); provides auditability by design, since the CDC pipeline itself is a log of exactly what changed and when, which compliance teams can review as evidence the migration didn't silently alter data.

Worked example. A payments database migration: enable CDC (e.g., log-based replication reading the database's write-ahead log), let it run for days while validating parity via periodic checksum comparison on a sample of tables, then during the cutover window: pause new writes to the source (typically via an application-level flag or a brief maintenance mode), wait for replication lag to hit zero (confirmed via the CDC tool's lag metric), do a final full-table checksum comparison, then repoint the application's connection string. Total application-facing downtime: the pause-to-repoint window, typically single-digit minutes even for a large database, versus hours for an offline freeze-and-migrate approach.

Trade-offs & pitfalls. Dual-write's appeal (no CDC infrastructure needed, feels simpler to reason about) is a trap for exactly this workload profile: the coordination failure mode (one write succeeds, the other doesn't) is precisely what strict referential integrity and compliance requirements can't tolerate, and "reconcile it after the fact" isn't a real answer when the reconciliation itself has to be auditable.

Direct answer: For a data warehouse migration between cloud providers where reported revenue calculations must stay correct, treat the parity validation of financial aggregates as the primary gate for cutover, not a secondary check: run both warehouses in parallel long enough to validate revenue numbers match across multiple full reporting cycles before switching the source of truth.

Structured elaboration. Data replication (change-data-capture (CDC) vs bulk): for an ongoing warehouse migration with continuous data loads, CDC-style continuous replication (or, if the source's ETL naturally runs in daily/hourly batches already, simply dual-loading each batch into both warehouses) is preferable to a one-time bulk copy, since it keeps the target warehouse continuously current and lets parity be validated repeatedly rather than once. Parallel runs: run both warehouses receiving the same data for a period spanning AT LEAST one full revenue-reporting cycle (e.g., a full month-end close if that's when revenue gets finalized/reported), since bugs in a migration frequently only surface at the specific aggregation boundaries (month-end, quarter-end) that don't occur in a shorter validation window. Validation tests: row counts (a first, cheap sanity check), checksums (catch data corruption/truncation), and METRIC RECONCILIATION specifically (recompute the actual reported revenue figures independently on both warehouses and diff them; this is the requirement the question explicitly calls out, and it's a stronger check than row counts or checksums alone, since it catches type-conversion, rounding, timezone-boundary, and join-logic discrepancies that could leave row counts intact while shifting the aggregate). Rollback strategy: keep the OLD warehouse (Provider A) as the reporting source of truth until Provider B has passed metric reconciliation across at least one full cycle; rollback is then simply "don't cut reporting over yet," which is low-risk precisely because the parallel-run period means nothing has actually been decommissioned. Stakeholder communication during cutover: finance/reporting stakeholders should be informed of the exact metric-reconciliation results (not just "migration complete") before the cutover, since they're the ones who'll be asked to defend the numbers if something's wrong post-cutover, and should sign off on the parity evidence, not just be notified after the fact. Target-specific considerations: if the destination is a platform like BigQuery, the migration is also a chance to redesign for its architecture rather than transplant the old layout verbatim (choose partitioning and clustering columns based on the reporting queries' actual filter/group-by patterns, and accept some denormalization where BigQuery's columnar, distributed-join model rewards it); any stored procedures encoding business logic in the old warehouse need an explicit replacement plan, since most cloud warehouses don't support the old engine's procedural SQL dialect directly, so that logic typically moves into the ELT/transformation layer (dbt-style modeled queries) rather than porting 1:1. If the source feed is a nightly batch rather than a continuously-updating pipeline, the same reconciliation discipline still applies, just on the batch's own cadence: dual-load each night's batch into both warehouses and reconcile the two before advancing, rather than waiting for a large backlog to accumulate.

Worked example. A SaaS company migrating its warehouse ahead of a fiscal-year-end: run parallel loads for 6-8 weeks spanning at least one full month-end close, reconcile revenue-recognition aggregates (MRR -- Monthly Recurring Revenue -- and recognized revenue by cohort) daily during that window, and require the finance team's explicit sign-off on a side-by-side comparison report before cutting the BI/reporting layer over to the new warehouse; only then decommission the old one, after a further short bake period.

Trade-offs & pitfalls. Cutting over based on a validation window that DOESN'T span a full reporting cycle (e.g., validating for two weeks when revenue is recognized/reported monthly) is the most likely way this kind of migration ships a silent revenue-reporting bug: the discrepancy only shows up at month-end, by which point the old warehouse may already be decommissioned.