Hello. I've got an unusual question that is more conceptual than dealing with something that is broken. We have an application that has a multi-terabyte database that requires very high availability for one piece of it. This high availability piece requires read-only access to just a small percentage of the data in the database to perform most of it's functions (at least for a limited window of time). We have handled this uptime requirement by replicating these limited tables to a secondary database. When the primary database needs to be made unavailable to the application for some sort of maintenance we switch the application to point to this secondary database for the duration of the outage.

We are now moving pieces of this functionality to Hibernate and also making this "failover" type processing more automatic. I've come up with an idea of how to make it work, but wasn't sure if this would cause Hibernate some problems.

We already have a custom DataSource class that we have wired up to Hibernate. I am considering having this datasource be backed by 2 connection pools. One for the primary database and one for the secondary database. When we switch the application into "downtime" mode (which would happen through an admin UI) the DataSource would return connections from the secondary connection pool. If I did it this way there would only be one SessionFactory instantiated, but sessions generated from it would wind up getting connections to either the primary or the secondary database depending on the current mode of the application.

Does anyone see any reason this would cause heartache for Hibernate? The other option I see is to have 2 SessionFactories and switch between them, but that seems like a lot of overhead for this relatively rare case.

Thanks for any insight!

Hibernate version: 3.2.1

Mapping documents: N/A

Code between sessionFactory.openSession() and session.close(): N/A

Full stack trace of any exception that occurs: N/A

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true): N/A

Debug level Hibernate log excerpt: N/A