Database Statement Logging for Replication Consistency

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing data replication systems face challenges in maintaining consistency between primary and replicate databases, particularly in ensuring that data manipulation language (DML) statements are executed identically across different replication architectures, leading to potential synchronization issues.

Innovation Solution

A system comprising a database engine, replication agent, and replication server that logs and replicates DML statements by writing normalized statement information into a transaction log, allowing the replication server to selectively apply individual log records or statements based on the replication architecture, using techniques like deferred logging and normalization to ensure consistency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If DML statements are replicated from primary database to replicate databases, then data synchronization is maintained, but data consistency between primary and replicate databases cannot be ensured due to different replication architectures

Engineering Contradiction:

Improvedata consistencyVSAvoidreplication architecture flexibility

Core Design Contradiction:

ReliabilityVSAdaptability or versatility

Solution Approach 1:

The patent changes the parameter of statement representation from raw DML text to normalized form with explicit data identification. By transforming statements into a standardized format that explicitly identifies affected data elements, the system maintains consistency across different replication architectures (asynchronous, synchronous, statement-based, log-based) without being constrained by architecture-specific variations.

Inventive Principle:

Principle #35Parameter changes

Solution Approach 2:

The patent segments DML statements into distinct components: normalized statement information and identified data elements. This segmentation allows the replication system to process and track individual data elements affected by statements, enabling consistent replication regardless of whether the underlying architecture uses statement replication or log-based replication with data snapshots.

Inventive Principle:

Principle #1Segmentation

2Reliability

If statement replication is used to maintain synchronization, then data consistency may be compromised when the same statement affects different data sets on replicate databases, but replicating only statement text without data information limits replication effectiveness

Engineering Contradiction:

Improvedata synchronization accuracyVSAvoiddata identification information

Core Design Contradiction:

ReliabilityVSLoss of information

Solution Approach 1:

The patent performs preliminary normalization of DML statements at the source database before replication, extracting and identifying the specific data elements that will be affected. This preliminary action ensures that the normalized statement information includes explicit data identification, which is then replicated to target databases. This allows the replication system to accurately apply statements to the correct data elements even when replicate databases have different data subsets, preventing synchronization errors while maintaining full replication effectiveness.

Inventive Principle:

Principle #10Preliminary action

3Loss of time

If data is replicated asynchronously to minimize impact on primary database, then replication latency occurs, but if synchronous replication is used to reduce latency, then primary database performance is impacted

Engineering Contradiction:

Improvereplication latencyVSAvoidprimary database performance

Core Design Contradiction:

Loss of timeVSProductivity

Solution Approach 1:

The patent extracts the essential replication information (normalized statement information with data identification) from the primary database's transaction log or statement queue and sends it to replicate databases. By taking out only the critical normalized statement data rather than waiting for full transaction completion or transferring large amounts of raw data, the system enables asynchronous replication with minimized latency while keeping the primary database's workload low and maintaining its performance.

Inventive Principle:

Principle #2Taking out (Extraction)

Data Source

PatentUS8676749B2Statement logging in databases

Publication Date: 2014.03.18 SYBASE INC

AI summary

System, method, computer program product embodiments and combinations and sub-combinations thereof for logging of data manipulation language (DML) statements are described herein. An embodiment includes a database engine, a replication agent and a replication server which replicates data from the database engine to one or more replicate databases. The database engine writes parsed query information into a transaction log so that a process of replication agent may read the parsed query information and forward it to the replication server. The database engine writes all log records associated with a statement in a manner that allows the replication server to distinguish if those log records belong to same statement. In another embodiment, replication agent selectively sends SQL statement information to the replication server based on the replication server architecture.