Clustering / High Availability

This database supports a simple clustering / high availability mechanism. The architecture is: two database servers run on two different computers, and on both computers is a copy of the same database. If both servers run, each database operation is executed on both computers. If one server fails (power, hardware or network failure), the other server can still continue to work. From this point on, the operations will be executed only on one server until the other server is back up.

Clustering can only be used in the server mode (the embedded mode does not support clustering). The cluster can be re-created using the CreateCluster tool without stopping the remaining server. Applications that are still connected are automatically disconnected, however when appending;AUTO_RECONNECT=TRUE, they will recover from that.

To initialize the cluster, use the following steps:

• Create a database
• Use the CreateCluster tool to copy the database to another location and initialize the clustering. Afterwards, you have two databases containing the same data.
• Start two servers (one for each copy of the database)
• You are now ready to connect to the databases with the client application(s)

Using the CreateCluster Tool

To understand how clustering works, please try out the following example. In this example, the two databases reside on the same computer, but usually, the databases will be on different servers.

• Create two directories: server1, server2. Each directory will simulate a directory on a computer.
• Start a TCP server pointing to the first directory. You can do this using the command line:

  java org.h2.tools.Server
  
      -tcp -tcpPort 9101
  
      -baseDir server1
  

• Start a second TCP server pointing to the second directory. This will simulate a server running on a second (redundant) computer. You can do this using the command line:

  java org.h2.tools.Server
  
      -tcp -tcpPort 9102
  
      -baseDir server2
  

• Use the CreateCluster tool to initialize clustering. This will automatically create a new, empty database if it does not exist. Run the tool on the command line:

  java org.h2.tools.CreateCluster
  
      -urlSource jdbc:h2:tcp://localhost:9101/~/test
  
      -urlTarget jdbc:h2:tcp://localhost:9102/~/test
  
      -user sa
  
      -serverList localhost:9101,localhost:9102
  

• You can now connect to the databases using an application or the H2 Console using the JDBC URL jdbc:h2:tcp://localhost:9101,localhost:9102/~/test
• If you stop a server (by killing the process), you will notice that the other machine continues to work, and therefore the database is still accessible.
• To restore the cluster, you first need to delete the database that failed, then restart the server that was stopped, and re-run the CreateCluster tool.

Detect Which Cluster Instances are Running

To find out which cluster nodes are currently running, execute the following SQL statement:

SELECT VALUE FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME='CLUSTER'

If the result is '' (two single quotes), then the cluster mode is disabled. Otherwise, the list of servers is returned, enclosed in single quote. Example: 'server1:9191,server2:9191'.

It is also possible to get the list of servers by using Connection.getClientInfo().

The property list returned from getClientInfo() contains a numServers property that returns the number of servers that are in the connection list. To get the actual servers, getClientInfo() also has properties server0..serverX, where serverX is the number of servers minus 1.

Example: To get the 2nd server in the connection list one uses getClientInfo('server1'). Note: TheserverX property only returns IP addresses and ports and not hostnames.

Clustering Algorithm and Limitations

Read-only queries are only executed against the first cluster node, but all other statements are executed against all nodes. There is currently no load balancing made to avoid problems with transactions. The following functions may yield different results on different cluster nodes and must be executed with care: RANDOM_UUID(), SECURE_RAND(), SESSION_ID(), MEMORY_FREE(), MEMORY_USED(), CSVREAD(), CSVWRITE(), RAND() [when not using a seed]. Those functions should not be used directly in modifying statements (for example INSERT, UPDATE, MERGE). However, they can be used in read-only statements and the result can then be used for modifying statements. Using auto-increment and identity columns is currently not supported. Instead, sequence values need to be manually requested and then used to insert data (using two statements).

When using the cluster modes, result sets are read fully in memory by the client, so that there is no problem if the server dies that executed the query. Result sets must fit in memory on the client side.

The SQL statement SET AUTOCOMMIT FALSE is not supported in the cluster mode. To disable autocommit, the method Connection.setAutoCommit(false) needs to be called.

It is possible that a transaction from one connection overtakes a transaction from a different connection. Depending on the operations, this might result in different results, for example when conditionally incrementing a value in a row.