How do you connect a Datasource to a Cluster ?
- Published: 04 April 2009
JBoss recipe of the day
We'll examine two popular Database: Oracle and MySQL
Oracle cluster (RAC)
Oracle Real Application Clusters (RAC) is a software component you can add to a high-availability solution that enables users on multiple machines to access a single database with increased performance. RAC comprises two or more Oracle database instances running on two or more clustered machines and accessing a shared storage device via cluster technology. To support this architecture, the machines that host the database instances are linked by a high-speed interconnect to form the cluster.
Oracle RAC allows multiple computers to run the Oracle RDBMS software simultaneously while accessing a single database thus providing a clustered database. In order to benefit from Oracle RAC features like fault tolerance and load balancing all you have to do is configuring the connection url with the list of Oracle hosts which belongs to the cluster.
In this example we are configuring our datasource to connect to a RAC made up of host1 and host2:
<connection-url> jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on) (address=(protocol=tcp)(host=host1)(port=1521))(address=(protocol=tcp)(host=host2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=(type=select)(method=basic)))) </connection-url>
My SQL cluster
MySQL Cluster is a real-time open source transactional database designed for fast, always-on access to data under high throughput conditions.
In order to achieve load-balancing and failover across MySQL cluster you need to modify your jdbc Connection string adding the "loadbalance" keyword. To do load-balancing across the SQL nodes in MySQL Cluster, you would use a different JDBC connection string with the "loadbalance" keyword added:
<connection-url> jdbc:mysql:loadbalance://host-1,host-2,...host-n/database?loadBalanceBlacklistTimeout=5000 </connection-url>
Notice the "loadBalanceBlacklistTimeout" adds the needed feature that failed connections in a connection pool are put aside for the specified time, and only working connections are utilized. This parameter is essential for proper failover.