How to implement a fail-over data connection in QlikView / Qlik Sense

A popular implementation for high-availability data sources is to duplicate production data into multiple servers. Once the primary data server is down, a secondary server can come online to ensure undisrupted data queries.

Advanced data sources such as Oracle database support transparent fail-over, which requires no particular implementation at the application level. However, if this feature is not in place (i.e primary and secondary data servers do not share a common connection interface), data connection fail-over can still be implemented in QlikView/ Qlik Sense applications.
 

Currently most Qlik connectors do not support a native feature for automatically switching to fail-over data sources when the primary database is not accessible. However, it is possible to catch exceptions of the primary connection and attempt the secondary connection as follows: 

1. Create two connections to each database. 
2. Set system variable ErrorMode = 0 so that the script execution does not halt when connection error occurs 
3. Attempt the first connection to the primary database 
4. Check system variable ScriptError to see if there is an error with the first connection 
5. If there is, use the second connection to the fail-over database 
6. Reset variable ErrorMode if needed 

Below is an example for this implementation:

SET ErrorMode = 0; //If any error is found, continue execution
CONNECT To  MainConnection;
IF ScriptError >0 then //If there was an error connecting try this
     SET ErrorMode = 1; // If this one fails an error must be thrown
     CONNECT TO FailoverConnection;
ENDIF
SET ErrorMode = 1; // In case there was no error, set ErrorMode to 1 for the rest of the script.

Documentation of the error handling variables can be found on the help site at http://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/ErrorVariables/ErrorVariables.htm

Refer to the community discussion at https://community.qlik.com/thread/220195 for more details.