CODEWITHSUNDEEP
javadatabaseoracle-databasetomcatdatasource
Wudong
Wudong

Reputation: 2360

How to allow Tomcat to connect to more than one Databases?

We have two production databases that have the same data, and a tomcat server currently will connect to one of them to serve a webservice.

The production databases will be updated on at a time (taking quite some time to do so). So to minimise the downtime we will have to manually switch to the other database when running the updating. It is quite cumbersome process.

So the question is is there any load balance/fault tolerance in the DataSource layer to allow this happen automatically? i.e., When one database is down, the other one is used automatically.

We are using Oracle database, also wonder if it is something can be done in the database connection layer.

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

Upvotes: 4

Views: 2063

Answers (3)

VH-NZZ
VH-NZZ

Reputation: 5428

I would strongly advise against any solution introducing coupling between the Java implementation and the data sources and stick to the tenets of separation of concerns.

Specifically, I'd set up a TCP proxy (like HAProxy) to load-balance your two Oracle databases and configure Tomcat to connect to the proxy. That way, you'll get load-balancing if you enable both databases within HAProxy and if you need to upgrade them individually, you just cleanly phase them off one at a time within the proxy without Tomcat noticing anything.

If you need no load-balancing, the proxy can still be used to direct connection to one database or the other.

Of course, depending on your problem, a master–master replication may also be worth looking into. That wouldn't discredit using a proxy, though. HTH

Remark: There are details to pay attention to if you're using a connection pool within Tomcat. Namely, configure the pool to routinely perform the keep-alive ping of idle connections and make sure that the frequency is below that of the proxy. Otherwise the proxy might silently destroy a connection before the pool notices it. And that, lad, is a nightmare to debug.

FWIW, have a look at this walkthrough for setting up HAProxy with two MySQL servers. Using Oracle shouldn't be a far cry from it.

Upvotes: 2

Mark Bramnik
Mark Bramnik

Reputation: 42481

As far as I understand, Its not easy to do, and you should be very careful doing so. I'll explain an idea, we've used something that reminded me your question.

So, if you don't want to use some Database specific solution (I'm sure, Oracle has something to offer here), you might want to implement it at the java layer. Basically you should implement your own data source which will be much more intelligent than the data source that comes out of the box.

Basically the main idea behind the data source is to supply connections upon request. So, when the first database goes for maintenance, the datasource should be notified (probably manually or something, you'll need to develop some functionality) and then all the subsequent requests to the datasource will be redirected to the new database. 

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
    }
}

Technical implementation really depends on how you actually use your datasources, do you define a tomcat specific Resource and obtain a datasource from JNDI? Or maybe you don't use tomcat here but instead work with Spring that provides an access to the datasource? There are plenty of possible configuration, so I can't be more specific here.

But I guess, you've got the idea.

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

Hope this helps

Upvotes: 0

madhav-turangi
madhav-turangi

Reputation: 431

Safer way is to enable failover support at the Oracle database side, as you get better connection/session management out of the box from Oracle database and do not have to deal that at Jdbc DataSource level.

There is a good blog post.

Upvotes: 0

Related Questions