Database View Reload Strategy for Zero Downtime Data Replication
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face significant downtime and operational impact during the reloading of large application tables from a source system to a target system, as the data unavailability can last for multiple days, affecting related operations.
Innovation Solution
The solution involves creating a database view in the target system that points to a current target table during the initial load and subsequent reloads, allowing continuous access to data via the view while the data is transferred to a new target table, ensuring minimal disruption by maintaining access to the previous table until the reload is complete.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all data of the source table is transferred to the target system during reload, then the target table is updated with current data, but the data becomes unavailable in the target system until transfer is complete
Solution Approach 1:
The target table is segmented into two separate tables: a current target table that remains accessible during reload and a new target table that receives the reloaded data. This segmentation allows the data transfer operation to proceed without blocking access to the current table, thereby eliminating downtime while ensuring data is eventually updated in the new table.
Solution Approach 2:
A database view is introduced as an intermediary layer between the source table and the target table. The view dynamically references the current target table, allowing users to access data through the view during the reload process without directly accessing the source table. This intermediary mechanism maintains data availability while the actual data transfer occurs in the background.
2Adaptability or versatility
If the target table is deleted and reloaded, then the data structure is updated to match source table changes, but related operations are negatively impacted during the unavailability period
Solution Approach 1:
The target table is divided into a current target table and a new target table. The current table maintains the existing structure and remains accessible during reload operations, while the new table receives the updated data structure from the source table. This segmentation allows the data structure to be updated without disrupting ongoing operations, thereby maintaining both adaptability and productivity.
Solution Approach 2:
The new target table is created and populated with data from the source table before the current target table is deleted. This preliminary action ensures that when the structure update is complete, the new table is ready to receive data, allowing seamless switching without operational disruption. The view is updated to reference the new table after data transfer completes.
Data Source
AI summary
Systems and methods include creation of a first target table in a target system, the first target table having a structure of a first source table and loaded with data of the first source table, creation of a view in the target system, the view having a name of the first source table and configured to select from the first target table, reception of a request to reload a current version of the first source table into the target system, and, in response to the request, creation of a second target table in the target system, the second target table having a structure of the current version of the first source table, copying of data of the current version of the first source table to the second target table while the view is configured to select from the first target table, and, after copying of the data of the current version of the first source table to the second target table is complete, modification of the view to select from the second target table.