Your Guide to Syncing Data between Couchbase and Relational Databases with GlueSync

Introduction

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

Docker

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.

GlueSync

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.

High-level Steps

1. Run and Configure Couchbase and MSSQL
2. Create the configuration file for GlueSync
3. Configure and Run GlueSync for Couchbase to MSSQL
4. Watch the magic

Run Couchbase and MSSQL

Couchbase

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.

MSSQL

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.

Verify Containers

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.

 

MSSQL

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.

 

Create Login

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.

Create Table

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. 

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.

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.

Pull the image.

Update the path to the config file in the Docker command below to match the path on your computer.

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.

 

Conclusion

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.