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>

2 comments:

Jesica m said...

So helpful articles for anybody. I like it. I very much enjoyed reading your description of life at sea.
This blog site is pretty good! How was it made . I view something genuinely interesting about your site so I saved to my bookmarks . George offers Investing in Property opportunities and teaching Commercial to Residential Conversion.

jafor sheikh said...

The very heart of your writing shilst sounding agreeable at first, did not settle very well with me personally after some time. Someplace within the paragraphs you were able to make me a believer but just for a while. I nevertheless have a problem with your leaps in assumptions and one would do well to fill in those breaks. In the event you actually can accomplish that, I will definitely end up being fascinated.
Its such as you learn my thoughts! You seem to grasp so much approximately this, such as you wrote the ebook in it or something. I think that you simply can do with some% to force the massage house a bit, however other than that this is magnificent blog. An excellent read. I will certainly be back.
Property leveraging