MongoDB

Timothy Marland

Enterprises around the world are looking to modernize their existing applications. They need a streamlined way to synchronize data from devices at the Edge into their cloud data stores. Whether their goals are business growth or fending off the competition, application modernization is the primary vehicle that will help them get there. 

Often the first step in this process is to move data from an existing relational database repository (like Oracle, SQL Server, DB2, or Postgres, for example) into a JSON-based flexible database in the cloud (like MongoDB, Aerospike, Couchbase, Cassandra, or DocumentDB). Sounds simple, right? I mean, really, if JSON (NoSQL) is so simple and flexible, why would data migration be hard? There must be a bunch of automated tools to facilitate this data migration, right? 

Unfortunately, the answers are “Not really,” “Because data synchronization is rarely simple,” and “The available tools are often DIY-based and don’t provide nearly the level of automation required to facilitate an ongoing, large-scale, production-quality, conflict-resolved data synchronization.”

## Why is this so complex?

### Data modeling

One of the first challenges is data modeling. To effectively leverage the benefits inherent in a JSON-based schema, you need to include data modeling as part of your migration strategy. Simply flattening or de-normalizing a relational schema into nested JSON structures, or worse yet, simply moving from relational to JSON without any data modeling consideration, results in a JSON data repository that is slow, inefficient, and difficult to query. You need an intelligent data modeling platform that automatically creates the most effective JSON structures based on your application needs and the target JSON repository without requiring specialized resources like data scientists and data engineers. 

### Building and monitoring pipelines

Once you’ve mapped the data, you need tools that allow you to build reliable, scalable data pipelines to move the data from the source to the target repository. Sadly, most of the tools available today are primarily DIY scripting tools that require both custom (often complex) coding to transform the data to the new schema properly and custom (often complex) monitoring to ensure that the new data pipelines are working reliably. You need a data pipeline automation and monitoring platform to move the data and ensure its quality. 

### DIY is hard

This process of data synchronization, pipeline automation, and monitoring is where most application modernization projects get bogged down and/or ultimately fail. These failed projects often consume significant resources before they fail, as well as affect the overall business functionality and outcomes, and lead to missed objectives. 

## CDC: MongoDB Atlas, Atlas Device Sync, and Dataworkz

Synchronizing data between edge devices and various databases can be complex. Simplifying this is our goal, and we will demonstrate how to achieve bi-directional synchronization between mobile devices at MySQL in the cloud using MongoDB Atlas Device Sync and Dataworkz.

Let's dive in.

## Prerequisites 

