Automatic Client Failover within vFabric SQLFire

If your connected to a SQLFire member who unexpectedly disapears or is shutdown SQLFire will automatically ensure that client swicthes to another server within the distributed system without the need to reconnect as demonstrated using SQLFire command line client "sqlf".

Lets assume we have started 2 SQLFire members as follows

sqlf server start -server-groups=MYGROUP -dir=server1 -client-port=1527 -mcast-port=12333 &
sqlf server start -server-groups=MYGROUP -dir=server2 -client-port=1528 -mcast-port=12333 &

1. Lets connect to the first member as shown below

[Sat Jan 28 22:14:59 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf>

2. Now lets ensure we are indeed connected to the first member as shown below.

sqlf> show connections;
CONNECTION0* -  jdbc:sqlfire://localhost:1527/
* = current connection
sqlf>

3. Lets run a query against a table in the distributed system

sqlf> show connections;
sqlf> select team_id, name, long_name from teams;
TEAM_ID    |NAME           |LONG_NAME                     
----------------------------------------------------------
18         |Giants         |GWS GIANTS                    
1          |Tigers         |Richmond                      
16         |Bulldogs       |Western Bulldogs              
12         |Power          |Port Adelaide                 
5          |Pies           |Collingwood                   
17         |Suns           |Gold Coast Suns               
4          |Blues          |Carlton                       
2          |Crows          |Adelaide Crows                
10         |Roos           |North Melbourne               
13         |Saints         |St. Kilda                     
8          |Cats           |Geelong Cats                  
14         |Swans          |Sydney Swans                  
15         |Eagles         |West Coast Eagles             
11         |Demons         |Melbourne                     
7          |Dockers        |Fremantle                     
9          |Hawks          |Hawthorn                      
3          |Lions          |Brisbane Lions                
6          |Bombers        |Essendon                      

18 rows selected
sqlf> 

4. At this point we will shutdown the first SQLFire member who accepted the connection above using it's client port of 1527.

[Sat Jan 28 22:20:24 papicella@:~/vmware/software/sqlfire/vFabric_SQLFire_101/pasdemos/afl2011 ] $ sqlf server stop -dir=server1
The SQLFire Server has stopped.

5. Now lets re-run the query and verify we are still connected to the distributed system and can still display table data

sqlf> select team_id, name, long_name from teams;
TEAM_ID    |NAME           |LONG_NAME                     
----------------------------------------------------------
18         |Giants         |GWS GIANTS                    
1          |Tigers         |Richmond                      
16         |Bulldogs       |Western Bulldogs              
12         |Power          |Port Adelaide                 
5          |Pies           |Collingwood                   
17         |Suns           |Gold Coast Suns               
4          |Blues          |Carlton                       
2          |Crows          |Adelaide Crows                
10         |Roos           |North Melbourne               
13         |Saints         |St. Kilda                     
8          |Cats           |Geelong Cats                  
14         |Swans          |Sydney Swans                  
15         |Eagles         |West Coast Eagles             
11         |Demons         |Melbourne                     
7          |Dockers        |Fremantle                     
9          |Hawks          |Hawthorn                      
3          |Lions          |Brisbane Lions                
6          |Bombers        |Essendon                      

18 rows selected
sqlf> 

6. Finally lets ensure we indeed did switch to the remaining member which accepts client connections on 1528

sqlf> show connections;
CONNECTION0* -  jdbc:sqlfire://localhost:1528/
* = current connection

For more information on SQLFire see the link below.

http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html

Using a Connection Pool with SQLFire

A Java application can use the JDBC thin driver to access a single member of a SQLFire cluster and execute SQL statements. This makes it a perfect candidate to use a connection pool to maintain connections for clients rather then connect to it individually. Two that spring to mind are Apache DBCP or Oracle's UCP. In this example we show a configuration using Spring and Oracle UCP which creates a pool of SQLFire connections which clients can then use as required.

1. Create a new spring bean application config file with a UCP defined as follows.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

  <bean id="sqlfireUcpDataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="jdbc:sqlfire://localhost:1527/" />
    <property name="connectionFactoryClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
    <property name="connectionPoolName" value="SQLFIRE_UCP_POOL" />
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="20" />
    <property name="initialPoolSize" value="5" />
  </bean>
  
</beans>

2. Create a test class as follows to verify the pool as shown below.

package vmware.au.sqlfire.pool;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

import oracle.ucp.jdbc.PoolDataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestSQLFireJDBCPool 
{
 private Logger log = Logger.getLogger("com.vmware.sqlfire");
 private ApplicationContext context;
 private static final String BEAN_NAME = "sqlfireUcpDataSource";
 private PoolDataSource pds;
 
 public TestSQLFireJDBCPool() 
 {
  context = new ClassPathXmlApplicationContext("application-config-SQLFIRE.xml");
  pds = (PoolDataSource) context.getBean(BEAN_NAME);  
 }

 public void run()
 {
  Connection conn = null;
  List<Connection> connections = new ArrayList<Connection>();
  
  try 
  {
   // ensure pool is started by getting a connection object
   conn = pds.getConnection();
   log.info(conn.toString());
   conn.close();
   
   log.info("Getting 5 connections from pool");
   
   for (int i = 0; i < 5; i++)
   {
    connections.add(pds.getConnection());
   }
   
   log.info(displayPoolDetails());
   log.info("Closing all connections from pool");
   
   for (Connection connection: connections)
   {
     connection.close(); 
   }
   
   log.info(displayPoolDetails());
   
 
  } 
  catch (SQLException e) 
  {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } 
  finally
  {
   connections = null;
  }
 }

    public String displayPoolDetails () throws SQLException
    {
      StringBuffer sb = new StringBuffer();
      
      sb.append("** Pool Details **\n");
      sb.append("NumberOfAvailableConnections: " +
                         pds.getAvailableConnectionsCount());
      sb.append("\nBorrowedConnectionsCount: " +
                         pds.getBorrowedConnectionsCount());
      sb.append("\n");
      
      return sb.toString();
    }
    
 /**
  * @param args
  */
 public static void main(String[] args) 
 {
  TestSQLFireJDBCPool test = new TestSQLFireJDBCPool();
  test.run();

 }

}

3. You will need to use the following JAR files such as Spring, Oracle UCP etc..

Output as follows:

Jan 12, 2012 10:11:54 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@657d5d2a: startup date [Thu Jan 12 22:11:54 EST 2012]; root of context hierarchy
Jan 12, 2012 10:11:54 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-config-SQLFIRE.xml]
Jan 12, 2012 10:11:55 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@62da3a1e: defining beans [sqlfireUcpDataSource]; root of factory hierarchy
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: $Proxy0@1dc18a4c
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Getting 5 connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 0
BorrowedConnectionsCount: 5

Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: Closing all connections from pool
Jan 12, 2012 10:11:55 PM vmware.au.sqlfire.pool.TestSQLFireJDBCPool run
INFO: ** Pool Details **
NumberOfAvailableConnections: 5
BorrowedConnectionsCount: 0

