Share via

  • Article

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.

    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.

    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:

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.

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: