OLAP MPP Distributed Relational Database Dual-Active Disaster Recovery System Design

As the '14th Five-Year Plan for Big Data Industry Development' was released, big data has increasingly become a vital pillar of the digital economy. Building comprehensive disaster recovery and dual-active systems for OLAP MPP clusters—which host data warehouses and handle high-value, high-density big data storage and analysis—has become critically important.

OLAP MPP cluster disaster recovery systems can meet the DR requirements for non-real-time batch processing scenarios (such as T+1 and T+0.X), but RPO and RTO tend to be large, often reaching the order of hours. As data warehouse operations demand ever-higher continuity—especially in real-time data warehouse scenarios—it is necessary to build real-time dual-active systems to ensure operational continuity. When the primary database fails, the standby database must take over immediately. For real-time data warehouses, establishing near-real-time or real-time dual-active systems that achieve RPO and RTO at the minute level, second level, or even zero is essential to guarantee zero data loss and data consistency between primary and standby, thereby ensuring business continuity.

The disaster recovery and dual-active configurations for OLAP MPP analytical clusters differ significantly from those of OLTP transactional databases used in online production systems—both in technical implementation and in DR tier requirements. For instance, transactional databases have complete WAL (Write-Ahead Logging) transaction logs, enabling database backup, dual-active replication, and remote disaster recovery via transaction logs. To pursue high throughput performance, OLAP MPP analytical databases cannot fully adopt a DR and dual-active design based on transaction logs. Currently, the mainstream disaster recovery and dual-active approaches in the industry include: data synchronization mode, ETL mode, and dual-active mode, as described below:

1. Data Synchronization Methods

Data synchronization between primary and standby clusters is the foundation of a dual-active disaster recovery system. Efficient, stable, and consistent synchronization is key to its reliable operation. This section lists common data synchronization methods and their respective advantages and disadvantages.

1.1. Synchronization Based on Transaction Logs and Data Blocks

Advantage: Directly synchronizes incremental changed data; suitable only for data warehouses with small data changes.

Disadvantage: Invasive to the primary database; synchronization timeliness drops sharply when the primary is busy. Not suitable for scenarios with large data changes.

1.2. Synchronization Based on Backup and Restore

Uses backup and restore tools to perform full and incremental backups, which are stored on some storage medium and then restored to the standby database.

Advantage: Utilizes existing product backup and restore tools.

Disadvantage: Requires the database to support incremental backup capabilities, often with severe lock waiting. The standby database can only provide read access. During recovery, the standby cannot immediately provide services. Backup and restore cannot achieve the same RPO as streaming synchronization methods, making RPO=0 impossible. RTO is longer, and a large amount of space is needed to store backup data.

1.3. Synchronization Based on Export and Import

Based on exporting from the primary and importing to the standby at the upper application level. The business logic must identify incremental data, leading to high invasiveness on business design and scheduling. Taking a job as a unit, the application must remember the related tables generated by the job. After the job completes, the primary initiates the export of those tables, and the standby performs the import.

Advantage: Allows table-level data synchronization. Since the exported data is in text files, the primary and standby can be heterogeneous—for example, primary is Product A and standby is Product B.

Disadvantage: Highly invasive to the primary's application scheduling and database design, making implementation difficult.

2. Dual-Active Disaster Recovery Modes

This section introduces some dual-active disaster recovery implementation approaches—some are product capabilities, others are application solutions—for your reference.

2.1. ETL-Based Dual-Processing Dual-Active Mode

Uses two independent scheduling systems to process jobs, with control handled by upper-layer applications.

Advantage: Does not depend on the database product's own disaster recovery capabilities; the application controls the process independently. Typically, the primary continues to provide services, and only after near-real-time or post-batch verification confirms consistency between primary and standby is the standby opened for external service.

Disadvantage: Since both primary and standby run ETL jobs simultaneously, CPU, memory, and I/O resources are consumed on both sides. Additionally, SQL functions with nondeterministic values—such as now, random, and row_number sorting—can cause data inconsistency across the primary and standby clusters by producing different result sets on the same data. Users are advised to modify SQL statements to ensure deterministic values and deterministic ordering, guaranteeing data consistency between primary and standby. When inconsistency occurs, the primary's data overrides the standby. This synchronization can use "synchronization based on transaction logs and data blocks" technology.

2.2. Dual-Active Mode with Product-Provided Middleware for Scheduling

