jump to navigation

Oracle to SQL Server Replication-A bit of insight May 13, 2013

Posted by msrviking in Configuration, Data Integration, Heterogeneous, Integration, Replication.
Tags: , , , , , ,
add a comment

In last couple of posts in the series, I had shared an approach and also appropriate pre-requisites for setting up an Oracle Publisher based Transactional Replication setup. Today in this post, I shall talk about how different it is to replicate data from SQL Server and Oracle.

Just before I started working on this assignment I recollected several of my past experiences where I had configured SQL Server Replication setups in all the topologies, and based on that I safely presumed that it won’t be that different with Oracle Publisher too. Well, I was partly right, SQL Server replication setup was, and is easy because the wizard helps one to configure publisher, distributor and replication quickly. I would think and also voice that its same for what I had worked on in this assignment except that I had to establish successful connection with Oracle and then let SQL Server do its job of configuring the publisher, distributor and Transactional Replication (pull-subscription).

The difficult part was having the appropriate Oracle client and OLE DB drivers installed, and please refer to my post which has links to the downloads from Oracle Technology Network (OTN).

I would like to share few internals on Oracle Publication from SQL Server, and without this the significance of configuration such type of publication wouldn’t be much.

1. Configuring Oracle Publisher is a new feature starting from SQL Server 2005, and works with Oracle 9i onwards.

Before SQL Server 2005, I remember well we had used SQL Server 2000 DTS packages to pull data to synchronize data between SQL Server and Oracle, and this way got replaced with Oracle Publication using Replication, and this is nice. The use of DTS package wasn’t straightforward because we needed few staging tables to capture the data because of DML change in Oracle published (source tables) and these were to be propagated into SQL Server tables through an UPSERT method. Although this worked well, but had the whole bunch of problems associated initially, before the system settled with that type of data changes that need to be captured. And the with this new feature in SQL Server 2005 I think we have similar setup happening, but everything is handled by SQL Server, and that’s why I said its pretty neat and nice.

2. Data from Oracle could be synched up with SQL Server either through Snapshot Replication or Transactional Replication.

Please note the difference in here between these two type of Replications when configured with Oracle Publisher. At a configuration and working level its almost similar with SQL Server Publisher yet I thought I will put up couple of notable points for anyone’s quick reference.

– A snapshot replication takes snapshot of published tables and applies them as a whole on the subscription end. Simple, eh, and this is run by a Snapshot Agent job which gathers the table data in the form of schema scripts, and publishes for subscriptions to consume. No “change tracking triggers” are created on the publisher tables.

– A transactional replication tracks change in each row of the published tables, through “triggers” created on the tables. The triggers that are created on published tables captures the DML changes and pushes the data into change tracking table and the Log Reader agent reads, and moves the changed data for publishing to subscribers through an intermediary Distribution database.

So transactional replication creates database triggers on the published tables and data is tracked into change tracking tables on the Oracle end. The changed data then is read into distribution database through Log Reader Agent running on SQL Server. This link has details on how the whole process happens, but let me share a snapshot of the tables that are created on the Oracle database.

Here are few snapshots of the system tables, change tracking tables and the triggers created when Oracle Publisher is configured.

Figure.1.

Replication tables created on Oracle schema

ScreenShot003

Figure.2.

Change Tracking Tables to track changes of data on published tables

ScreenShot004

Figure.3.

Trigger that is created on the publisher table

ScreenShot002

With this I am thinking you should be well briefed on what happens when an Oracle Publisher is configured with Transactional Replication. Of course, I didn’t talk much on the Snapshot Replication with any pics, because I couldn’t setup one during this assignment.

In the next post I will walk you through configuration of Oracle Publisher, Distributor.

Until then happy reading, and please feel free to comment.

Cheers!

Advertisements