GlueSync Tutorial Series – Keep in sync MS SQL Server with Couchbase

We are starting a journey in our successful RDBMS to NoSQL connector GlueSync.

Follow us and learn how to easily sync MS SQL Server and Couchbase Server with an architecture overview and an hands-on demo. During the following weeks more videos will be released, starting with the first of the “advanced” stream, covering “field skipping and field renaming” and the Oracle database video tutorial the week after.

For those of you eager to get started, here is the transcript of the configuration process, a step-by-step guide to setup the environment with the utmost simplicity and ease. Let’s start!

GlueSync – Demo steps

SQL to NoSQL

1. Download MS SQL Server using that command

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Password!' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

2. Setup couchbase in docker

docker pull couchbase/server:enterprise-6.6.2

docker run -d --name db -p 8091-8096:8091-8096 -p 11210-11211:11210-11211 couchbase

3. With SQL editor software, connect to your MS SQL Server instance using the localhost address, create a database called demodatabase and then run this query in the console.

SQL SCRIPT

use demodatabase
go
create table demodata
(
	Id int not null primary key,
	firstname varchar(255),
	lastname varchar(255),
	age int,
	street varchar(255),
	city varchar(255),
	state varchar(255),
	zip varchar(15),
	pick varchar(255)
)
go

4. Import the demo data available here https://tinyurl.com/sva5p29y into your MS SQL Server newly created table, remember to set CSV and semicolon separated values, also don’t forget to flag the option column headers in first row in the bottom left corner of the wizard window.

5. Connect to your Couchbase server and run the initial setup steps. (To connect to it use http://localhost:8091/)

If you’re willing to evaluate also the NoSQL to SQL feature please be sure you setup the eventing service (put a minimum of 100Mb of ram for it, just for demo purposes). Set a username and password, those will need in the config file.

6. Create a bucket in Couchbase, for the sake of the demo i named it demo, put at least ~500Mb for demo purposes

7. Create a folder called gluesync-sql-to-nosql (just for distinguishing it from the other one) and inside it create a folder called config where you should store the config.json configuration file. An example of that config file is pasted here after

{
    "sourceHost": "YOUR MS SQL IP ADDRESS",
    "sourcePort": "1433",
    "sourceName": "demodatabase",
    "sourceUsername": "sa",
    "sourcePassword": "Password!",
    "sourceEntities": {
      "demodata" : {}
    },
    "sourceChangeRetention": 5,
    "copySourceEntitiesAtStartup": true,
    "targetHost": "YOUR COUCHBASE IP ADDRESS",
    "targetPort": "8091",
    "targetName": "demo",
    "targetUsername": "Administrator",
    "targetPassword": "administrator",
    "maxTransactionCountPerIteration": 100,
    "maxItemsCountPerTransaction": 10000,
    "couchbase": {
      "timeoutSeconds": 60
    }
}

8. Get MS SQL container IP address via that command

docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mssql-container-name

9. Use that IP address in the GS config file(s) replacing it in the "YOUR MS SQL IP ADDRESS” section

10. Get couchbase container IP address via that command

docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' couchbase

11. Use that IP address in the GlueSync config file(s) replacing it in the “YOUR COUCHBASE IP ADDRESS” section.

12. Login with your credentials to our registry repository (if you have already logged in before to the same registry please logout first)

docker login registry.gitlab.com -u YOURUSERNAME -p YOURPASSWORD

13. Run the GlueSync mssql-to-couchbase connector using that command. Be sure you run the command from the gluesync-sql-to-nosql folder so it will pass correctly the above mentioned config.json file.

docker run -v $PWD/config:/opt/app/config registry.gitlab.com/molo17srl/products/gluesync/sql-to-nosql-kotlin/mssql-to-couchbase:latest

14. Play around with your queries in SQL 😉

TIP #1 to logout from a docker repo run this command

docker logout registry.gitlab.com

TIP #2 to query the documents imported by the connector in Couchbase you can create an index like this (only for demo purposes)

CREATE PRIMARY INDEX primaryindexdemo on demo

NoSQL to SQL

Similar approach applies to the viceversa case, repeat the setup steps from 1 to 11 of the above script if you haven’t configured the environment yet.

1. Create a new bucket in Couchbase called MetadataBucket, leave a minimum amount of RAM for demo purposes. The name is just for the sake of the demo, feel free to change it but remember to put it correctly into the following config.json file

2. Create a folder called gluesync-nosql-to-sql (just for distinguishing it from the other one) and inside it create a folder called config where you should store the config.json configuration file. An example of that config file is pasted here after

{
    "sourceHost": "YOUR COUCHBASE IP ADDRESS",
    "sourcePort": "8091",
    "sourceName": "demo",
    "sourceUsername": "Administrator",
    "sourcePassword": "administrator",
    "sourceEntities": {
      "demodata" : {}
    },
    "sourceChangeRetention": 5,
    "copySourceEntitiesAtStartup": false,
    "targetHost": "YOUR SQL SERVER IP ADDRESS",
    "targetPort": "1433",
    "targetName": "demodatabase",
    "targetUsername": "sa",
    "targetPassword": "Password!",
    "maxTransactionCountPerIteration": 5000,
    "maxItemsCountPerTransaction": 10000,
    "couchbase": {
      "timeoutSeconds": 60,
      "indexReplicaCount": 0,
      "eventing": {
        "baseUrl": "http://YOUR.COUCHBASE.IP.ADDRESS",
        "metadataBucketName": "MetadataBucket"
      }
    }
}

3. Login with that command

docker login registry.gitlab.com -u YOURUSERNAME -p YOURPASSWORD

4. Run the GlueSync couchbase-to-mssql connector using that command. Be sure you run the command from the gluesync-nosql-to-sql folder so it will pass correctly the above mentioned config.json file

docker run -v $PWD/config:/opt/app/config registry.gitlab.com/molo17srl/products/gluesync/nosql-to-sql-kotlin/couchbase-to-mssql:latest

5. Play around with your queries in CB or SQL 😉

Do not miss the next articles! Follow our MOLO17 channel here and GlueSync Blog.

Many are the stories behind this product I would like to tell you about and even more are its capabilities, so keep reading this series for the next story to read about what GlueSync has permitted a customer to achieve in a real-life retail use case.

Want to try GlueSync? Contact us: sales@molo17.com