SQLFire multi-site WAN replication

In then latest SQLFire 102 release we have introduced muti-site WAN deployments. The example below shows how quickly we can get this setup with simple SQL calls on both sites. In this very simple example we will have 2 distributed SQLFire sites being setup as a multi-site WAN deployment scenario.

The steps below switch from one site to the other so be careful to run the SQL on the correct site.

Note: This demo is designed to on a single laptop/desktop, hence the refernces to localhost

Site 1

1. Start a locator as follows for the first distributed SQLFire system

> sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=10101 -locators=localhost:10101 -conserve-sockets=false -distributed-system-id=1 -remote-locators=localhost[20202] -client-bind-address=localhost -client-port=1527

2. start 2 SQLFire nodes as shown below.

> sqlf server start -server-groups=MYGROUP -locators=localhost[10101] -client-bind-address=localhost -client-port=1529 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[10101] -client-bind-address=localhost -client-port=1530 -dir=server2 &

Site 2

3. Start a locator as follows for the second distributed SQLFire system

> sqlf locator start -peer-discovery-address=localhost -peer-discovery-port=20202 -locators=localhost:20202 -conserve-sockets=false -distributed-system-id=2 -remote-locators=localhost[10101] -client-bind-address=localhost -client-port=1528

4. start 2 SQLFire nodes as shown below.

> sqlf server start -server-groups=MYGROUP -locators=localhost[20202] -client-bind-address=localhost -client-port=1531 -dir=server1 &
sqlf server start -server-groups=MYGROUP -locators=localhost[20202] -client-bind-address=localhost -client-port=1532 -dir=server2 &

Site 1

Create sender/receiver as shown below.

5. Create sender as follows

  
create diskstore cluster1store;

CREATE GATEWAYSENDER cluster2sender
(
  REMOTEDSID 2
  ENABLEPERSISTENCE true
  DISKSTORENAME cluster1store 
)
SERVER GROUPS (MYGROUP); 

6. Create receiver as follows

  
CREATE GATEWAYRECEIVER test_receiver (startport 1550 endport 1561) 
server groups (MYGROUP);

Site 2

Create sender/receiver as well as the table we wish to replicate between sites.

7. Create sender as follows

  
create diskstore cluster2store;

CREATE GATEWAYSENDER cluster1sender
(
  REMOTEDSID 1
  ENABLEPERSISTENCE true
  DISKSTORENAME cluster2store 
)
SERVER GROUPS (MYGROUP);

8. Create receiver as follows

  
CREATE GATEWAYRECEIVER test_receiver (startport 1550 endport 1561) 
server groups (MYGROUP);

Site 1

Create the table we wish to replicate. This must be done on each site as DDL is not replicated, only DML is.

9. Create table as shown below

  
CREATE TABLE test_table 
(ID INT NOT NULL, NAME VARCHAR(10))
GATEWAYSENDER(cluster2sender) SERVER GROUPS (MYGROUP);

Site 2

Create the table we wish to replicate. This must be done on each site as DDL is not replicated, only DML is.

10. Create table as shown below.

  
CREATE TABLE test_table 
(ID INT NOT NULL, NAME VARCHAR(10))
GATEWAYSENDER(cluster1sender) SERVER GROUPS (MYGROUP);

Verifying the setup

With this now setup we simply need to connect to either distributed system and insert some data into our table as shown below.

11. Connect to the first distributed system and insert some data as shown below.

  
[Tue May 29 10:47:47 papicella@:~/sqlfire/vFabric_SQLFire_102/pasdemos/wan-demo/dist1 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1527';
sqlf> insert into test_table values (1, 'apples');
1 row inserted/updated/deleted
sqlf> commit;
sqlf> select * from test_table;
ID         |NAME      
----------------------
1          |apples    

1 row selected

12. Connect to the second distributed system to verify the data was replicated from test_table

  
[Tue May 29 10:46:31 papicella@:~/sqlfire/vFabric_SQLFire_102/pasdemos/wan-demo/dist2 ] $ sqlf
sqlf version 10.4
sqlf> connect client 'localhost:1528';
sqlf> select * from test_table;
ID         |NAME      
----------------------
1          |apples    

1 row selected

For more information on multi site WAN deployments with SQLFire see the documentation below.

http://pubs.vmware.com/vfabric5/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.0/config_guide/topics/gateway-hubs/wan-steps.html