DDL Trigger Replication Agent for Database Schema Synchronization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems lack the capability to replicate data definition language (DDL) commands in environments where DDL command text is not logged, stored, or recorded, limiting data replication and fault tolerance.
Innovation Solution
Implementing a DDL trigger that captures DDL events, logs them into a transaction log, and uses a replication agent to process and replicate these commands across databases, ensuring that DDL operations are executed consistently across multiple database systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Device complexity
If DDL command text is not logged, stored or recorded in the database system, then the device complexity is reduced and storage requirements are minimized, but the capability to replicate DDL commands across databases is lost
Solution Approach 1:
The patent introduces a replication agent as an intermediary component that captures DDL commands through triggers and replicates them to target databases. This mediator enables DDL replication without requiring the database system itself to log DDL command text, thus resolving the contradiction between reduced logging infrastructure and maintained replication capability
Solution Approach 2:
The system creates copies of DDL commands through triggers that capture the command text and replicate it to shadow tables and target databases. This copying mechanism allows DDL replication without requiring the primary database system to maintain logs of DDL operations
2Reliability
If DDL commands are replicated across multiple databases, then fault tolerance and data availability are improved, but the system complexity and coordination overhead increase
Solution Approach 1:
The replication system is segmented into distinct components: triggers for capturing DDL events, replication agents for processing and transmitting commands, and shadow tables for storing command history. This segmentation allows each component to perform its function independently, reducing overall system complexity while maintaining fault tolerance
Solution Approach 2:
The system performs preliminary actions by setting up triggers and replication agents in advance on both source and target databases. This preliminary configuration enables automatic DDL replication without requiring complex real-time coordination during actual DDL operations, thus improving reliability while managing system complexity
3Reliability
If DDL triggers are implemented to capture and log DDL events, then DDL replication capability is enabled, but the processing time and overhead of DDL operations increase
Solution Approach 1:
The trigger captures DDL command text by copying it to a shadow table rather than requiring extensive processing or validation. This simple copying operation minimizes the time overhead while enabling complete DDL replication functionality
Solution Approach 2:
The system uses lightweight trigger mechanisms that execute quickly and dispose of their work by inserting data into shadow tables. These triggers are designed to be fast, temporary operations that do not persistently block DDL operations, thus minimizing time loss while enabling replication
Data Source
AI summary
Systems, methods and computer program products for DDL replication are described herein. An embodiment includes a replication agent to instantiate one or more DDL triggers in a primary database and a replication server to provide DDL command text to a replicate database based on said DDL triggers. The replication agent uses the DDL trigger(s) to capture one or more DDL events and retrieve a transaction log associated with the DDL events. The replication agent processes the transaction log to obtain DDL command text. The DDL command text is then sent to a replicate database by the replication server where it is executed in an appropriate user context. In this way, DDL commands in may be replicated using DDL trigger(s) and session context switching.