Bug 62598

Summary: support pool with multiple JDBC data sources
Product: Tomcat Modules Reporter: yair.lenga
Component: jdbc-poolAssignee: Tomcat Developers Mailing List <dev>
Status: NEW ---    
Severity: enhancement    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Linux   

Description yair.lenga 2018-08-04 13:26:30 UTC
We are using Tomcat to access "read-only" databases. For scaling and for fail over, multiple databases are deployed (different physical locations, etc).

The request: would like to be able to create a connection pool that will return connections from ANY of those configured databases. This has big value for
* Load balancing
* Fail Over

Many JDBC drivers provide such functionality in proprietary form (Oracle "thin" client, Sybase HA connections, ...). However, this solution is less than ideal.
* Complex to configure, each driver is different, some drivers require server side configurations
* Difficult to monitor with JMX (see below for wishlist).
* Does not work across database from different vendors.

Compare this with WebLogic JDBC Multi Source: https://docs.oracle.com/middleware/1213/wls/JDBCA/jdbc_multidatasources.htm#JDBCA209)

The ASK - provide functionality similar to WebLogic Multi-Source connection:
* Define a connection pool that "merges" connection from other sources
* Ability to configure primary list
* Ability to configure backup list, which will be used if all (or some) primaries are down

Nice to have:
* Expose status to JMX
* Allow JMX to update the primary list and backup list (enable/disable individual entries, for planned maintenance, etc).

The configuration directive can look like:
<Resource name="jdbc/multiDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.MultiSourceFactory"
          primary="jdbc/conn-1,jdbc/conn-2,jdbc/conn-3,..."
          backup="jdbc/backup-ds-1,jdbc/backup-ds-2,jdbc/backup-ds-3,..."
>

Where 'conn-1' ... 'backup-ds-3' are other JDBC pools that were defined.