Database Online Data Migration Using Shadow Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Software platform upgrades often result in downtime due to data migration issues, as conventional methods require executing application-specific programs during downtime, which can take several hours for large or complex platforms, leading to prolonged disruptions.
Innovation Solution
Data migration is performed during uptime by creating a shadow table with the target structure, using a trigger mechanism to identify changes, and a migration program to transfer data, allowing only a small fraction of data to be converted during downtime, enabling seamless upgrades without extensive downtime.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data migration is performed during downtime using conventional application-specific programs, then data structure conversion from old software version to new software version is achieved, but downtime is prolonged for several hours
Solution Approach 1:
The patent creates a shadow table with the target structure before downtime and performs data migration during uptime. The shadow table is prepared in advance with the new software version's structure, and data is gradually migrated during normal operation, so that only minimal downtime is needed for the final switch.
Solution Approach 2:
The patent creates a shadow table as a copy of the original table but with the target structure for the new software version. This shadow table serves as a parallel structure that can be populated during uptime, allowing the system to switch to the new structure without losing data or requiring extensive conversion time during downtime.
2Loss of time
If data migration is performed during uptime using shadow tables and triggers, then downtime is reduced to minimal levels, but system complexity increases due to additional tables and migration programs
Solution Approach 1:
The shadow table acts as an intermediary structure between the original table and the final target structure. It allows data to be gradually migrated during uptime while maintaining system operation, and serves as a bridge that simplifies the final switch during downtime by having the target structure already prepared and populated.
3Productivity
If conventional data migration methods are used during downtime, then migration tasks can be completed with simple structures, but productivity is reduced due to prolonged system unavailability
Solution Approach 1:
The patent enables data migration to continue during uptime while the system remains operational. The shadow table is populated incrementally during normal system operation, allowing the migration process to run continuously without interrupting business operations, thus maintaining productivity while achieving the migration goal.
Data Source
Figure 1
Figure 2
AI summary
Data is recursively migrated during uptime from a first table to a second table in a first schema in a database while taking into account changes to the first table in a change table. The database has first and second application servers respectively connecting to the database using first and second schemas and running first and second versions of a software program. Downtime can be initiated to migrate the remaining entries in the change table to the second table, delete the first table, and rename the second table to have a same name as the first table prior to deletion so that during uptime the second application server connects to the second table and runs the second version of the software program. Related apparatus, systems, techniques and articles are also described.