How do you connect a Datasource to a Cluster ?

When connecting WildFly / JBoss EAP to a Database cluster you need to provide an appropriate JDBC Connection String.

We’ll examine three popular Database: Oracle, MySQL and PostgreSQL.

Oracle Real Application Clusters (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>

MySQL and Maria DB Cluster

MySQL / MariaDB 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 to specify the failover and load balancing mode:

jdbc:(mysql|mariadb):[replication:|sequential:|loadbalance:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

Each parameter corresponds to a specific use case:

Mode Description
sequential This mode supports connection failover in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on slaves. The connector will try to connect to hosts in the order in which they were declared in the connection URL, so the first available host is used for all queries.
For example, let’s say that the connection URL is the following: jdbc:mariadb:sequential:host1,host2,host3/testdb
When the connector tries to connect, it will always try host1 first. If that host is not available, then it will try host2. etc. When a host fails, the connector will try to reconnect to hosts in the same order.
loadbalance This mode permits load-balancing connection in a multi-master environment, such as MariaDB Galera Cluster. This mode does not support load-balancing reads on slaves. The connector performs load-balancing for all queries by randomly picking a host from the connection URL for each connection, so queries will be load-balanced as a result of the connections getting randomly distributed across all hosts.
replication This mode supports connection failover in a master-slave environment, such as a MariaDB Replication cluster. The mode supports environments with one or more masters. This mode does support load-balancing reads on slaves if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave from the connection URL to execute read queries for a connection.
aurora This mode supports connection failover in an Amazon Aurora cluster. This mode does support load-balancing reads on slave instances if the connection is set to read-only before executing the read. The connector performs load-balancing by randomly picking a slave instance to execute read queries for a connection.

Example:

"jdbc:mysql:replication://master1,slave1/test"

PostgreSQL Failover

To support simple connection fail-over it with PostgreSQL it is possible to define multiple endpoints (host and port pairs) in the connection url separated by commas. The driver will try to once connect to each of them in order until the connection succeeds. If none succeed, a normal connection exception is thrown.

The syntax for the connection url is:

jdbc:postgresql://host1:port1,host2:port2/database

The simple connection fail-over is useful when running against a high availability postgres installation that has identical data on each node.

For example an application can create two connection pools. One data source is for writes, another for reads. The write pool limits connections only to a primary node:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=primary

And read pool balances connections between secondary nodes, but allows connections also to a primary if no secondaries are available:

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSecondary&loadBalanceHosts=true

If a secondary fails, all secondaries in the list will be tried first. In the case that there are no available secondaries the primary will be tried. If all of the servers are marked as “can’t connect” in the cache then an attempt will be made to connect to all of the hosts in the URL in order

Found the article helpful? if so please follow us on Socials