Using Sequoia with GlassFish

Sequoia is a open source database clustering middleware that allows any Java application to transparently
access a cluster of databases through JDBC.No code change is required to enable an application to
have failover, replication capabilities.
    This demo will have the modified sample application from Netbeans,  deployed in GlassFish,
interacting with Sequoia controller for database operations. The only change needed in the sample is to
configure the properties of jdbc-connection-pool to interact with Sequoia controller.

Bundles used :

GlassFish v2 build 38
Sequoia 2.10.6
Netbeans 5.5.1 beta

Setup :

Install GlassFish & Netbeans

GlassFish server can be registered through Netbeans > Runtime > Servers > Sun Java System Application Server

Installation of Sequoia :

Extract the downloaded bundle and set the environment variable SEQUOIA_HOME to the installation (extracted) directory.
Sequoia has sample configurations for various modes. This demo will modify and use the "hsqldb-raidb1-distribution-1.xml"
available in SEQUOIA_HOME/config/virtualdatabases to work with JavaDB bundled with GlassFish.

Sequoia Controller :

Controller is responsible for delegating the requests to underlying databases that are transparent to the application.
Behind the scenes, sequoia will log the requests in a recovery table that will be used during recovery of a failed node
(database). The entire setup can be distributed ie., Application, Sequoia controller, databases to store the application data,
 database to store the recovery log can all be distribued. Controller will load balance the read operations (queries) to one
 of the database nodes and will replicate the write operations (create, drop, insert, delete, update) to all the database nodes.

Configuration of virtual database:

Virtual database represents the configuration of individual database nodes, recovery db node and has credentials for application
 to connect to  controller.
 
copy "hsqldb-raidb1-distribution-1.xml" as "hetero-raidb1-distribution-1.xml"
Change VirtualDatabase name to "heterodb"
Set the "admin" password of AuthenticationManager to "adminadmin" [To do controller operations]
Set the virtualuser login of AuthenticationManager to "jdbc" and password to "jdbc" [For applications to interact with Sequoia]
For this demo, Derby is used in embedded mode.

Configuring individual database nodes for data replication :

<DatabaseBackend name="localhost1" driver="org.apache.derby.jdbc.EmbeddedDriver" url="jdbc:derby:heterodb1;Password=APP;User=APP;databaseName=heterodb1;create=true;" connectionTestStatement="select count(*) from sys.systables">       <ConnectionManager vLogin="jdbc" rLogin="APP" rPassword="APP">         <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>       </ConnectionManager>     </DatabaseBackend>     <DatabaseBackend name="localhost2" driver="org.apache.derby.jdbc.EmbeddedDriver" url="jdbc:derby:heterodb2;Password=APP;User=APP;databaseName=heterodb2;create=true;" connectionTestStatement="select count(*) from sys.systables">       <ConnectionManager vLogin="jdbc" rLogin="APP" rPassword="APP">         <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" idleTimeout="30" waitTimeout="10"/>       </ConnectionManager>     </DatabaseBackend>

  

Configuring recovery log table :

<RecoveryLog driver="org.apache.derby.jdbc.EmbeddedDriver"         url="jdbc:derby:recoverydb1;Password=APP;User=APP;databaseName=recoverydb1;create=true;" login="APP" password="APP">         <RecoveryLogTable tableName="RECOVERY" logIdColumnType="BIGINT"           vloginColumnType="VARCHAR (500) " sqlColumnType="VARCHAR(500)"           extraStatementDefinition=",PRIMARY KEY (log_id)"/>         <CheckpointTable tableName="CHECKPOINT"           checkpointNameColumnType="VARCHAR(500) "/>         <BackendTable tableName="BACKEND"           databaseNameColumnType="VARCHAR(500) "           backendNameColumnType="VARCHAR(500) "           checkpointNameColumnType="VARCHAR(500) "/>         <DumpTable tableName="DUMP" dumpNameColumnType="VARCHAR(500) "           dumpDateColumnType="TIMESTAMP"           dumpPathColumnType="VARCHAR(500) "           dumpFormatColumnType="VARCHAR(500) "           checkpointNameColumnType="VARCHAR(500) "           backendNameColumnType="VARCHAR(500) "           tablesColumnType="VARCHAR(500) "/>       </RecoveryLog>

Configuring the Backuper :

Backuper is used to take complete backup of a node (backend) and can be used to create a new backend or
transfer the backup to another controller to bring back the controller from failure.

Above sample configration uses Derby's Embedded driver and hence setting Derby Embedded backuper.

<Backup>       <Backuper backuperName="DerbyEmbeddedBackuper"         className="org.continuent.sequoia.controller.backup.backupers.DerbyEmbeddedBackuper"         options="zip=true"/> </Backup>

Complete virtual database configuration file can be downloaded from here and copied to SEQUOIA_HOME/config/virtualdatbases

