Database Statement Categorization and Normalization for Replication Consistency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database replication technologies face challenges in maintaining consistency between primary and replicate databases, particularly with SQL DML statements, leading to performance issues and data synchronization problems due to latency and differences in replication architectures.
Innovation Solution
A system comprising a database engine with a statement categorizer and normalizer that categorizes and normalizes SQL DML statements, allowing for consistent replication across primary and replicate databases by regenerating statements on replicate databases, thus overcoming inconsistencies and performance issues associated with result-based replication.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If log based replication is used to transfer replication information, then data can be replicated asynchronously, but data consistency between primary and replicate databases deteriorates due to latency
Solution Approach 1:
The patent introduces a transaction log as an intermediary mechanism that captures all data manipulation statements at the primary database before replication. This log serves as a mediator that ensures both databases process the same transactions in the same order, maintaining consistency while allowing asynchronous replication. The transaction log acts as the intermediary that reconciles the timing differences between primary and replicate database operations.
2Reliability
If statement replication is used to transfer DML statements, then data consistency can be maintained, but performance deteriorates when data on replicate database is a subset of primary database data
Solution Approach 1:
The patent applies local quality by allowing different replicate databases to have different subsets of data from the primary database. Each replicate database can be configured to receive only the specific DML statements relevant to its data subset. This enables optimized replication performance for each replicate database while maintaining consistency within its own data scope, rather than forcing all replicates to process all statements.
3Speed
If multiple copies of data object are maintained for performance improvement, then access times are reduced, but system complexity increases
Solution Approach 1:
The patent segments the replication system into distinct modular components: a transaction log module at the primary database, replication agents that read and process the log, and replicate databases that apply the transactions. This segmentation allows each component to be independently optimized and managed, reducing overall system complexity while enabling multiple data copies for improved access performance.
Data Source
AI summary
Systems, methods and computer program products for statement categorization and normalization are described herein. An embodiment includes a statement categorizer and a statement normalizer. The statement categorizer categorizes statements to reduce or even eliminate inconsistencies that may arise between primary and replicate databases as a result of executing certain statements. Categorized statements are normalized by the statement normalizer to generate normalized statements, which are provided to a replication server. Normalized statements assist replication server to regenerate a statement to be executed on a replicate database.