{"id":2039,"date":"2021-10-26T00:00:16","date_gmt":"2021-10-26T07:00:16","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/your-guide-to-syncing-data-between-couchbase-and-relational-databases-with-gluesync\/"},"modified":"2021-10-26T00:00:16","modified_gmt":"2021-10-26T07:00:16","slug":"your-guide-to-syncing-data-between-couchbase-and-relational-databases-with-gluesync","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/pt\/your-guide-to-syncing-data-between-couchbase-and-relational-databases-with-gluesync\/","title":{"rendered":"Your Guide to Syncing Data between Couchbase and Relational Databases with GlueSync"},"content":{"rendered":"

<\/p>\n\n\n\n

\n\n\n\n

<\/p>\n\n\n\n

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<\/a> about Couchbase services and multi-dimensional scaling<\/span>.\u00a0\u00a0<\/span><\/em><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

You should be redirected to the Dashboard. We will use data from the sample bucket in this tutorial. To install the data, click <\/span>Sample Buckets<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

If, for some reason, you don\u2019t see the <\/span>Sample Bucket<\/b> link on the dashboard, go to <\/span>Settings <\/b>from the left menu, look near the top right of the UI, and you\u2019ll see a menu item for <\/span>Sample Buckets<\/b>, as shown below.\u00a0<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Check beer-sample and click <\/span>Load Sample Data<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Give it a few minutes to load, then click <\/span>Buckets<\/b> in the navigation on the left.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

\u00a0<\/span>You should see the beer-sample bucket we just loaded.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Let’s view the documents. Click the <\/span>Documents<\/b> link on the right, next to <\/span>Statistics<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

We’re going to sync documents of type beer. You can put a filter (type=”beer”) in the N1QL WHERE box and click <\/span>Retrieve Docs<\/b> to get those documents; it’ll be a little easier to see and test this way.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

\u00a0<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Create Couchbase User<\/h3>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

We\u2019ll 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.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Go to the <\/span>Security<\/b> link on the left then click <\/span>ADD USER<\/b> in the top right.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Fill out the user information, as shown below. We\u2019re using the Username of <\/span>cb-user<\/b> and Password <\/span>RandomCBPass2!<\/b>; keep this info for later. Make sure you check <\/span>Full Admin<\/b> under the roles. Once it\u2019s filled out, click the <\/span>Add User<\/b> button.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

You should see the user we just created.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

\u00a0<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Add a GlueSync Metadata bucket<\/span><\/h3>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

GlueSync uses Couchbase\u2019s Eventing service. Eventing requires a Metadata bucket, and we\u2019ll create one now. Get more info<\/a> on GlueSync and the Metadata bucket.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Go to <\/span>Buckets <\/b>on the left menu and click <\/span>ADD BUCKET<\/b> in the top right.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Enter the Metadata Name and click <\/span>Add Bucket<\/b>. We\u2019ll use the defaults for the other options.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

You should see two buckets now.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

\u00a0<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

MSSQL<\/span><\/h2>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

We\u2019ll 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<\/a>.<\/span>\u00a0<\/b><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Login with the credentials passed to the Docker command from the earlier step, in this case, Login: <\/span>sa<\/b> and Password: <\/span>RandomMSSQLPass2!<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

\u00a0<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Create New Database<\/span><\/h3>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

We\u2019ll create a new database where the data from Couchbase will be loaded.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

In this case, since I\u2019m very creative, I\u2019ll use <\/span>cb<\/b>, and choose Ok.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

\u00a0<\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Create Login<\/span><\/h3>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Next, create a MSSQL Login. Right-click the Login folder and choose <\/span>New Login\u2026<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

The Login name we\u2019re using is <\/span>mssql-user <\/b>and Password is <\/span>RandomMSSQLUserPass2!<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Set Default database to <\/span>cb<\/b>, the database created above.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

You should follow your security standards when creating users. The instructions in this article\u00a0 are for tutorial purposes only and some options I\u2019m picking are for simplicity. <\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Go to <\/span>User Mapping<\/b>, on the left under <\/span>Select a page<\/b>, and select <\/span>cb <\/b>under <\/span>Users mapped to this login<\/b> and <\/span>db_owner<\/b> under the <\/span>Database role membership<\/b>.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Now that we\u2019ve got that setup, verify you can login with the user. Log out of sa and log back in with the new user.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Create Table<\/span><\/h3>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Let\u2019s create a table for the data to be synced to, from Couchbase. Go to the <\/span>Databases<\/b>, then <\/span>cb<\/b>, then open a <\/span>New Query<\/b> window. The table fields in this table match the attributes in the Couchbase beer-samples document. The SQL to create the table is below.<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

\n\n\n

<\/figure>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

Run the create table statement.<\/span>\u00a0<\/span><\/p>\n\n\n\n

<\/p>\n\n\n\n

<\/p>\n\n\n\n

create\u00a0table\u00a0beer\r\n(\r\n \u00a0\u00a0\u00a0name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0varchar(200)\u00a0\u00a0not\u00a0null\u00a0constraint\u00a0beer_pk\u00a0primary\u00a0key\u00a0nonclustered,\r\n \u00a0\u00a0\u00a0abv\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0numeric(5,\u00a02)\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0brewery_id\u00a0\u00a0varchar(max)\u00a0\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0category\u00a0\u00a0\u00a0\u00a0varchar(max),\r\n \u00a0\u00a0\u00a0description\u00a0varchar(max)\u00a0\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0ibu\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0srm\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0style\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0varchar(max),\r\n \u00a0\u00a0\u00a0upc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0int\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0not\u00a0null,\r\n \u00a0\u00a0\u00a0updated\u00a0\u00a0\u00a0\u00a0\u00a0varchar(max)\u00a0\u00a0not\u00a0null\r\n)\r\ngo<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Configure and Run GlueSync for Couchbase to MSSQL<\/span><\/h2>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
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.\u00a0 \u00a0<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Make config folder<\/span><\/h3>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Make a folder for the GlueSync configuration file. We’ll share this with the Docker container when starting it.<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
mkdir\u00a0c:agluesyncconfig<\/code><\/pre>\n\n\n\n
\u00a0<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Get Docker network IP addresses for Couchbase and MSSQL<\/span><\/h3>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
We need the Docker network information for the Couchbase and MSSQL containers to configure GlueSync.<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Get the container IDs.<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
C:UsersDavid\u00a0Elliott>\u00a0docker\u00a0container\u00a0ls\u00a0-a\r\nCONTAINER ID\u00a0\u00a0\u00a0IMAGE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0COMMAND\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CREATED\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0STATUS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PORTS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAMES\r\n10b985792024\u00a0\u00a0\u00a0mcr.microsoft.com\/mssql\/server:2019-CU10-ubuntu-20.04\u00a0\u00a0\u00a0"\/opt\/mssql\/bin\/perm\u2026"\u00a0\u00a0\u00a034 minutes ago\u00a0\u00a0\u00a0Up 34 minutes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00.0.0.0:1433->1433\/tcp, :::1433->1433\/tcp\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0mssql\r\nd2df1bad1647\u00a0\u00a0\u00a0couchbase:enterprise-6.6.3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"\/entrypoint.sh couc\u2026"\u00a0\u00a0\u00a040 minutes ago\u00a0\u00a0\u00a0Up 40 minutes\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00.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\u00a0\u00a0\u00a0cb-663<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
Now, get the IP addresses for the containers using those IDs by using the Docker Inspect command on Couchbase.<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
C:UsersDavid\u00a0Elliott>\u00a0docker\u00a0inspect\u00a0d2df1bad1647\u00a0|\u00a0Select-String\u00a0IPAddress\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"SecondaryIPAddresses":\u00a0null,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"IPAddress":\u00a0"172.17.0.2",\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"IPAddress": "172.17.0.2",<\/code><\/pre>\n\n\n\n
Run the Docker Inspect command on MSSQL.<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
C:UsersDavid\u00a0Elliott>\u00a0docker\u00a0inspect\u00a010b985792024\u00a0|\u00a0Select-String\u00a0IPAddress\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"SecondaryIPAddresses":\u00a0null,\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"IPAddress":\u00a0"172.17.0.3",\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0"IPAddress":\u00a0"172.17.0.3",<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
The IP addresses are:<\/span><\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n