How best to handle DB failover?

[mcevikce]

I have read how to configure HA maneged connection factories as well as how to configure a data source to detect if connection is valid by useing:

  <check-valid-connection-sql>. Unfortunetly I am still running into issues.

I have a data source configured in a following wat:

<local-tx-datasource>

  <jndi-name>jdbc/DSWriteSybaseServer</jndi-name>

<connection-url>jdbc:sybase:Tds:dswrite.sybase.url.com:4500</connection-url>

  <driver-class>com.sybase.jdbc3.jdbc.SybDriver</driver-class>

  <security-domain>DSWriteSybaseDBPassword</security-domain>

  <min-pool-size>5</min-pool-size>

  <max-pool-size>${m25</max-pool-size>

  <connection-property name="APPLICATIONNAME">${jbmyapplication</connection-property>

  <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>

  <check-valid-connection-sql>select @@servername</check-valid-connection-sql>

  <metadata>

     <type-mapping>Sybase</type-mapping>

  </metadata>

</local-tx-datasource>

What happens is, the connection URL defined above is a DNS cname mapped to an IP address. When DB failover occurs, we switch the IP address for that DNS cname to our DR DB server's IP address. DB failover doesn't mean the database server is totally down. DB server can still be accesses but all accounts are locked.

But even with <check-valid-connection-sql> defined, my old connections are still there. I would expect all of the old connections to be recycled but thats not the case.

Is there a way to clear all the connections in my datasource and recreate them with new URL?

[vickyk]

mcevikce wrote:

But even with <check-valid-connection-sql> defined, my old connections are still there. I would expect all of the old connections to be recycled but thats not the case.

Is there a way to clear all the connections in my datasource and recreate them with new URL?

Actually you are not using ha datasource, it seems the URL gets changed on the fly.

The DB connections available in the pool are alive, the check-valid-connection when called will basically take longer time since the DB is locked, it is not closed/shutdown. If it would have been closed/shutdown I would have recommended you to try <use-fast-fail> as mentioned here

http://community.jboss.org/wiki/ConfigJCACommon

In your case I would suggest you to write a custom MBean which will flush the pool by invoking flush() operation on one of the JCA infrastructure MBeans, check jboss.jca:* Mbeans ( ManagedConnectionPool::flush()).