Thursday, July 16, 2009

Setting JBOSS to leverage database fail over

We have a client who runs a clustered SQL Server instance leveraging a clustered JBOSS environment. We ran into problems in LiveCycle when the client fails over the database server from one node to the other. What we ended up doing is to set up jboss to round-robin a set of database connection URL, such that when one fails, it will try the other connection string. Here's how to set up the adobe-ds.xml and mssql-ds.xml to support database fail over:

<datasources>
<ha-local-tx-datasource>
<jndi-name>IDP_DS</jndi-name>
<connection-url>jdbc:sqlserver://DBSERVER1:PORT;DatabaseName=DBNAME|sqlserver://DBSERVER2:PORT;DatabaseName=DBNAME</connection-url>
<url-delimeter>|</url-delimeter>
<driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
<!-- newly added to test out connection retry -->
<connection-property name="autoReconnect">true</connection-property>
<connection-property name="maxReconnects">10</connection-property>
<connection-property name="initialTimeout">10</connection-property>
<user-name>DBUSER</user-name>
<password>DBPASSWORD</password>
<min-pool-size>1</min-pool-size>
<max-pool-size>30</max-pool-size>
<blocking-timeout-millis>60000</blocking-timeout-millis>
<idle-timeout-minutes>10</idle-timeout-minutes>
<prepared-statement-cache-size>100</prepared-statement-cache-size>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<!-- sql to call when connection is created
<new-connection-sql>some arbitrary sql</new-connection-sql>
-->
<!-- sql to call on an existing pooled connection when it is obtained from pool-->
<check-valid-connection-sql>select count(*) from edcprincipalentity</check-valid-connection-sql>

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<metadata>
<type-mapping>MS SQLSERVER2000</type-mapping>
</metadata>
</ha-local-tx-datasource>
</datasources>

No comments: