SQLFire the NewSQL Database – Part-II

Here is the overall Architecture diagram of the VMs network connection on the Physical Host Machine.

SQLFire-Demo-Setup

  • Prepare the SQLFire Cluster

1.Login to SQLFireLocator (192.168.0.13) with the username and password

$mkdir locator

Start the Locator with the following command.

$sqlf locator start -dir=locator -mcast-port=0 -client-bind-address=sqlfirelocator -client-port=7711 -peer-discovery-address=sqlfirelocator -peer-discovery-port=12345

if everything you have configured correctly then the locator will star as shown in the screenshot.

locator-start

2.Login to the SQLFireServer1 (192.168.0.10) with username and password

$mkdir server1

Start the first server

sqlf server start -dir=server1 -mcast-port=0 -locators=sqlfirelocator[12345] -bind-address=sqlfireserver1 -client-bind-address=sqlfireserver1 -client-port=7712

server1-start

3.Login to the SQLFireServer2  (192.168.0.11) with username and password

$mkdir server2

Start the second Server

sqlf server start -dir=server2 -mcast-port=0 -locators=sqlfirelocator[12345] -bind-address=sqlfireserver2 -client-bind-address=sqlfireserver2 -client-port=7712

Don’t start the third server yet.

Lets create some database tables.

Login to the locator again,I assume you have installed SQLfire on your home directory .For me it is /home/pnayak/vFabric_SQLFire_103,now create the database using sqlf command and running the ToursDB_schema.sql file as shown.

create-db

Run this query to see the memebrs in the cluster.

sqlf> select ID,KIND,PID from SYS.MEMBERS;

sqlf> select ID,KIND,PID from SYS.MEMBERS;

ID                                                                                                          |KIND            |PID

—————————————————————————————————————————-

sqlfireserver2(2462)<v2>:59119/45426  |datastore(norma&|2462

sqlfirelocator(2566)<v4>:19404/44678   |locator(normal) |2566

sqlfireserver1(2455)<v1>:36092/45403  |datastore(norma&|2455

sqlf-query1

You also can run the same query  using SQuirrel. You can install SQuirrel on the Base Windows 7 OS and run it from there and connect to the Locator and run the query.

SQL GUI Client.

Download the SQuirrel  SQL Client installer jar file from http://squirrel-sql.sourceforge.net and then install  it using java  -jar command.

java -jar squirrel-sql-<version>-install.jar

Run the query via SQuirrel.

Add sqlfireclient.jar as driver.

add-driver

Create the Alias to connect the SQLFire Server Locator.

connect-alias

Now run the Query.

squirrel1

Now let’s load some data to the replicated tables created  on SQLFire Database.

again connect to the SQLfire DB.

sqlf> connect client ‘sqlfirelocator:7711’;

sqlf> run ‘loadTables.sql’;

after the execution of the query you can verify it on SQurrel GUI as shown below.

squirrel2

You can execute the query to see the DATAPOLICY of the APP Database.

select TABLENAME,DATAPOLICY from SYS.SYSTABLES where TABLESCHEMANAME=’APP’;

Run the following query to know the number of rows you have in the FLIGHTS table.

select count(*) memberRowCount, DSID() from FLIGHTS;

note the number is 542.

query-count

Now Stop SQLFireserver2.

login to SQLFireserver2 (192.168.0.11)

Execute the following command.

$ sqlf server stop -dir=server2

Now Start third Server

Login to SQLFireServer3 (192.168.0.12)

$mkdir server3

$sqlf server start -dir=server3 -mcast-port=0 -locators=sqlfirelocator[12345] -bind-address=sqlfireserver3 -client-bind-address=sqlfireserver3 -client-port=7712

Finally stop the server SQLFireServer1

login to SQLFireServer 1 (192.168.0.10)

Execute the following command.

$ sqlf server stop -dir=server1

At this point, the servers in SQLFireserver1 and SQLFireserver2  are stopped, and the  SQLFireserver3 is running.

Repeat this command in sqlf to size the FLIGHTS table

select count(*) memberRowCount, DSID() from FLIGHTS;

You can check if the number is same as before i.e 542.

So what we did in the above procedure  ?  Why we stopped and started the servers ? To summarize the process . First we started Servers one and two, loaded data into them then we stopped server 2 ,we started server 3 and joined it to the cluster, then Stooped the server 1.

From the above procedure it was clear that SQLFire servers can be joined to the cluster and removed from the cluster dynamically but data remains  and the locator takes care of connecting to the right existing server in the cluster to retrieve data.

Stop the Cluster

sqlf shut-down-all -mcast-port=0 -locators=sqlfirelocator12345] -bind-address=sqlfireserver1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s