Copy the JavaDB driver (derby.jar available in GlassFish_Install_Dir/javadb/lib) to $SEQUOIA_HOME/drivers so that they
are available for Sequoia controller.

Controller configuration :

Configuration file sample is available at SEQUOIA_HOME/config/controller
copy "controller-distributed-1.xml" to "hetero-distributed-1.xml" and set the virtual database to use.

<VirtualDatabase configFile="hetero-raidb1-distribution-1.xml" virtualDatabaseName="heterodb" autoEnableBackends="true"/>

Complete controller configuration file can be downloaded from here and copied to SEQUOIA_HOME/config/controller

Starting the Sequoia controller :

SEQUOIA_HOME/bin/controller.sh -f config/controller/hetero-distributed-1.xml

20:31:21,629 INFO controller.core.Controller Sequoia  controller (2.10.6) 20:31:21,694 INFO controller.core.Controller Loading configuration file: config/controller/hetero-distributed-1.xml 20:31:21,791 INFO controller.core.Controller JMX is enabled 20:31:21,820 INFO controller.core.Controller Starting JMX server on host: 127.0.0.1 20:31:22,185 INFO backup.backupers.DerbyEmbeddedBackuper DerbyEmbeddedBackuper backup compression is set to true 20:31:22,223 INFO backend.DatabaseBackend.localhost1 Adding connection manager for virtual user "jdbc" 20:31:22,229 INFO backend.DatabaseBackend.localhost2 Adding connection manager for virtual user "jdbc" 20:31:22,234 INFO backend.DatabaseBackend.localhost3 Adding connection manager for virtual user "jdbc" 20:31:25,899 INFO sequoia.controller.recoverylog Creating recovery log table: RECOVERY 20:31:26,011 INFO sequoia.controller.recoverylog Creating checkpoint table: CHECKPOINT 20:31:26,160 INFO sequoia.controller.recoverylog Creating backend table: BACKEND 20:31:26,227 INFO sequoia.controller.recoverylog Creating dump table: DUMP 20:31:26,410 INFO controller.RequestManager.heterodb Request manager will parse requests with the following granularity: TABLE 20:31:26,417 INFO controller.virtualdatabase.heterodb Using Hedera properties file:/hedera_jgroups.properties ------------------------------------------------------- GMS: address is127.0.0.1:32771 ------------------------------------------------------- 20:31:31,211 INFO controller.virtualdatabase.heterodb Group heterodb connected to Member(address=/127.0.0.1:32771, uid=heterodb) 20:31:31,211 INFO controller.virtualdatabase.heterodb First controller in group heterodb 20:31:31,422 INFO sequoia.controller.recoverylog Checking recovery log consistency 20:31:31,533 WARN controller.virtualdatabase.heterodb Cannot enable backend localhost1 from a known state. Resynchronize this backend by restoring a dump. 20:31:31,536 WARN controller.virtualdatabase.heterodb Cannot enable backend localhost2 from a known state. Resynchronize this backend by restoring a dump. 20:31:31,538 WARN controller.virtualdatabase.heterodb Cannot enable backend localhost3 from a known state. Resynchronize this backend by restoring a dump. 20:31:31,539 INFO controller.core.Controller Adding VirtualDatabase heterodb 20:31:31,553 INFO controller.core.Controller Waiting for connections on 0.0.0.0:25322 20:31:31,562 INFO controller.core.Controller Controller started on 2007.03.08 10 at08:31:31 PM IST 20:31:31,562 INFO controller.core.Controller Controller 127.0.0.1:25322 ready, listening to requests ...

Initializing the nodes using the Sequoia console:

SEQUOIA_HOME/bin/console.sh

Launching the Sequoia controller console Initializing Controller module... Initializing VirtualDatabase Administration module... Initializing SQL Console module... Sequoia driver (Sequoia core v2.10.6) successfully loaded. localhost:1090 > admin heterodb Virtual database Administrator Login > admin Virtual database Administrator Password > Ready to administrate virtual database heterodb heterodb(admin) > initialize localhost1 force Virtual Database heterodb has been successfully initialized from backend localhost1 heterodb(admin) > initialize localhost2 force Virtual Database heterodb has been successfully initialized from backend localhost2 heterodb(admin) > enable localhost1 Enabling backend localhost1 from its last known checkpoint heterodb(admin) > enable localhost2 Enabling backend localhost2 from its last known checkpoint

Now both the nodes are initialized and can serve for replication.

Sample application :

Start Netbeans, Create a new project > Samples > Enterprise > CustomerCMP




select the jdbc-connection-pool




and change the configuration (DataSource Classname, Properties) to point to sequoia controller



For GlassFish to talk to Sequoia controller, copy the Sequoia jdbc-driver from $SEQUOIA_HOME/drivers/sequoia-driver.jar to
GLASSFISH_HOME/domains/domain/lib/ext

