Database View Layer for Zero Downtime Schema Upgrades
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Minimizing downtime during application upgrades in enterprise software systems is challenging, as maintenance procedures often require taking the application and database offline, leading to unavailability and disrupting user interaction.
Innovation Solution
Implementing a view layer within the database system that allows transitioning access from the data persistency to a temporary access schema, enabling maintenance operations without downtime, and then back to the original data persistency, using projection views and synonyms to manage database objects and structure changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If maintenance procedures are performed on the database system, then the database can be upgraded and patched, but the application must be taken offline causing downtime
Solution Approach 1:
The database system is segmented into multiple schemas (source schema, target schema, and view layer schemas). The view layer is divided into first and second view layers that can be independently managed. This segmentation allows maintenance operations to be performed on one schema while others remain operational, enabling zero-downtime upgrades.
Solution Approach 2:
A view layer is introduced as an intermediary between the application and the underlying database schemas. This view layer contains projection views that abstract the actual data storage location. During maintenance, the application continues to access data through the view layer while the underlying schemas are upgraded, patched, or migrated without affecting application availability.
2Reliability
If the application is taken offline for maintenance, then database upgrades can be performed, but user interaction is disrupted
Solution Approach 1:
The view layer maintains continuous data access capability throughout the maintenance process. Projection views in the view layer are dynamically updated to point to either the source schema or target schema based on operational needs. This ensures that user interactions with the application continue uninterrupted while maintenance operations are performed on the underlying database structures.
3Loss of time
If a view layer is introduced to enable zero downtime, then continuous access is possible, but system complexity increases
Solution Approach 1:
The view layer serves multiple functions simultaneously: it acts as a data access interface for the application, a migration pathway between schemas, and a maintenance isolation layer. Projection views can be configured to reference either the source or target schema, providing universal access capability that simplifies the overall system architecture despite the presence of multiple schemas.
Data Source
AI summary
Methods, systems, and computer-readable storage media for providing a first access schema in a view layer of the database system, the first access schema including first database objects and projection views, each projection view corresponding to a second database object of second database objects in a data persistency, transitioning access of sessions of an application from the data persistency to the first access schema, executing at least a portion of the maintenance procedure in the database system, the at least a portion of the maintenance procedure changing one or more second database objects within the data persistency, creating a second access schema, and transitioning access of the sessions of the application from the first access schema to the second access schema, transitioning access of the sessions of the application from the second access schema to the data persistency, and removing the second access schema from the database system.