Database Schema Segmentation for Zero Downtime Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
During zero downtime upgrades, existing technologies face challenges in managing database access, leading to potential corruption of production systems due to incorrect metadata or access computations, which can result in data being written to shared tables, compromising business processes and data integrity.
Innovation Solution
A zero downtime upgrade procedure that renames source tables, clones them, and equips them with database triggers to transfer data, while using a bridge database schema to manage access, restricting read and write access to unmanaged tables through a names table and logging access attempts to prevent data corruption and ensure consistency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If upgrade procedure runs in parallel with production system in the same database, then zero downtime is achieved, but data consistency and integrity are compromised due to shared table access
Solution Approach 1:
The database schema is segmented into two separate schemas: a production schema for the running system and a target schema for the upgrade. This segmentation allows parallel operation while preventing direct conflicts, as each schema operates independently with its own table set.
Solution Approach 2:
A bridge schema is introduced as an intermediary layer that manages access between the production and target schemas. The bridge schema contains metadata about table relationships and coordinates data access, allowing the upgrade procedure to access necessary data without directly conflicting with production operations.
2Productivity
If upgrade procedure accesses shared tables, then data can be updated, but production system data may be corrupted through unintended writes
Solution Approach 1:
The upgrade procedure extracts and copies only the specific data and metadata necessary for the upgrade into the target schema, rather than accessing and modifying shared production tables directly. This extraction approach allows the upgrade to proceed with its own data copy, eliminating the risk of corrupting production data.
Solution Approach 2:
The system performs preliminary actions by creating a complete copy of the production schema as the target schema before the upgrade begins. All necessary data is pre-copied and prepared in the target schema, so the upgrade procedure operates on this prepared copy rather than on the live production data, preventing any potential corruption.
3Ease of operation
If metadata about database access is provided manually or computed automatically, then upgrade procedure can access required tables, but errors in metadata can endanger production system consistency
Solution Approach 1:
The system implements self-service through automatic metadata computation and validation mechanisms. The bridge schema automatically discovers and records table relationships, access patterns, and dependencies. The system validates metadata correctness through consistency checks, eliminating the need for manual metadata provision and reducing errors associated with manual configuration.
Solution Approach 2:
The system incorporates feedback mechanisms that continuously monitor and validate metadata during the upgrade process. If inconsistencies or errors are detected in the metadata, the system provides feedback to correct them before they can affect production data consistency, ensuring reliable operation throughout the upgrade.
Data Source
AI summary
A zero downtime upgrade procedure upgrading a source system to a target system is initiated that prepares software for the target system in parallel to the operation of the source system. As part of the upgrade procedure, source tables of the source system are renamed. Thereafter, the renamed source tables are cloned for the target system. Further, the renamed source tables are equipped with database triggers to transfer data from the renamed source tables to the cloned tables used by the source system according to a source database schema. Next, the target tables are generated that include updated software content for later use by the target system while the source tables are used via a bridge database schema. Tables can then be shared between the source database schema accessed via the bridge database schema and the target system.