Automatic Query Routing Across External Servers for Failover
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems fail to route queries efficiently when a single external server becomes unavailable, requiring manual intervention and impacting business continuity, especially in heterogeneous analytical environments.
Innovation Solution
A method and system for virtual query routing that automatically selects an available external server from a list defined in a modified Data Definition Language (DDL) statement, considering factors like CPU usage, workload settings, and data freshness, with dynamic override options at the session or SQL level.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a single external destination system is used for query routing, then the system structure is simple, but the query fails when the external server is unavailable
Solution Approach 1:
The system pre-configures multiple external destination servers in the DDL statement before query execution. When a query needs to access external data, the system has already established a list of alternative servers, so when the primary server fails, the system can immediately switch to the next available server without manual intervention or complex real-time configuration changes.
Solution Approach 2:
The system dynamically selects which external server to use based on availability during query execution. The DDL statement defines multiple possible destinations, but the actual routing decision is made dynamically at execution time by attempting connections and falling back to alternative servers if the primary one is unavailable, combining structured configuration with flexible runtime adaptation.
2Reliability
If manual intervention by DBA is required for failover, then the system structure remains simple, but the business capabilities are impacted due to time consumption
Solution Approach 1:
The system performs automatic failover by itself without requiring manual intervention from DBAs. When the primary external server becomes unavailable, the system automatically attempts connections to alternative servers defined in the DDL statement and switches to the next available one, eliminating the need for human intervention and significantly reducing failover time.
Solution Approach 2:
The system implements automatic feedback-based failover by monitoring the availability of external servers and automatically responding to unavailability conditions. When a connection to the primary server fails, the system detects this feedback and automatically attempts alternative servers, creating a closed-loop response that eliminates manual intervention and accelerates business continuity.
3Reliability
If multiple external servers are defined in DDL, then the system can handle server unavailability, but the DDL statement complexity increases
Solution Approach 1:
The DDL statement is enhanced to support multiple external destination servers, making it universally applicable for defining both primary and backup servers in a single statement. This multi-functional DDL construct allows the same statement structure to handle normal operation and failover scenarios, reducing the need for separate configuration mechanisms.
Data Source
AI summary
A data request that references an external data environment object (foreign object) is identified. A Data Manipulation Language (DML) statement for accessing the object is traversed in a defined order to identify foreign servers having the foreign object. Connections are attempted to foreign servers in the defined order and a selection to one of the foreign servers is made based on server and/or data conditions. The selected server is used for the request to process the portion of the request that includes the foreign object. In an embodiment and during execution of data request, the server and/or the data conditions can be dynamically overridden to change selection criteria for the selected server.