A product-level scheduling middleware is a component provided by the OLAP product. It offers a unified interface externally and manages SQL scheduling, verification, and synchronization between primary and standby clusters (synchronization uses the database's underlying "transaction log and data block synchronization" technology). Customer businesses simply submit tasks to this product-level middleware, which automatically schedules them: LOAD statements can be sent to both primary and standby simultaneously for dual loading; DML statements can be flexibly configured—either executed on both sides and then verified, or first executed on the primary with incremental data then synchronized to the standby. A task is considered successful only after the data in both primary and standby passes verification.

Furthermore, considering SQL functions with nondeterministic values (e.g., now, random, row_number sorting that produce different result sets from the same data), if the dual-processing scheduling method is adopted, users are advised to modify SQL statements to ensure deterministic values and ordering, guaranteeing consistent SQL execution results across primary and standby. Some simple system time functions can be rewritten by the middleware to ensure consistency of SQL execution results.

The recommended optimal approach is to use "synchronization based on transaction logs and data blocks," which directly synchronizes the primary database's SQL execution result logs and changed data blocks to the standby.

Advantage: Provided at the product level, completely transparent to applications.

Disadvantage: Introduces middleware as an access service. Since all database access goes through this middleware, any middleware anomaly can impact the high availability of the entire dual-active system. The high availability of the middleware itself must be considered.

2.3. Dual-Active Mode with Application-Provided Middleware for Scheduling

The user's application processing jobs connect to the primary database for write operations. Two scheduling methods are supported:

Method 1: Job-Level Real-Time Consistency. At the final step of a job, incremental data from the target tables affected by that job is synchronized to the standby. If synchronization fails, the job is considered a failure.

Method 2: Job-Level Asynchronous Consistency. At the final step of a job, job information is recorded in a synchronization queue. The synchronization queue processing retrieves the completed job information, gets the target tables, and synchronizes their incremental data. When and which tables to synchronize are determined by the "synchronization queue processing" application. Data verification occurs after primary and standby are synchronized.

Advantage: Application-controlled processing logic, achieving job-level real-time or near-real-time consistency with flexible control. The primary cluster can handle writes and perform data processing tasks such as statistical analysis, and after processing, the results are synchronized to the backup cluster. The backup cluster can offload query services from the primary, reducing contention for system resources caused by concurrent reads and writes on the primary.

Disadvantage: Intrusive to application logic, increasing its complexity.

2.4. Dual-Active Mode Based on Strongly Consistent Real-Time Synchronization

Using a unified scheduling cluster as the entry point and adopting virtual cluster technology, the primary and standby clusters are incorporated as logical sub-clusters under unified management of the scheduling cluster. Within the virtual cluster, a mirroring relationship is established between two sub-clusters of identical scale and data distribution policy, forming what is called a mirror cluster. As the name implies, the tables and data in the two mirror clusters are consistent. By establishing a mirror cluster relationship, the primary and standby computing clusters achieve strongly consistent real-time dual-active capability, supporting real-time dual-active scenarios within the same city.

(1)  Establish a mirror relationship between the primary and standby computing clusters. The mirror relationship can be created at the table level or the database level.

(2)  The two mirrored sub-clusters within the virtual cluster can be deployed in the same machine room or in different machine rooms within the same city, with certain requirements on network quality and bandwidth between them. When deployed in different rooms, this forms an intra-city remote disaster recovery setup. In such a deployment, one coordinator node can be deployed in the backup room to achieve data disaster recovery for the management cluster.

(3)  Business operations directly connect to one of the sub-clusters to issue DDL, DML, and DQL SQL statements. For DDL, statements are simultaneously sent to both mirrored clusters for execution. For DML and DQL operations, they are directly executed on the default sub-cluster currently connected. DML results are transmitted to the other sub-cluster using a chain forwarding method. Data modification operations in a mirror cluster must be committed uniformly in both sub-clusters before the result is returned to the user.

(4)  If either the primary or standby computing cluster fails, applications need no changes. The scheduling cluster transparently switches business operations for the application. For example, in the diagram, VC1 and VC2 are in a mirror cluster relationship, with business operations defaulting to VC1. If VC1 experiences an overall failure, the scheduling cluster automatically switches the business connection to VC2.

(5)  If all nodes of the management cluster in the primary machine room fail, manual modification is required to set the management node in the backup room as the sole management cluster node. After that, business operations can issue SQL tasks through the management node in the backup room.

Advantage: Provides external services through a unified scheduling cluster, with the primary and standby computing clusters achieving product-level, strongly consistent real-time dual-active through the mirror relationship. When any entire computing cluster fails, the scheduling cluster automatically dispatches tasks to the surviving cluster, completely transparent to applications, requiring no manual switchover.

Disadvantage: Transactions across the primary and standby computing clusters are strongly consistent, requiring a 10 Gigabit Ethernet connection between them. Not suitable for remote real-time dual-active across wide area networks.

3. Solution Comparison Summary