All the set up is done to build & deploy the application to GlassFish.

From Netbeans, build & deploy the sample application. This will build the project, generate .ear file, start GlassFish,
create connection pools, deploy the application to GlassFish

Once the deployment is successful, run project from Netbeans.
Netbeans will open the browser with the web-application's URL.

Customer CMP application :

Simple application where customer details can be captured.



Create a new customer and ensure that the entries are persisted, through the "search" option provided in the application.



Now, to check the fail-over functionality, disable one of the node (backend) through Sequoia console.

heterodb(admin) > disable localhost2 Disabling backend localhost2 with automatic checkpoint. heterodb(admin) >

Sequoia Controller will have similar logs :

22:33:13,236 INFO  controller.RequestManager.heterodb All activity is suspended for heterodb 22:33:13,361 INFO  controller.virtualdatabase.heterodb Checkpoint disable localhost2-127.0.0.1:25322-20070308223313132+0530 was stored 22:33:13,364 INFO  controller.virtualdatabase.heterodb Disabling backend localhost2,  waiting for current transaction to complete on this backend 22:33:13,384 INFO  sequoia.controller.connection 5 connections freed on "jdbc:derby:heterodb1;Password=APP;User=APP;databaseName=heterodb1;create=true;" 22:33:13,509 INFO  controller.virtualdatabase.heterodb Backend localhost2 is now disabled 22:33:13,509 WARN  controller.virtualdatabase.heterodb No information has been found for remote controller Member(address=/127.0.0.1:32773, uid=heterodb) 22:33:13,509 WARN  controller.virtualdatabase.heterodb Updating backend list with unknown backend localhost2  disabled on controller Member(address=/127.0.0.1:32773, uid=heterodb)

Create another customer and ensure that the entries are persisted. [Behind the scenes, localhost1 alone will have
the entries and localhost2 is disabled/down]



Now disable localhost1 and enable localhost2

heterodb(admin) > disable localhost1 Disabling backend localhost1 with automatic checkpoint. heterodb(admin) > enable localhost2 Enabling backend localhost2 from its last known checkpoint

Access the application, search for customers of last name "Prasath"



There will not be any change in behavior of the application.
Behind the scenes, when localhost2 is enabled, all the entries will be persisted into localhost2 from recovery log's last
known checkpoint and ensure transparent failover & replication.

Multiple controllers :

It is possible to have more than one controller for a virtual database. In such a case, URL for the application will be,
for eg : jdbc:sequoia://127.0.0.1:25322,127.0.0.1:25323/heterodb

Controller-2, virtual database configured similar to as shown above. Backends can be localhost3, localhost4.
Complete controller-2, virtual database file , configuration file can be copied to SEQUOIA_HOME/config/virtualdatabase, SEQUOIA_HOME/config/controller respectively.

With 2 controllers available, it is possible to shutdown an controller as a whole for maintenance. In case one of the controller
 fails (eg, controller-2), sequoia driver will use the other and ensures availability. Backup can be taken on controller-1,
 transferred to controller-2 and nodes can be restored.

Steps in brief :

Disable the backends of controller-2
Add new customers using the application.
Take database dump of localhost1.

localhost:1090 > admin Virtual Database Name > heterodb Virtual database Administrator Login > admin Virtual database Administrator Password > Ready to administrate virtual database heterodb heterodb(admin) > backup localhost1 localhost1.bkp DerbyEmbeddedBackuper /home/jagadish/Desktop Backend login for backup process > admin Backend password for backup process > Backup backend localhost1 in dump file localhost1.bkp

Bring back controller-2

heterodb(admin) > show controllers heterodb is hosted by 2 controller(s): 127.0.0.1:1091        127.0.0.1:1090

Transfer the backup from controller-1 to controller-2 :

heterodb(admin) > transfer dump localhost1.bkp 127.0.0.1:1091 nocopy Transferring dump localhost1.bkp to controller 127.0.0.1:1091... Done

Restore the backends using backup.

 




heterodb(admin) > restore backend localhost3 localhost1.bkp.2
Backend login for restore process> admin
Backend password for restore process > 
Restoring backend localhost3 with dump localhost1.bkp.2
heterodb(admin) > restore backend localhost4 localhost1.bkp.2
Backend login for restore process> admin
Backend password for restore process > 
Restoring backend localhost4 with dump localhost1.bkp.2

Disable controller-1

heterodb(admin) > disable localhost2 Disabling backend localhost2 with automatic checkpoint. heterodb(admin) > disable localhost1 Disabling backend localhost1 with automatic checkpoint.

Access the application, customers added when controller-2 was disabled will be visible via controller-2.

References :

GlassFish
Sequoia - Tutorial
Sequoia - Installation Guide
Sequoia - Administration Guide
Netbeans

Technorati Tags: glassfish