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