How to Achieve Continuous Data Sync from Oracle RDBMS to MongoDB/Atlas

Before we delve into replicating data to MongoDB, let's briefly touch on why MongoDB stands out. MongoDB is undeniably one of the most successful modern databases today. While traditional databases like Oracle, Sybase, and Postgres SQL have been around since the 80s or 90s, MongoDB has emerged as a leading choice in the past two decades. With over 47,000 customers and a monthly influx of over 140,000 developers into its community, MongoDB boasts unparalleled popularity.

The most crucial aspect that retains developers within the MongoDB ecosystem is its flexible document model. In addition, MongoDB distinguished itself as one of the pioneering solutions with scalable, sharding architecture, enabling efficient handling of large volumes of data and traffic while maintaining high performance. These features played a significant role in its widespread adoption. Fast forward to today, MongoDB has evolved into a mature product database after more than 15 years since its debut, with a vast user base. Our focus today will be on data replication into MongoDB.

Firstly, both customers are not seeking to replace or rewrite their existing applications; instead, they intend to continue operating them while exploring new innovations. Secondly, they are leveraging modern database platforms like MongoDB Atlas for their new applications.

The primary challenge lies in ensuring seamless access to business data from legacy applications for these new applications. This necessitates ongoing synchronization of data between the two systems until a decision is made to decommission the current applications, which could be several years down the line or may not even be part of the plan. Therefore, a robust solution is required to facilitate continuous data movement and synchronization between these two environments.

What Are The Common Approaches?

What are the common approaches to addressing this problem? There are several methods, but two are widely adopted by organizations.

The first approach involves adding an API layer to the existing systems to expose the data to new applications. This method provides a straightforward means of accessing the data without directly modifying the legacy application.

The second approach is known as the dual-write method. With this approach, data is written not only to the current or legacy database but also to the new databases simultaneously. This ensures that both systems remain synchronized with the latest data.

Now, let's examine these two options:

① Adding API Layer to Existing System

As shown in the figure above, the first option involves setting up a new API service to expose the data from the current application running on Oracle to the new applications on the right side. To do this, we would develop a new API service, typically using Java or Python. Java is quite common and offers a quick setup process along with ease of design and implementation. Once the API component is in place, new applications can query it directly using HTTP requests or RESTful API methods. This approach is straightforward and often feasible to implement in-house, provided there are developers available to work on it.

However, there are some considerations to keep in mind. Introducing this new workload to the existing database, which may not have been provisioned or fine-tuned for this purpose, could potentially impact the performance of your application. Additionally, designing, developing, testing, and launching the new API service may require a significant amount of time. Furthermore, ongoing maintenance, monitoring, and potential performance issues with large queries impacting existing applications need to be addressed. These are important factors to consider when opting for this approach.

② Dual Writes to New Databases & Message Queue (Kafka)

Another widely-used approach, especially for multiple use cases, involves employing a messaging queue to route data or events between systems. Kafka is often chosen as it is a widely popular and reliable messaging solution for modern architecture.

The process typically entails modifying legacy applications to not only write data to the current database (e.g., Oracle) but also publish data changes (e.g., inserts, updates) using the Kafka API, sending them to a Kafka message topic. This enables the replication or distribution of changes to multiple downstream applications.

While this solution offers benefits in terms of data distribution and scalability, it also introduces complexities and overhead in managing the integration process. Implementing this approach requires significant time to modify existing applications and often entails ongoing maintenance. Integrating it into mission-critical workflows can be challenging due to potential risks and added complexity to the critical path. Additionally, additional coding work is involved, not only in modifying the original applications but also in writing consumer code to pull events from the Kafka message queue and possibly writing logic to insert data into MongoDB, as in our case. Along the way, various challenges such as network timeouts or errors may arise, requiring extensive error handling code. The consumer code also typically needs to handle the breakage and resumption, a very delicate matter indeed to manage.

Overall, while this solution offers benefits, it also requires careful consideration of its complexities and potential challenges during implementation.

So, how do we do it? As illustrated in the diagram above, at the bottom represents your current application systems, typically running on popular relational databases like Oracle, MySQL, or SQL Server.

With TapData Live Data Platform, deploying and connecting is straightforward. Whether you choose our on-premises solution or opt for our cloud version, you simply connect or provision it. No extra coding is required; just provide the necessary credentials. We then leverage database log monitoring to instantly convert change events into streaming streams effortlessly.

These events come complete with payload and full record data for each row, allowing for optional filtering or transformation. Alternatively, you can proceed without any modifications.

Next, you have the flexibility to move this data to your desired destination. Whether it's sending it to Kafka for multiple consumers or storing it in modern databases like MongoDB or data warehouses for analytics, the possibilities are vast.

At the core of this capability is our Change Data Capture (CDC)-based data pipeline. In the next part, we'll delve into how it works.

Change Data Capture and Real Time Transformation

A variety of Built-in CDC Connectors

To clarify, CDC capability is essential for capturing and processing real-time data changes from various databases. TapData simplifies this process by offering more than 60 pre-built CDC connectors, with which you can effortlessly capture data changes without the need for manual setup.

For most common databases like Oracle, SQL Server, PostgreSQL, Sybase, and DB2, we already have connectors available. You simply need to activate them, and you can instantly start capturing CDC events. Additionally, we support event capture from other databases, as well as from Kafka, MQs, or even SaaS APIs. Our aim is to offer a comprehensive solution that empowers you to access real-time data from any of your data sources seamlessly and efficiently.

Comprehensive MongoDB Data Model Support

When it comes to transferring data, especially concerning our discussion here about migrating data from a relational database to the modern Scalable Document Database, like MongoDB or MongoDB Atlas. MongoDB has a unique data model known as a denormalized document model, which differs significantly from the traditional relational table structure. This model allows for nested sub-documents and arrays, offering developers an intuitive way to represent complex real-world data.

Hence in our use case, the challenge lies in mapping data from, for example, Oracle to a modern database like MongoDB, bridging the gap between relational and document-oriented models. TapData addresses this challenge with a key capability specifically designed to handle such complex scenarios.

• Relational to Document model, many to one real time merging
• Embedded Array/Document
• Automatically Insert / Update / Delete in sub documents

It seamlessly converts tables one-to-one or many-to-one. In MongoDB, best practices often involve designing a denormalized data model, consolidating multiple tables into a single collection. With TapData, you can replicate data from relational databases while also having the flexibility to combine, merge, or consolidate them according to your needs. And the final looking is a denormalized MongoDB collection that aligns with MongoDB's document-oriented approach.

Conclusion