Synchronizing a Synapse table from an on-premise Oracle table

Question

Synchronizing a Synapse table from an on-premise Oracle table

pmscorca 1,052

Hi,

I've a Synapse table on a dedicated SQL pool and I need to update it reading from an Oracle table.

I've already implemented a Self-hosted IR and I've already read from the Oracle table rightly with the Oracle connector.

The requirement from my customer is to synchronize the Synapse table when a data change occurs for the Oracle table.

So I cannot use a scheduled trigger for the ADF pipeline that reads from the Oracle table.

Now, any suggests to me to solve this issue, please? Does it exist any property for the Oracle connector in Synapse that could help me?

Thanks

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-06-11T08:15:05.39+00:00

@pmscorca Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

0 additional answers

Your answer

phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-06-11T08:15:05.39+00:00

@pmscorca Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

phemanth 15,765 Microsoft External Staff Moderator

@pmscorca

Thanks for using MS Q&A platform and posting your query.

There are a couple of approaches you can take to synchronize the Synapse table with the Oracle table upon data changes in Oracle, without relying on scheduled triggers:

1. Change Data Capture (CDC):

Oracle GoldenGate: If you're using Oracle GoldenGate for CDC in your Oracle environment, you can leverage it to capture changes in the Oracle table and replicate them to your Azure Synapse Analytics dedicated SQL pool. GoldenGate provides real-time or near real-time data integration capabilities.

2. Azure Data Factory (ADF) with Triggers:

SQL Database triggers: While you can't use scheduled triggers in ADF, you can set up triggers directly on the Oracle table. These triggers can then call an ADF pipeline that reads the changed data and updates the Synapse table. This approach requires some development effort on the Oracle side to create the triggers.

3. Azure Synapse Pipeline with Monitor:

Monitor existing data: You can create an ADF pipeline that monitors the Oracle table for changes. This pipeline could leverage tools like Azure Logic Apps or custom code to periodically check for updates in the Oracle table. However, this approach wouldn't be real-time and might introduce some latency in synchronization.

Important points to consider:

Data volume and latency: Choose the approach that best suits your data volume and latency requirements. CDC solutions offer near real-time updates, while trigger-based approaches might have some delay.
Development effort: CDC tools and Oracle triggers require more development effort compared to a simple monitoring pipeline.
Cost: Evaluate the cost implications of each approach, including any licensing fees for third-party CDC tools.

Oracle Connector Properties:

Unfortunately, the Oracle connector in Azure Synapse doesn't have a built-in property for change data capture. However, you can leverage the connector to read data from the Oracle table within your chosen synchronization approach.

Hope this helps. Do let us know if you any further queries.

pmscorca 1,052 Reputation points

2024-06-06T21:43:15.97+00:00

Hi, many thanks for your reply.

Using CDC or an Oracle trigger should mean a cost for my customer in terms of development effort and/or money.

Synchronizing the Synapse table in real-time is an important requirement.

It could be useful to increment the schedule frequency of the pipeline trigger.

Implementing a monitoring pipeline by using Azure Logic Apps should mean a significant development effort.
pmscorca 1,052 Reputation points

2024-06-06T21:47:01.5366667+00:00

Hi, many thanks for your reply.

Using CDC or an Oracle trigger should mean a cost for my customer in terms of development effort or money.

It could be a good idea to increment the schedule frequency of the pipeline trigger.

Implementing a monitoring pipeline should mean a significant development effort.
pmscorca 1,052 Reputation points

2024-06-07T08:21:46.9333333+00:00

Hi, is the Oracle connector integrated in ADF/Synapse and not required any installations on Oracle on-prem?
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-06-07T09:37:47.2533333+00:00
@pmscorca

Yes, the Oracle connector in ADF and Synapse is fully integrated. You don't need to install any components directly on your on-premises Oracle database server. Here's a breakdown of the setup process:

Install the Oracle Instant Client: This lightweight software library provides the necessary drivers for ADF/Synapse to communicate with your Oracle database. You'll need to install the appropriate Instant Client version on the ADF integration runtime (IR) that interacts with Oracle. There are two options for the IR:

