Are you modernizing your development process? Are you following a rapid development approach to deliver higher quality and more frequent capabilities to your customers? Part of that process often requires us to work with multiple databases for a transition period when we need both databases available. We need a way to sync the data between the databases, the more seamlessly, the better. Using Couchbase with GlueSync fits that purpose.
GlueSync allows you to replicate data to and from RDBMS (Microsoft SQL Server and Oracle) in real time with Couchbase.
We’ll use Docker in this post as we go through the steps to setup and configure Couchbase, Microsoft SQL Server, and GlueSync, demonstrating the syncing of data between the two.
Before We Get Started
First, this tutorial uses Docker and if you don’t have Docker, you can find more information here https://www.docker.com/. You’ll need to install Docker to complete the steps below.
Next, GlueSync requires a license. To complete this tutorial, you’ll need a license from the GlueSync team. You can go to https://gluesync.com/plans/ and view more info on their plans and https://gluesync.com/contact-sales/ to contact the GlueSync team.
- Run and Configure Couchbase and MSSQL
- Create the configuration file for GlueSync
- Configure and Run GlueSync for Couchbase to MSSQL
- Watch the magic
Run Couchbase and MSSQL
We’ll use Couchbase 6.6.3 for this tutorial. The following Docker command will get Couchbase up and running, exposing the ports we need.
docker run -d --name cb-663 -p 8091-8096:8091-8096 -p 11210-11211:11210-11211 couchbase:enterprise-6.6.3
For the MSSQL Docker run command, notice we’re including the SA password. Update that and make a note of it, we’ll use it later.
docker run -d --name mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=RandomMSSQLPass2!' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-20.04
Now you should have two containers running, Couchbase and MSSQL. We’ll start the GlueSync container after we get these configured.
Configure Couchbase with GlueSync and MSSQL
We’ll start with Couchbase and do a basic installation.
Setup New Cluster
Running the Docker commands above would have started Couchbase and exposed the ports so you can open the Couchbase UI by going to http://localhost:8091/ui/index.html.
We’ll choose Setup New Cluster and set up a cluster of one node (not recommended for production, but works great for dev and doing a tutorial).
Enter the new cluster information and click Next: Accept Terms. Make a note of your password, we’ll need it later. We’re using RandomCBPass2! for this instance.
If you accept the terms and conditions, check that option, and click Finish With Defaults.
NOTE GlueSync works with Couchbase using a couple of Couchbase technologies, Sync Gateway and the Eventing service. We are using the Eventing option in this tutorial. The Eventing feature will be installed with the Finish With Defaults selection below.
Couchbase offers a single install where you can enable all the services or pick and choose to customize to the needs of your project. This tutorial will install all the services for convenience. Learn more about Couchbase services and multi-dimensional scaling.
You should be redirected to the Dashboard. We will use data from the sample bucket in this tutorial. To install the data, click Sample Buckets.
If, for some reason, you don’t see the Sample Bucket link on the dashboard, go to Settings from the left menu, look near the top right of the UI, and you’ll see a menu item for Sample Buckets, as shown below.
Check beer-sample and click Load Sample Data.
Give it a few minutes to load, then click Buckets in the navigation on the left.
You should see the beer-sample bucket we just loaded.
Let’s view the documents. Click the Documents link on the right, next to Statistics.
We’re going to sync documents of type beer. You can put a filter (type=”beer”) in the N1QL WHERE box and click Retrieve Docs to get those documents; it’ll be a little easier to see and test this way.
Create Couchbase User
We’ll create a user for the GlueSync application to use. This is for tutorial purposes and you should follow your security practices when managing your users in conjunction with Couchbase and GlueSync requirements.
Go to the Security link on the left then click ADD USER in the top right.
Fill out the user information, as shown below. We’re using the Username of cb-user and Password RandomCBPass2!; keep this info for later. Make sure you check Full Admin under the roles. Once it’s filled out, click the Add User button.
You should see the user we just created.
Add a GlueSync Metadata bucket
GlueSync uses Couchbase’s Eventing service. Eventing requires a Metadata bucket, and we’ll create one now. Get more info on GlueSync and the Metadata bucket.
Go to Buckets on the left menu and click ADD BUCKET in the top right.
Enter the Metadata Name and click Add Bucket. We’ll use the defaults for the other options.
You should see two buckets now.
We’ll use SQL Server Management Studio to configure MSSQL, which was installed in the step above. If you need to install the management studio, you can find it here.
Login with the credentials passed to the Docker command from the earlier step, in this case, Login: sa and Password: RandomMSSQLPass2!.
Create New Database
We’ll create a new database where the data from Couchbase will be loaded.
In this case, since I’m very creative, I’ll use cb, and choose Ok.
Next, create a MSSQL Login. Right-click the Login folder and choose New Login….
The Login name we’re using is mssql-user and Password is RandomMSSQLUserPass2!.
Set Default database to cb, the database created above.
You should follow your security standards when creating users. The instructions in this article are for tutorial purposes only and some options I’m picking are for simplicity.
Go to User Mapping, on the left under Select a page, and select cb under Users mapped to this login and db_owner under the Database role membership.
Now that we’ve got that setup, verify you can login with the user. Log out of sa and log back in with the new user.
Let’s create a table for the data to be synced to, from Couchbase. Go to the Databases, then cb, then open a New Query window. The table fields in this table match the attributes in the Couchbase beer-samples document. The SQL to create the table is below.
Run the create table statement.
create table beer
name varchar(200) not null constraint beer_pk primary key nonclustered,
abv numeric(5, 2) not null,
brewery_id varchar(max) not null,
description varchar(max) not null,
ibu int not null,
srm int not null,
upc int not null,
updated varchar(max) not null
Configure and Run GlueSync for Couchbase to MSSQL
GlueSync has two components, one for Couchbase to MSSQL, and another for MSSQL to Couchbase. This will set up the application to sync data from Couchbase to MSSQL. Setting up the other direction is a very similar setup.
Make config folder
Make a folder for the GlueSync configuration file. We’ll share this with the Docker container when starting it.
Get Docker network IP addresses for Couchbase and MSSQL
We need the Docker network information for the Couchbase and MSSQL containers to configure GlueSync.
Get the container IDs.
C:\Users\David Elliott> docker container ls -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
10b985792024 mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-20.04 "/opt/mssql/bin/perm…" 34 minutes ago Up 34 minutes 0.0.0.0:1433->1433/tcp, :::1433->1433/tcp mssql
d2df1bad1647 couchbase:enterprise-6.6.3 "/entrypoint.sh couc…" 40 minutes ago Up 40 minutes 0.0.0.0:8091-8096->8091-8096/tcp, :::8091-8096->8091-8096/tcp, 11207/tcp, 0.0.0.0:11210-11211->11210-11211/tcp, :::11210-11211->11210-11211/tcp, 18091-18096/tcp cb-663
Now, get the IP addresses for the containers using those IDs by using the Docker Inspect command on Couchbase.
C:\Users\David Elliott> docker inspect d2df1bad1647 | Select-String IPAddress
Run the Docker Inspect command on MSSQL.
C:\Users\David Elliott> docker inspect 10b985792024 | Select-String IPAddress
The IP addresses are:
- Couchbase – 172.17.0.2
- MSSQL – 172.17.0.3
NOTE: If you stop and restart the containers, the IP address can change and you’ll need to verify the config is still correct.
Create the GlueSync configuration file
Update the information below with your specific info and save it to the config.json file in the folder you created above, i.e. c:\a\gluesync\config\config.json
Get GlueSync credentials to pull GlueSync image from Docker
Check out the Before We Get Started section above for GlueSync license info for the Docker registry and downloading the product.
Once you have that info, follow the steps below to install GlueSync.
Since you may already have credentials stored, you may want to logout. If you run into credential issues, this may be the reason.
C:\Users\David Elliott> docker logout registry.gitlab.com
Removing login credentials for registry.gitlab.com
Login to the registry – this may be a different registry than you typically use, so double-check that value.
C:\Users\David Elliott> docker login registry.gitlab.com
Pull the image.
C:\Users\David Elliott> docker pull registry.gitlab.com/molo17srl/products/gluesync/nosql-to-sql-kotlin/couchbase-to-mssql:latest
latest: Pulling from molo17srl/products/gluesync/nosql-to-sql-kotlin/couchbase-to-mssql
Status: Downloaded newer image for registry.gitlab.com/molo17srl/products/gluesync/nosql-to-sql-kotlin/couchbase-to-mssql:latest
You may want to logout again (I’ve been tripped up when I didn’t log out and get errors next time I run a command).
C:\Users\David Elliott> docker logout registry.gitlab.com
Removing login credentials for registry.gitlab.com
Start the GlueSync Container/Application
Update the path to the config file in the Docker command below to match the path on your computer.
docker run -d --name gluesync-cb-to-mssql -v c:/a/gluesync/config:/opt/app/config registry.gitlab.com/molo17srl/products/gluesync/nosql-to-sql-kotlin/couchbase-to-mssql:1.2.3
You should have three containers running in Docker.
If it’s not running, check the container logs for messages. I’ve inadvertently put the config file into the wrong folder and GlueSync didn’t start; the logs let me know what was going on.
Watch the Magic
Go to the MSSQL and query the table, you should see the data from the Couchbase bucket in the table we created. Once GlueSync started, it synced the data from Couchbase to MSSQL.
To play around with it, you can modify data and see the changes synced across the databases. Go to the beer-samples documents and make a change. Here, I updated two documents. You can see the Docker output log below the Couchbase UI. GlueSync detected the two changes.
Switching over to MSSQL, I can see the updates in MSSQL as well.
We often have to work with multiple databases, particularly when we’re doing a modernization. We may have a phased release or need to get existing data from an older relational database into Couchbase. This could involve custom code and testing or considering a product like GlueSync which makes the process more seamless.
Checkout GlueSync for more information on their products and capabilities.