Using DBVisualizer to connect to a SQLFire cluster

There are many database development visual tools out there which more often then not allow you to connect to the many different RDBMS databases including Oracle, MySQL, etc.. In this post here we use DBVisualizer to connect to SQLFire and show how easy you can view/manipulate your distributed SQLFire database from a visual tool.

Note: It's assumed you already have DbVisualizer installed and running.

First thing we need to do is add SQLFire driver JAR file to the list of drivers DBVisualizer can use.

1. Select Tools -> Driver Manager
2. Click the + symbol to define a new driver and enter details as shown below.

Select the com.vmware.sqlfire.jdbc.Driver class

Note: sqlfireclient.jar can be found in $SQLFIRE_HOME/lib directory

In order to use the client driver, you must specify a JDBC connection URL for your SQLFire distributed system. The basic URL format for the client driver is: jdbc:sqlfire://hostname:port/
where hostname and port correspond to the -client-bind-address and -client-port value of a SQLFire server or locator in your distributed system.

3. Close the window when done
4. Right click on "Connections" icon and select "Create Database Connection".
5. If promoted elect "Use Wizard"
6. Enter a connection name in this demo I enter -> pas-sqlfire
7. Select the database driver we created at step #2 which should be "SQLFire 1.0"
8. Click next
9. Enter in connection details as shown below.

If authentication is disabled, then you can specify any temporary username and password value into these fields.

Note: SQLFire uses the username specified in the JDBC connection as the schema name when you do not provide the schema name for a database object. SQLFire uses "APP" as the default schema. If your system does not enable authentication, you can specify "APP" for both the username and password to maintain consistency with the default schema behavior. 

 

 10. Click finish

The following shows a query we run to view the distributed database SQLFire members.

For more information on SQLFire see the link below.

http://www.vmware.com/products/application-platform/vfabric-sqlfire/overview.html