Programming Replication from Heterogeneous Data Sources

Microsoft® SQL Server™ enables third-party products to become Publishers within the SQL Server replication framework. The Replication Distributor Interface allows replication from heterogeneous databases that provide 32-bit OLE DB drivers to Subscribers running SQL Server 2000. Heterogeneous data sources include:

• Oracle databases
  
  
• DB2 databases
  
  
• Microsoft Access databases
  
  
• Other databases that comply with SQL Server ODBC or OLE DB Subscriber requirements.

When integrated, the Replication Distributor Interface exposes the SQL Distributor, and allows heterogeneous data sources to store meta data and replicated transactions in the SQL Server Distributor database. The Replication Distributor Interface is an OLE DB service provider that allows users to store replicated SQL statements, scripts, and .bcp files in the Distributor store-and-forward database. The Replication Distributor Interface is based on the OLE DB connection model and supports a subset of the DataSource, Session, and Error objects. An additional Distribution object is added to the Session object and is used to store transactions marked for replication in a SQL Server distribution database.

Note  The Replication Distributor Interface is a special purpose OLE DB service that is used only to distribute replicated SQL Server transactions. It does not support the minimal set of interfaces necessary to be considered a standard OLE DB data provider.

The Replication Distributor Interface cannot be used with replication types that need updates to be made at the Subscriber. The only types of replication that can be used with the Replication Distributor Interface are snapshot replication and transactional replication that has read-only Subscribers. Merge replication, and transactional replication with immediate updating, queued updating, or immediate updating with queued updating as failover are not available from heterogeneous Publishers to SQL Server Subscribers.

Here are the steps to using the Replication Distributor Interface.

1. This step uses Microsoft Visual Basic® or Microsoft Visual C++® and the replication SQL-DMO objects, makes calls for setting up the publication, articles, and subscription information. These calls differ from the typical SQL-DMO setup calls because they are made on the distribution server and not at the Publisher. A third-party replication tool can also be used to implement this first step
   
   
2. This step takes the place of the Snapshot Agent and Log Reader Agent. The Replication Distributor Interface is used to store the replication transactions on a server that is currently acting as the Distributor, which will then be distributed by the Distribution Agent. The following C++ code is a sample of an object that is used to place commands into the distribution database.

   // Instantiate a data source object for the SQL Server Publishing
   // provider.
   hr = CoCreateInstance(CLSID_SQLDistribution, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize,
   (void**) &pIDBInit);
   

Using the Replication Distributor Interface leaves the responsibility of data modification detection to the developers because the Log Reader Agent is not available. The use of the monitoring and troubleshooting tools, alerts, and notifications are still available in SQL Server Enterprise Manager. The Log Reader Agent can be activated by using Microsoft Host Integration Server 2000 and its Distribution Store Interface (iDistributionStore). For more information, see the Host Integration Server 2000 documentation.

The Microsoft SQL Server CD-ROM ships with some Replication Distributor Interface samples. For more information, see Replication Distributor Interface Samples.

When deploying an application using the Replication Distributor Interface independently of SQL Server 2000, you must include additional files in the installation kit you use to distribute your application. If you will be deploying your application to a computer where SQL Server 2000 will also be installed, these files will already be present.