How a Veteran DBA Tackles Financial Multi‑Database and Big Data Architecture

Digital transformation in the financial industry now runs core transaction systems, data‑mid‑platforms, and offline‑real‑time big‑data platforms in parallel, often requiring simultaneous operation of SQLServer, Oracle, MySQL, PostgreSQL, and DB2 clusters under strict high‑availability, data‑consistency, regulatory, and disaster‑recovery constraints.

The interview is with Yao Wei, a senior DBA with more than ten years of experience across retail, internet, and finance, who has built end‑to‑end data platforms, solved massive BI bottlenecks, and now focuses on financial database evolution and domestic‑database migration.

Pre‑emptive DBA Involvement

Business‑requirement review: assess transaction concurrency, tidal traffic, data‑retention periods, and compliance needs. Finance locks ACID, 99.999% HA, and six‑month log retention; retail forecasts promotion peaks to avoid later re‑sharding.

Schema and storage design: validate sharding keys, primary keys, indexes, hot/cold data layers, field precision, charset, and transaction granularity to prevent skew, long transactions, time‑precision mismatches, and charset conflicts that only surface during post‑deployment reconciliation.

Middleware & data‑sync design: early CDC, Flink, dblink, and replication planning. For closed‑source Oracle/SQLServer, evaluate log‑reading and breakpoint‑resume mechanisms to eliminate data‑loss risks during replica switches.

High‑availability & disaster‑recovery review: design two‑site three‑center, multi‑active, and failover‑time strategies. Retail/Internet adopt master‑slave or cluster auto‑switch based on business‑tolerated interruption thresholds.

Performance‑testing plan: DBA leads load‑test model, simulating peak tidal flow to expose slow SQL, lock conflicts, 2PC timeouts, and checkpoint failures before production.

Conclusion: DBAs must move beyond post‑incident firefighting to become data‑side reviewers throughout the development lifecycle, embedding database constraints, sync risks, storage costs, and compliance requirements into every stage.

Industry‑Specific Pain Points

Retail (Bailian Life)

Massive traffic spikes during promotions cause real‑time inventory and order queries to overwhelm a single BI server.

Multiple DB clusters, dblink, and replication create a single‑point‑of‑failure; scalability is poor.

Historical transaction data leads to multi‑hour report queries; real‑time dashboards suffer severe latency.

Rapid custom‑report iteration cannot be supported by the traditional architecture.

General Internet

24/7 user‑behavior, transaction, and marketing data require both real‑time serving and offline analysis, but batch (MaxCompute) and streaming (real‑time warehouse) stacks are split, causing data‑consistency gaps.

Flink task state balloons and frequent checkpoint failures degrade stability.

Heterogeneous sources (MySQL, ES, Redis) lack a unified ingest platform.

Elastic scaling is difficult; peak resources are insufficient while valleys waste capacity, raising storage costs.

Financial Core

Commercial DB licenses cost millions, are subject to overseas control, and pose supply‑cut and data‑security risks.

Five hard constraints—concurrency, latency, storage, transaction consistency, and regulatory compliance—often conflict, leading to cross‑DB coupling and shard‑skew failures.

Domestic (信创) databases have weaker multi‑active DR capabilities.

Strict change‑approval processes cause lock‑blocking and sync‑delay risks.

Massive transaction detail streams cause Flink state bloat, 2PC timeouts, and cross‑DB reconciliation failures.

Deep Technical Incompatibilities

Kernel & Transaction Model

Oracle/DB2 use MVCC with rollback segments, guaranteeing no dirty reads.

MySQL InnoDB isolation differs; SQLServer snapshot isolation relies on a version store.

Mixed cross‑DB transactions can have divergent commit logic, leading to hidden reconciliation gaps that may only be discovered months after go‑live.

Lock Mechanism Differences

Oracle row‑level locks never escalate, while SQLServer may upgrade row locks to table locks; PostgreSQL shared‑lock granularity differs. The same batch update can block the entire business on SQLServer (table lock) but only rows on Oracle, a problem that only appears under real peak load.

Log System Incompatibility (CDC Pitfalls)

Oracle redo archiving is independent; LogMiner‑based module handles multi‑node RAC log switching and provides backup‑based recovery.

SQLServer CDC depends on transaction logs; replica switches can truncate logs, causing data loss.

MySQL binlog format and filter rules cannot be directly reused for Oracle redo, leading to contradictory filter logic in a unified ingest platform.

DB2 log‑archiving cycles are special; breakpoints cannot be reused, making resume unreliable.

Field & Syntax Compatibility

Time‑precision mismatches (SQLServer datetime, Oracle timestamp, MySQL datetime) cause reconciliation timestamp failures. Charset differences (GBK, UTF‑8, AL32UTF8) produce garbled Chinese characters across DBs. Stored‑procedure and function syntax are completely incompatible, causing immediate errors during migration or sync and requiring extensive testing.

Reliability Mechanisms for SQLServer AlwaysOn CDC

Multi‑layer intelligent retry: replica‑switch detection via timed polling, graded retry on primary‑replica status change, 100 ms short‑break retries, and longer‑window fallback to archived logs to avoid abrupt sync termination.

Precise checkpoint persistence: three‑tuple checkpoint (LSN + transaction timestamp + replica ID) stored in a distributed KV store, surviving service restarts.

Three‑tier log fallback: (1) extend transaction‑log truncation until CDC consumption completes; (2) local cache of logs for read‑after‑crash fallback; (3) offline archive pool for replay, with daily CDC row‑count reconciliation that auto‑triggers back‑fill.

Architecture Governance and Risk‑Left‑Shift

The five core stages (requirement, schema, middleware, HA/DR, performance testing) are embedded into the architecture review gate to shift risk left.