- Accounts with [MongoDB Atlas](https://www.mongodb.com/cloud/atlas/register) (this can be tested on free tiers), [Dataworkz](http://dataworkz.com/), and Google Cloud
- Kafka
- Debezium

## Step 1: prepare your mobile application with Atlas Device Sync

Set up a template app for this test by following the [steps outlined in the docs](https://www.mongodb.com/docs/atlas/app-services/get-started/#tutorials). Once that step is complete, you will have a mobile application running locally, with automated synchronization back to MongoDB Atlas using the Atlas Device Sync SDK. 

## Step 2: set up a source database and target MongoDB Atlas Collection

We used GCP in us-west1-a and Cloud MySQL for this example. Be sure to include [sample data](https://www.mongodb.com/docs/guides/atlas/sample-data/).

### Check if BinLog replication is already enabled

![Check if BinLog Replication is Enabled][1]

1. Access your Google Cloud MySQL instance.
2. In the left navigation pane, under **Databases**, select **Backups**.
3. Access your Google Cloud MySQL instance.
4. In **Settings**, check the status of **Automated backups**.
5. If the status is *Disabled*, follow the steps below.

### Enable BinLog replication

These steps are necessary to set up some of the background processes which enable ongoing two-way synchronization.

1. In **Backups**, **Settings**, click **+ CREATE BACKUP**.
2. In the **Edit backup settings** dialog:
    - Select the **Automate backups** and **Enable point-in-time recovery** check boxes.
    - Click **SAVE** to activate the changes.

![Enable Automated Backups][2]

3. Check the status of **Automated backups** again under **Settings**. It should appear as shown below. This step is important because the metadata created in the backup process is used by the tools to help enable bi-directional synchronization.

![GCP Backup Settings][3]

### Step 3: configure connector for MySQL 

The connector for MySQL needs to be defined in Dataworkz.

![Configure Dataworkz for MySQL][4]

- Log in to the Dataworkz application. 
- Go to **Configuration** -> **Databases** -> **MySQL**. 
- Click the + icon in the above image to add the configuration. 
- It will open the MySQL configuration screen. 

![Connect to MySQL in Dataworkz][5]

- Provide the following details in the configuration screen: 
    - MySQL connector name 
    - Host server and port 
    - User name 
    - Password 
    - Select Deployment Type 
    - Select TLS/SSL option as appropriate 
- Click **Save** to add the MySQL configuration. 

### Step 4: configure connector for MongoDB 

The connector for MongoDB Atlas needs to be defined in Dataworkz. 

![MongoDB Atlas definition in Dataworkz][6]

- Log in to the Dataworkz application. 
- Go to **Configuration** -> **Databases** -> **MongoDB.**
- Click the + icon in the above image to add the configuration. 
- It will open the MongoDB configuration screen. 

![MongoDB configuration screen in Dataworkz][7]

- Provide the following details in the configuration screen (these can be accessed by clicking on the **Connect** button displayed below).
    - MongoDB Connector Name 
    - Host server 
    - User name 
    - Password 

![MongoDB Atlas Connect button display on overview screen][8]

- Click **Save** to add the MongoDB configuration. 

### Step 5: set up Kafka

The connector for Kafka needs to be defined in Dataworkz.

![Kafka connection configuration in Dataworkz][9]

- Log in to the Dataworkz application.
- Go to **Configuration** -> **Streaming Systems** -> **Kafka**. 
- Click the + icon in the above image to add the configuration. 
- It will open the Kafka configuration screen. 

![Kafka connection configuration details in Dataworkz][10]

- Provide the following details in the configuration screen: 
    - Kafka Connector Name 
    - Bootstrap server name and port (as per the format in the image below) 
    - Zookeeper (optional)
- Click **Save** to add the Kafka configuration. 

### Step 6: configure CDC for MySQL 

Configure CDC for MySQL in Dataworkz by following these steps:

![MySQL CDC configuration in Dataworkz][11]

- Log in to the Dataworkz application. 
- Go to **Configuration** -> **Change Data Capture** -> **MySQL**. 
- Click the + icon in the above image to add the configuration. 
- It will open the CDC configuration screen. 

![Dataworkz CDC configuration screen][12]

- Provide the details in the configuration detail screen: 
    - Choose the Workspace. 
    - Choose Kafka (which was configured in the previous step).
    - Add the Debezium server name. 
    - Choose the MYSQL Storage that was previously configured. 
- Select the appropriate database from the list. 
- Choose the Database table for Sync.

![Dataworkz CDC configuration screen][13]

You can see the configured MySQL CDC in the list of configurations. 

![Dataworkz MySQL CDC configuration showing up in list][14]

### Step 7: data preparation — JSON modeling 

This step enables the modeling of the results of RDBMS in JSON structure. To do this, select **Data Prep** -> **JSON Modeller** inside the Dataworkz settings.

![Dataworkz settings JSON modeling setup][15]

![Dataworkz settings JSON modeling detail screen][16]

- Click on **Select root dataset for json modeling** (above image).

![Workspace selection screen for CDC][17]

- Select the Workspace in which MySQL CDC was configured. 
- Select cdc_kafka as the Source Collection. 
- Choose root/base table/dataset. 
- Click on the **Enable Bi-directional sync** checkbox. 
- Select a sync field from the list of the columns that will be used to determine the last updated time for a row. 
- Select columns for the target dataset. 
- Click **Update** and you will see the below screen. 

![JSON modeling screen in Dataworkz][18]

- Choose the desired mapping with the base table by right-clicking. 
    - If the table has one-to-many mapping, choose **Create array document**. 
    - If the table has one-to-one mapping, choose **Create field in child document** 
- Selecting **create array document** would require selecting a child table relationship (for creation of an array document).
- Choose **Child Table** in the screen that pops up. 

![Dataworkz array document child table screen][19]

- Choose **join key** in the **Relationship mapping** section. 
- In the **Unique Key** section, choose the unique key for the child table (used for update or delete events). 
- Select a sync field from the list of the columns that will be used to determine the last updated time for a row. 
- Click **update**. 
- A one-to-many relationship shows up. 

![Dataworkz JSON model detail screen][20]

- Click on **Save** and choose the target destination (MongoDB Collection, in this case).

![Dataworkz JSON Upsert and key settings][21]

- Choose **Transfer Mode** as Upsert and select the unique key for the base table in the pop-up box. 

![Dataworkz custom mapping settings screen][22]

- Choose the desired job name and select the job frequency as **Recurring**. Set this job as **Continuous** from the **Choose Frequency** section. 

![Dataworkz job frequency setitngs screen][23]

### Step 8: monitoring/maintaining the sync job 

The sync job that was scheduled in the previous step can be viewed under **Jobs** -> **Continuous Job**” in the Dataworkz portal. It provides the following: 

- job status (running/deleted/paused) 
- ability to pause a running job 
- ability to resume a paused job 
- ability to delete a continuous job

![Dataworkz sync job summary screen][24]

## Conclusion

MongoDB Atlas Device Sync streamlines and automates the challenges of mobile data synchronization with Atlas. By integrating with Dataworkz, that same data can now be automatically and bi-directionally synchronized with MySQL, Oracle and other databases.

Set up your account at [cloud.mongodb.com](https://www.mongodb.com/products/platform/cloud) and [dataworkz.com](https://www.dataworkz.com/) to create accounts and begin your automated bi-directional data synchronization journey.


  [1]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt85b18bd98135559d/65c54454245ed9597d91062b/1.jpg
  [2]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt0f313395e6bb7e0a/65c5447cf0270544bcea8b0f/2.jpg
  [3]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt42fc95f74b034c2c/65c54497ab9c0fe8aab945fc/3.jpg
  [4]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt0bf05ceea39406ef/65c544b068e9235c39e585bc/4.png
  [5]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt852256fbd6c92a1d/65c544cf245ed9f5af910634/5.png
  [6]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt6f7a22db7bfeb113/65c544f78b3a0d12277c6c70/6.png
  [7]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt732e20dad37e46e1/65c54515fb34d04d731b1a80/7.png
  [8]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltc1f83c67ed8bf7e5/65c5453625aa94148c3513c5/8.png
  [9]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltcfe7cf5ed073b181/65c5454a49edef40e16a355a/9.png
  [10]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt99688601bdbef64a/65c5455d211bae4eaea55ad0/10.png
  [11]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt7317f754880d41ba/65c545780acbc5455311135a/11.png
  [12]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt690ff6f3783cb06f/65c5458b68e92372a8e585d2/12.png
  [13]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt0810a0d139692e45/65c545a5ab9c0fdc87b9460a/13.png
  [14]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt39b298970485dc92/65c545ba4cd37037ee70ec51/14.png
  [15]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt6596816400af6c46/65c545d4eed32eadf6ac449d/15.png
  [16]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt4eaf0f6c5b0ca4a5/65c545e3ff4e591910ad0ed6/16.png
  [17]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt9ffa8c37f5e26e5f/65c545fa4cd3709a7870ec56/17.png
  [18]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt1678582be8e8cc07/65c5461225aa943f393513cd/18.png
  [19]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltd4b2f6a8e389884a/65c5462625aa94d9b93513d1/19.png
  [20]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltbd4a6e31ba6103f6/65c5463fd4db5559ac6efc99/20.png
  [21]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt6e3be8f5f7f0e0f1/65c546547998dae7b86b5e4b/21.png
  [22]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/bltd2fccad909850d63/65c54669d2c66186e28430d5/22.png
  [23]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blted07a5674eae79e0/65c5467d8b3a0d226c7c6c7f/23.png
  [24]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt8b8b5ce479b8fc7b/65c5469125aa94964c3513d5/24.png

Learn how to set up automated, automated, bi-directional synchronization of data from mobile devices to MongoDB Atlas  and Google Cloud MySQL.

Timothy Marland

Content by Timothy Marland

Synchronize Your Mobile Application With MongoDB Atlas and Google Cloud MySQL