Integrating Heterogeneous Data
Many businesses and organizations have data stored in databases from multiple vendors. Integrating this data is often a key component in allowing systems to work together in an organization. Replication allows you to integrate heterogeneous data in two ways:
- Use Oracle as a source for data that can be replicated to Microsoft SQL Server, IBM, and Oracle databases.
- Use SQL Server as a source for data that can be replicated to IBM and Oracle databases.
The type of replication configuration used for integrating heterogeneous data depends on the source and destination(s) for the data:
- The following diagram illustrates replicating data from SQL Server to IBM DB2 and Oracle.
- The following diagram illustrates replicating data from an Oracle database to other databases. The data is first replicated to a SQL Server database and can then be replicated to other databases including SQL Server, IBM DB2, and Oracle.
Adventure Works Cycles Example
Adventure Works Cycles is a fictional manufacturing company used to demonstrate database concepts and scenarios. For more information, see Samples and Sample Databases.
Adventure Works Cycles acquired the Mexican company Importadores Neptuno recently in an effort to expand their infrastructure to support the expected growth of the company. Importadores Neptuno uses an Oracle database to maintain manufacturing and financial data. However, core elements of that data must be shared in order to maintain accurate scheduling and inventory data in Adventure Works Cycles' Manufacturing Resource Planning (MRP) application.
While there is no current plan to migrate the Importadores Neptuno databases, Adventure Works Cycles does need to transmit and receive data on a daily basis and transfer that information into their existing SQL Server online transaction processing (OLTP) and online analytical processing (OLAP) databases. Adventure Works Cycles will replicate data from the Oracle database to SQL Server databases at the central office.
Common Requirements for This Scenario
Applications that involve integrating heterogeneous data typically have the following requirements, which an appropriate replication solution must address:
- The system must allow data to be replicated between databases from different vendors.
- The system must maintain transactional consistency.
- Replication processing should require minimal overhead on the source server.
- The system should have low latency if the replication of incremental changes is required.
- The system should have high throughput if the replication of incremental changes is required: it should handle the replication of a large number of transactions.
- The data required at the destination servers might be a subset of the data available at the source server.
The Type of Replication to Use for This Scenario
SQL Server uses a publishing industry metaphor to describe the components of the replication system. The components include the Publisher, Distributor, Subscribers, publications and articles, and subscriptions.
- In the first diagram above, the Oracle database is the Publisher. Some or all of the data at the Oracle database is included in the publication, with each table of data being an article. The data is replicated to the first SQL Server (which 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.
- In the second diagram above, the SQL Server database is the Publisher, and the IBM and Oracle databases are the Subscribers.
For more information on the components of the system, see Replication Publishing Model Overview.
SQL Server offers different types of replication for different application requirements: snapshot replication, transactional replication, and merge replication. This scenario is best implemented with the heterogeneous replication features of snapshot replication and/or transactional replication, which are well suited to handle the requirements outlined in the previous section:
- 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 only once (for example when migrating data) 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.
By design, snapshot and transactional replication address the principal requirements for this scenario:
- 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
The primary option to consider for this scenario is filtering. Snapshot and transactional replication allow you to filter columns and rows, so the tables at Subscribers contain only the data required by your application. For more information, see Filtering Published Data.
Steps for Implementing This Scenario
To implement these scenarios, you must first create a publication and subscriptions, and then initialize each subscription. 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:
- Monitoring Replication
- Strategies for Backing Up and Restoring Snapshot and Transactional Replication
- Backup and Restore for Oracle Publishers
- Troubleshooting Replication
- Troubleshooting Oracle Publishers
- Removing Replication