How to allow Tomcat to connect to multiple Databases?

Question

We have two production databases that have the same data, and the tomcat server is currently connecting to one of them to serve the web service.

Production databases will be updated at the same time (enough time is required for this). Therefore, to minimize downtime, we must manually switch to another database when the update starts. This is a rather cumbersome process.

So, the question is, is there a load balance / fault tolerance at the DataSource level for this to happen automatically? that is, when one database is down, the other is used automatically.

We use the Oracle database, we also wonder if something can be done at the database connection level.

To clarify, we only need read access to the database, so the transaction is not involved.

Answer 1

I would strongly advise against any solution that makes a connection between the Java implementation and data sources, and adhere to the principles of separation of problems .

, - TCP (, HAProxy), Oracle Tomcat . , , HAProxy, , Tomcat, -.

, - .

, , - . , .

---

: , , Tomcat. , keep-live , . - , . , , .

---

FWIW, HAProxy MySQL. Oracle .

Answer 2

- Oracle, / Oracle Jdbc.

.

Answer 3

, , . , -, .

, - ( , Oracle - ), java. , , , .

- . , , (, - , ), .

class MyDatasource implements javax.sql.DataSource {

    private DataSource firstDatasource = ...;
    private DataSource secondDatasource = ...;

    private boolean isMainDbAccessible = true; 

    public Connection getConnection() {
           if(isMainDbAccessible) {

              return firstDatasource.getConnection();
           }
           else {
              return secondDatasource.getConnection();
           }
    }

    public void startMainDbMaintenance() {
         isMainDbAccessible = false;
         // maybe you should passivate somehow the first DS, like close its connections and so forth
    }

    public void endMainDbMaintenance() {
        isMainDbAccessible = true;
        // again, check the connections state here
    }
}

, , tomcat JNDI? , , tomcat , Spring, ? , .

, .

By the way, a similar solution can also be implemented at the Java Driver level (we support 2 instances of the driver)

Hope this helps