Managed IR: This is the default, cloud-based IR managed by Microsoft. It already has the Oracle Instant Client pre-installed, so no additional setup is required on your end.

Self-hosted IR: If you choose a self-hosted IR for more control or to access on-premises data sources, you'll need to install the compatible Oracle Instant Client version on the machine where the IR is hosted.

Configure the Oracle Linked Service: Within ADF/Synapse, you'll create a linked service that defines the connection details for your Oracle database. This includes specifying the server's name, port, service name (SID), and authentication credentials. The Oracle connector uses the information in the linked service to establish a secure connection to your Oracle database.

Key Points:

Simplified Setup: The integration between ADF/Synapse and the Oracle connector eliminates the need for complex installations or configurations on your Oracle server.

Flexibility: You can choose between managed and self-hosted IRs based on your deployment scenario and security requirements.

Secure Communication: The Oracle connector uses industry-standard protocols and authentication mechanisms to ensure secure data transfer between ADF/Synapse and your Oracle database.

By following these steps, you can leverage the Oracle connector in ADF/Synapse to seamlessly extract, transform, and load (ETL) data from your on-premises Oracle database to your Azure Synapse table.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
pmscorca 1,052 Reputation points

2024-06-07T11:12:07.4533333+00:00

Hi,

in order to access to an on-premise Oracle source I've already installed a Self-hosted IR on a on-premise separate vm respect to the Oracle machine.

Moreover, I've already implemented some ADF pipeline to read from/write to Oracle using the Oracle (ODBC) connector of ADF successfully.

I haven't installed any Oracle Instant clients on the SHIR machine but the ADF pipelines function without any issues.

In the MS documentation isn't written that it occurs to install an Oracle Instant Client on the SHIR machine.

Why installing a such client on the SHIR machine? Does this client allow to capture a change on an Oracle table? Thanks
phemanth 15,765 Reputation points Microsoft External Staff Moderator

2024-06-10T07:22:56.75+00:00
@pmscorca

You're absolutely correct. The Microsoft documentation might not explicitly state the need for Oracle Instant Client on the Self-Hosted Integration Runtime (SHIR) machine for all scenarios, but it plays a crucial role in enabling communication between ADF and your Oracle database. Here's why:

Oracle Instant Client:

It's a lightweight set of libraries that provide the necessary drivers for ADF to interact with your Oracle database.

Even though ADF uses the Oracle (ODBC) connector, this connector still relies on the underlying Instant Client libraries to translate data access requests and communicate with the Oracle server.

SHIR and Instant Client:

While the documentation might not explicitly mention it for all cases, installing the Oracle Instant Client on the SHIR machine is essential for most scenarios when using a Self-Hosted IR to access Oracle.

This is because the SHIR acts as the bridge between ADF and your on-premises data sources. It needs the appropriate drivers to understand how to talk to the Oracle database.

Change Data Capture (CDC):

The Oracle Instant Client itself doesn't have functionalities for capturing changes in Oracle tables.

CDC is a separate concept that refers to techniques for identifying and tracking data modifications within a database.

As mentioned in the previous response, you can leverage Oracle GoldenGate for CDC capabilities if available in your environment.

In summary:

For successful data access from Oracle using a Self-Hosted IR, installing the compatible Oracle Instant Client version on the SHIR machine is necessary.

The Instant Client provides the communication bridge between ADF and Oracle, not directly enabling CDC.

Explore Oracle GoldenGate or other CDC solutions for real-time data change capture if needed.

Here are some additional resources that you might find helpful:

Copy data to and from Oracle - Azure Data Factory & Azure Synapse: https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle

Azure Data Factory Oracle connection through self Hostes IR using TNSNAMES: https://learn.microsoft.com/en-us/azure/data-factory/concepts-integration-runtime

Share via

Synchronizing a Synapse table from an on-premise Oracle table

0 additional answers

Your answer