Replicate an Oracle Table to SQL Server

January 2021
Enzo DataZen





In this blog we are going to demonstrate how to replicate an Oracle table to a SQL Server database using ENZO DataZen.



ENZO DataZen Overview

DataZen is a replication technology that can detect changes made to any source system through a proprietary Change Data Capture (CDC) engine. Because the CDC engine built into DataZen is universal by design, it can detect changes (adds, updates, and deletes) regardless of the source system (relational, no-sql, or any API exposed through ENZO Server or an ODBC driver).



Job Types

There are three kinds of Sync jobs in DataZen: Jobs Readers, Job Writers, and Direct Jobs.

  • Job Readers: A Job Reader runs a SQL command against the source system, and creates a universal Sync File (also called a Change Log) that can be applied to any target system at a later time. The Sync File may contain all the records (the first time the job is run), or only records that have changed since the last run.

  • Job Writers: A Job Writer reads one or more Sync Files and applies them to a target system. Multiple Job Writers can consume the same Sync Files, allowing the replication of a source system to multiple target systems. It is also possibly to replay past Sync Files at any time. Separating the Job Writer from the Job Reader also allows replication to take place across networks and data centers, or even between two corporations through a shared cloud drive.

  • Direct Jobs: A simple job types that combines a Job Reader and a Job Writer. Direct Jobs also create temporary Sync Files, but these files are deleted as soon as they have been applied to the target system. As a result, Direct Jobs do not allow multicasting nor replay capabilities.

ENZO DataZen replicates data using an eventual consistency model. This means that the data at the target system will eventually match data at the source.

Source Systems

DataZen can natively connect to any relational database through an ODBC driver. In addition, certain scripts can be automatically generated for some of the well-known database platforms (Oracle, MySQL, Teradata, and SQL Server).

In addition to using ODBC drivers, DataZen can also access virtually any platform directly using SQL commands through Enzo Server without the need for an ODBC driver. For example, if you currently have ENZO Server configured to access SharePoint Online, you can instantly replicate SharePoint list data to any other system with DataZen.



Replicate an Oracle Table

We will be using Oracle as the source system, and will demonstrate how to setup a Direct Job replication. As discussed previously, Direct Jobs combine both a Job Reader and a Job Writer into a single operation for simpler replication topologies.

To create a Direct Job, start DataZen, click on the desired Agent (at least one agent must be registered), and select NEW -> Direct Data Sync Job from the context menu.

Configure the Data Source

Enter a Job Name, and specify a connection string to the source system. You can click on build... to help construct a valid connection string. Enter a valid SQL statement for Oracle and click on preview. This is necessary to ensure the connection string is valid and to help build the rest of the job.

The statement specified will be wrapped by another SELECT operation so that only a few records are returned at a time. If you enter a complex SQL command that cannot be wrapped into a SELECT operation (such as if you call a stored procedure), check the Bypass TOP/LIMIT N option under the Advanced Settings tab to disable heuristics.

Configure Replication Settings

Click on the Replication Settings tab and select a Key field (in this example, we picked EMPLOYEE_ID). You may select more than one field as the Key field if needed. To propagate delete operations, check the Identify and propagate deleted records.

Choose a replication schedule (as a Cron expression), and select a folder in which the Sync files will be created.

Choosing a Timestamp/Date Column speeds up the identification of modified records significantly. It is only available if the SQL command is a SELECT operation.

Configure the Target System

Click on the Target System tab and specify a connection string. In this example, we are using a SQL Server Native connection; as a result the High Performance MERGE Operation option is available (this operation offers signficant performance advantages for SQL Server).

Enter a target table in the target database. The table should be in the following format for SQL Server: [schemaname].[tablename].

To minimize any errors in the creation of the target table, including possible data type convertion issues, click on the View/Run Table Script button. A screen will allow you to review the SQL command used to create the target table and execute the command that will create the target table. Enzo will automatically select the correct data type for the selected target system.

It is highly recommended (required when the targer system is Oracle or MySQL) to choose the Add an index to cover Key Columns. If the target system is Teradata, use a PRIMARY KEY on the Key Columns.

Click on Execute Command Now to create or recreate the target table.

Click Close and then click OK to create the Direct Job.

Monitor the Job

If you have selected a Schedule (Cron Expression) the job will start immediately; otherwise you will need to click on the Start icon for the job. The example provided selects records from a small table in Oracle (107 records in all), so the complete operation takes just a few seconds.

You can see the details of the operation (read and write) from the Output Log at the bottom of the screen.



The Last Execution window shows the last available operation statistics that actually performed a change. The following key information is available:

  • Records Read: Number of records read from the source system
  • Records Deleted: Number of deleted records detected since the last execution
  • Records Available: Total number of records available for replication (inserted, updated, deleted)
  • Source Execution Time: Time taken to select records from the source system
  • Map Reduce Time: Time taken to identify changed and deleted records
  • Target Upserted Records: Number of records updated and/or inserted into the target system
  • Target Deleted Records: Number of records deleted from the target system
  • Target Execution Time: Time taken to apply changes to the target system


Conclusion

This blog shows you how you can easily replicate database tables across two different relational database engines, specifically from Oracle to SQL Server. DataZen provides the necessary logic to make replication quick and easy, including data type mapping, automatic retries, and advanced heuristic options for large tables.

To try DataZen at no charge, download the latest edition of DataZen on our website.









NEW !!! INTRODUCING DATAZEN

Secured Corporate, Cloud and B2B Data Replication

  LEARN MORE   CONTACT US





Any Source

Combined with Enzo Unified, DataZen allows you to replicate data from virtually any source system, including Twitter, SharePoint Online lists, flat files, NoSQL data, or any ODBC data sources.



Flexible Cross Database Replication

Replicate tables between Oracle, MySQL, SQL Server, Teradata, DB2 or any other relational database, either located on the same network, or across the globe.




Secured B2B Data Replication

Securely share specific tables/views from any internal database with business partners using PGP encryption and cloud drives.



Cloud Adoption

By detecting changes at the source, DataZen forwards only new, updated and deleted records to the target systems, saving bandwidth and helping with cloud adoption.







To learn more about configuration options, and to learn about the capabilities of DataZen, download the User Guide now.

  USER GUIDE