A metadata lineage platform builds a cross‑system table dependency graph, automatically identifying cross‑DB joins and flagging high‑risk distributed transactions.

Unified sharding key (customer/account ID) ensures all data for a single customer resides in one shard; automatic detection of transactions spanning more than three shards forces redesign.

Five‑constraint weighted scoring per business type selects appropriate storage engines: strong‑consistency core accounting uses distributed commercial DBs (OceanBase/TiDB); high‑volume credit archives use low‑cost distributed storage; real‑time payment uses low‑latency engines.

Automated validation pipelines include:

Distributed‑transaction auto‑validation: scans all SQL and stored procedures, simulates network latency, auto‑stress 2PC timeouts, and flags missing fallback mechanisms.

Shard‑balance auto‑checker: simulates historical load, computes shard size and access frequency, suggests hot/cold split, warns on skew beyond thresholds, and validates post‑scale‑out redistribution.

Sync‑rule unified validator: compares CDC filter rules upstream/downstream, simulates full data sync, generates reconciliation samples, and catches precision, charset, or numeric drift issues.

Full‑scale replay reconciliation pipeline in dev: automatically syncs full synthetic data, runs end‑to‑end cross‑DB reconciliation, and blocks release if mismatches are found.

Static schema scan on commit: enforces field precision, charset, primary‑key, and index standards; alerts on cross‑DB type mismatches; validates stored‑procedure syntax across Oracle/SQLServer/MySQL.

Standard field template: enforces type, charset, and time‑precision templates for new tables, preventing custom non‑standard fields.

Cross‑Stack Monitoring and Incident Handling

A unified observability platform propagates a global traceId through DB transactions, binlog/CDC, Flink operators, and Spark tasks, enabling reverse tracing from compute latency back to upstream DB slow‑SQL or long‑transaction.

The dashboard aggregates DB metrics (slow SQL, transaction time, lock wait, sync delay), middleware metrics (CDC read rate), and compute metrics (Flink checkpoint duration, Spark runtime) on a single screen.

Cross‑stack root‑cause alerts: when Flink latency spikes, the system automatically fetches concurrent DB long‑transactions, batch updates, and lock‑wait records, attaches them to the alert, and labels the issue as either “DB source” or “compute resource”.

Unified ticket flow: alerts auto‑generate work orders, route DB issues to the DBA team and compute issues to the big‑data team, and include metric evidence to eliminate blame‑shifting.

Data Lifecycle Management and Archiving

Online‑offline tiered storage separates hot (≤12 months) data in high‑performance DB, warm (12‑36 months) data in low‑cost distributed storage, and cold (>36 months) data in read‑only archive.

Automatic partition migration moves expired partitions without manual table drops, preserving online query availability.

A unified query gateway routes recent queries to the online DB and historical queries to the offline warehouse transparently, requiring no code changes.

Compliance archiving encrypts stored data and retains operation logs for at least six months to satisfy the Data Security Law; the archive supports back‑trace and maintains dual copies during transition.

Signal‑to‑Noise Migration to Domestic “信创” Databases

Risk points per migration phase are identified and mitigated:

Assessment: only data volume is often measured, missing peak concurrency, complex stored procedures, and distributed‑transaction load. Mitigation: full‑peak load simulation, procedure/transaction inventory, and capability‑match scoring.

Refactor: syntax and transaction incompatibilities. Mitigation: automated syntax scanner, full‑stack replay verification after code changes.

Testing: insufficient data volume and concurrency, lacking 30‑second DR and millisecond latency verification. Mitigation: production‑size clone, tidal‑peak simulation, full‑flow reconciliation.

Cut‑over: dual‑write sync lag, data inconsistency, and cut‑over downtime. Mitigation: staged gray‑rollout (edge → general core → critical core), real‑time dual‑write reconciliation, pre‑cut‑over rehearsal.

Rollback: missing reverse sync, long rollback time violating continuity. Mitigation: retain bidirectional CDC sync throughout migration and include rollback time in acceptance criteria.

Enhancing HA for Domestic Databases

Four‑layer补齐 strategy:

Architecture layer: add same‑city real‑time backup cluster and remote asynchronous DR, plus an independent arbitration node to avoid split‑brain scenarios.

Two‑site‑three‑center layer: custom failover scheduler detects failures, auto‑routes traffic, and guarantees ≤30 s recovery.

Data‑consistency layer: multi‑layer cross‑center reconciliation compensates for asynchronous replication offsets, ensuring eventual consistency.

Ecosystem layer: provide monitoring, log archiving, backup‑restore tools tailored for domestic DBs; automate DR drills (power‑off, shard failure) to expose gaps early.

Business‑layer DR tiering matches risk to cost: core accounting uses same‑city active‑active strong sync; credit/wealth uses asynchronous remote DR; background/reporting avoids direct core DB access.

Lightweight Operational Platform for Fragmented Legacy Systems

A three‑step rollout introduces unified operations without massive system rewrites:

Standardize: an API‑gateway adds lightweight REST adapters for legacy systems, exposing metrics to monitoring, change‑management, reconciliation, and ticketing tools while leaving core systems untouched.

Lightweight ops portal: a B/S UI aggregates data, displays dashboards, and manages work‑order flow; it calls existing change‑system APIs and pulls metrics from the gateway.

Rule engine: configure cross‑system logic—monitoring alerts auto‑create DBA tickets, change‑release pre‑checks fetch real‑time metrics, reconciliation failures trigger sync verification—while preserving original system capabilities.

Incremental integration (monitoring → ticketing → change control) ensures zero disruption to existing processes, meeting the financial sector’s stringent stability requirements.