DBA > Articles

Replicating Transactions Between Microsoft SQL Server and Oracle Database Using Oracle GoldenGate

By: Nikolay Manchev
To read more DBA articles, visit http://dba.fyicenter.com/article/

Build a simple transaction replication example that spans these platforms, step by step.

Published August 2011
Most Oracle technology professionals who are interested in data replication are familiar with Oracle Streams. Until 2009, Streams was the recommended and most popular Oracle technology for data distribution.

In July 2009, Oracle acquired GoldenGate, a provider of database replication software. The company is now encouraging its customers to use Oracle GoldenGate (which is part of the Oracle Fusion Middleware family) for their data replication needs in new applications. Oracle's statement of direction regarding Oracle Streams says that product “will continue to be supported, but will not be actively enhanced.”

In this article we will build a simple transaction replication example using Oracle GoldenGate, in order to get acquainted with this new technology. Oracle GoldenGate Architecture

GoldenGate v11 enables transaction level replication among heterogeneous platforms. It supports Oracle Database, IBM DB2, Microsoft SQL Server, MySQL, Teradata, and many other platforms. (It also supports access through a generic ODBC driver.)

The most important components that we need to be familiar with are the Extract and Replicat processes. The Extract process runs at the source system and captures the data changes. The Replicat is running at the target machine and is responsible for applying the changes to the target database.

There are two common configurations for the Extract process. The so called “initial load” is used for populating the target database with an exact copy of the source data (i.e. Extract is fetching all data from the source database and typically runs only once). Then the “change synchronization” can take place. In “change synchronization” configuration the Extract is constantly monitoring the source database and captures all changes on the fly.

In this demonstration we will setup a Microsoft SQL Server 2008 as a source database, configure and perform an initial load and then start an Extract process in a change synchronization mode. In order to show that this replication is truly heterogeneous, we will run SQL Server on Windows XP and Oracle Database 11g Release 2 on Oracle Linux 5. As a prerequisite I will assume that you already have a clean installation of SQL Server 2008 on the Windows box and Oracle Database on the Linux machine.

We will start building the demonstration scenario by installing GoldenGate. Let's start with the Windows box.
GoldenGate for SQL Server Installation on Windows XP
First you need a copy of Oracle GoldenGate v11 for SQL Server. You can download it from http://edelivery.oracle.com (Oracle Fusion Middleware ? Microsoft Windows x32 ? Oracle GoldenGate for Non Oracle Database v11). The serial number of the media pack that you need is V22241-01.

Full article...


Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/