Integrating Heterogeneous Data
This topic is a brief introduction to using Microsoft SQL Server Replication technology to integrate data between SQL Server databases and supported Oracle and IBM DB2 databases.
Many businesses and organizations have vital data stored in databases from multiple vendors. When an organization begins to automate its processes it is often necessary for data in disparate databases to be compared and shared with other processes. In many instances the database systems are not directly compatible and often do not all use the same operating system. This scenario is commonly referred to as a heterogeneous data environment.
SQL Server provides features, such as Integration Services and PowerPivot, that can help you integrate heterogeneous data. Another way to integrate heterogeneous data is to use SQL Server's Replication features to replicate data from server to server. SQL Server supports heterogeneous data replication with certain Oracle and IBM databases.
Replication allows you to integrate heterogeneous data in several ways:
You can use an Oracle database as a publisher for data that can be replicated to SQL Server databases.
Use SQL Server as a publisher or distributor for data that can be replicated to IBM and Oracle databases. The following diagram illustrates replication from a SQL Server publisher to IBM DB2 and Oracle subscribers.
.gif "Replicating data to non-SQL Server databases")
Data that is published from Oracle to a SQL Server subscriber can then be distributed from SQL Server to Oracle or IBM subscribers. In this scenario the data is first replicated to a SQL Server database where it can then be replicated to other databases including SQL Server, IBM DB2, and Oracle.
Note
Direct replication from Oracle to IBM or Oracle subscribers is not supported by SQL Server.
The following diagram illustrates replicating data from an Oracle database to other databases. Some or all of the data at the Oracle database is included in the publication. The data is replicated to the first SQL Server (that is configured as a Distributor) and then distributed to the other SQL Server, and the IBM and Oracle databases. Each of these databases is a Subscriber to the publication, receiving schema and data as a subscription.
.gif "Replicating data from Oracle")
The following constraints apply to SQL Server replication in a hetrogeneous topology:
Transactional and Snapshot replication are supported. Merge replication is not supported.
You can use either push or pull subscriptions. Peer-to-Peer subscriptions are not supported.
An Oracle database can be a publisher or a subscriber in relation to SQL Server.
IBM databases are always subscribers in relation to SQL Server. Publishing from IBM DB2 to SQL Server is not supported.
For more information about replication in a hetrogeneous topology, see Heterogeneous Database Replication.
Choosing the Type of Replication to Use
SQL Server offers different types of replication for different heterogeneous application requirements:
If your application requires the replication of incremental changes as they occur, use transactional replication.
For Oracle publishing, transactional replication tracks changes at the Publisher using triggers and change tracking tables. For more information about transactional replication, see Transactional Replication Overview, How Transactional Replication Works, and Transactional Replication Workflow for Oracle Publishers.
If your application requires that data be replicated infrequently or that it be updated periodically rather than incrementally, use snapshot replication.
Because snapshot replication does not track and deliver incremental changes, triggers are not used on published tables. For more information about snapshot replication, see Transactional Replication Overview and How Transactional Replication Works.
Both snapshot and transactional replication address the principal requirements for many heterogeneous integration scenarios:
Replication between databases from different vendors
Transactional consistency
Minimal overhead
Transactional replication addresses additional requirements for systems that require incremental updates:
Low latency
High Throughput
Steps for Implementing a Heterogeneous Integration Solution
Click the links below for more information.
Oracle Publishing:
Oracle and IBM DB2 Subscribers:
After the subscription is initialized and data is flowing between the Publisher and Subscribers, you might need to consult the following topics for information on common administration and monitoring tasks: