Skip Headers
Oracle® Application Server CDC Adapters for SQL Server User's Guide
11g Release 1 (11.1.1)

Part Number E16093-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Introduction

Change data capture (CDC) is an integration technology that enables efficient near real-time integration by capturing changes made to enterprise data sources and providing them for processing in integration middleware, such as Oracle Fusion Middleware.

The Oracle CDC Adapter for SQL Server provides such capabilities for SQL Server 2000 and 2005, capturing changes in a non-intrusive manner leveraging the SQL Server Transaction Logs (TLOG). The CDC Adapter can be used by Oracle SOA Suite (BPEL, ESB), and Oracle Data Integrator (ODI).

This chapter provides an overview of the features and architecture of the OracleAS CDC Adapter for SQL Server. It includes the following topics:

OracleAS CDC Adapter for SQL Server Overview

The OracleAS CDC Adapter for SQL Server captures and delivers changes (such as insert, update, and delete operations) made to tables in SQL Server 2000 and 2005. By using the CDC Adapter, you can build efficient data integration solutions that use SQL Server data, synchronize systems to ensure data integrity, update data warehouses and operational data stores in near real-time, and enable event-driven business activity monitoring and processing.

The OracleAS CDC Adapter for SQL Server has the following capabilities:

Using the OracleAS CDC Adapter for SQL Server makes ETL (extract, transform, and load) processes more efficient and less disruptive to the operation of the source database systems, eliminating the need for the traditional ETL downtime window

OracleAS Adapter for SQL Server CDC Agent Architecture

The OracleAS CDC Adapter for SQL Server CDC agent uses the normal SQL Server transactin logs (TLOG) and a TLOG miner to extract the necessary data and place it into the staging area where it is available for consumption by BPEL/ESB. The following diagram shows this process:

Figure 1-1 SQL Server CDC Agent Architecture

SQL Server CDC agent architecture

The agent is a part of the overall OracleAS Adapter architecture that is described in OracleAS Adapter for SQL Server CDC Agent Architecture. The following describes the components shown in the diagram:

OracleAS CDC Adapter for SQL Server Architecture

The following figure illustrates the system components used for change data capture using the OracleAS CDC Adapter for SQL Server.

Figure 1-2 Component Architecture used with the SQL Server CDC adapter

Architecture of the Oracle CDC Agent for IMS/DB.

The OracleAS CDC Adapter for SQL Server component architecture includes the following components:

The Staging Area

The staging area is an area used by Oracle Connect to store captured data from a journal. The journal is scanned once and changes for every required table read during that scan are passed to the staging area, where they are stored. This means that the journal is scanned once each time it is polled. After the changes are written to the staging area, processing of these changes is carried out independently of the journal.

The staging area is a benefit when transactions are used. The changed data is not written to the change queue until the transaction is committed. Thus, if the transaction fails, there is no overhead of having to back out any processing done with the steps in the failed transaction.

The staging area can be on any computer with a Windows, Linux, or UNIX operating system that is running the Oracle Application Server and does not need to be run on the same server as the SQL Server CDC adapter. Once the information is extracted from the journal and written to the staging area, processing of changes is carried out on the staging area only. Thus, the staging area should be set up to consider the network configuration and where the consumer application runs.

The staging area maintains the last position read by the consumer application (the staging area context) and starts at this point the next time a request from the consumer application is received. The context is stored in the repository where the staging area is maintained.

The staging area is indexed so that access to the staging area for a specific stream is quick.

The staging area is cleared by default every 48 hours. All events that have been in the staging area for more than 48 hours are deleted.