Introduction to SQL Server Heterogeneous Replication

By: Murali Krishnan | Updated: 2011-02-16 | Comments (4) | Related: > Replication

Problem

One of my clients contacted me about replicating data to non SQL Server databases. My client has a new business partner whose application runs on an Oracle database. Part of the business deal is to replicate data between the two environments. We are already using SQL Server to SQL Server replication and in this tip I cover some of the options available to replicate data between SQL Server and non SQL Server databases.

Solution

Considering the existing availability of replication in SQL Server and the need to support the new business partner, I suggested the use of Heterogeneous Replication. This type of replication allows Oracle to be a subscriber or a publisher.

Heterogeneous Replication

As you may know, a typical replication setup consists of a publisher, distributor and at least one subscriber. Heterogeneous replication works basically the same way, but means having a Non-SQL Server in the role of publisher or subscriber.

In previous versions, SQL Server supported only Non-SQL Server subscribers, but from SQL Server 2005 onwards, publishing data from Oracle was introduced as one of the enhancements to heterogeneous replication. Regarding enhancements, more information can be reviewed in this article.

SQL Server supports heterogeneous replication for both Transactional and Snapshot replication. With this feature, we are able to use SQL Server replication benefits even when there are different types of databases in the business environment.

The below image illustrates heterogeneous replication with Oracle as a publisher.

options available to replicate data between sql server and non sql server databases

The below image illustrates heterogeneous replication with Oracle and/or DB2 as a subscriber.

heterogeneous replication allows oracle to be a subscriber or a publisher

Non-SQL Server Publishers:

In the SQL Server replication topology, the Non-SQL Server publisher can be an Oracle database. This means that Oracle can take on the publisher role. The detail steps and administrative considerations for configuring an Oracle publisher can be viewed in this article.. In addition to this, when we place Oracle in the publisher role, there are a few design consideration and limitations like an objects maximum size, case sensitivity, etc... The complete guidelines can be read in this MSDN library article.

In SQL Server Management Studio, we have an option for creating an Oracle publisher as shown in the below image.

in ssms you can create and oracle publisher

We need to configure Oracle as a publisher in the SQL Server distributor. When we do this, a linked server is created and configured with the Oracle database which is going to be used by replication. The below screen shot is the distributor and publisher mapping wizard.

the distibuter and publisher mapping wizaed in ssms

Non-SQL Server Subscribers:

A Non-SQL Server subscriber can be Oracle or IBM DB2. In this type of replication, the subscription must be a PUSH type where all the replication agents and distributor agents are running at the publisher. Also in this type of implementation, there are a few design and constraint considerations such as how NULL and NOT NULL are handled between the different database platforms. More information can be found in this article.

In SQL Server Management Studio, there is a subscription wizard with options for Non-SQL Server subscribers as shown in the below screen shot.

in ssma there is a option for non-sql server subscribers

After the 'Add Non-SQL Server Subscriber' option is selected, on the next screen you will see a list for types of Non-SQL Server subscribers. (As of now, SQL Server 2008 supports Oracle and IBM DB2 with OLE DB Provider.)

as of now, sql server 2008 supports racle and ibm db2 with ole db provider

Things to note:

Publishing data from Oracle to SQL Server is only available in SQL Server Enterprise Edition.
Non-SQL Server subscribers (Oracle / DB2 ) is available in both the Standard and Enterprise editions.
Heterogeneous replication is a good option when we need to provide immediate updates to other databases such as Oracle and DB2. Using the built-in replication process eliminates the need for additional development and validation procedures. There are still design considerations and limitations, such as the maximum size limits and how NULLs are handled, so there is a need for in-depth analysis when using heterogeneous replication.
When setting up Oracle as publisher, changes are tracked by creating triggers and tracking tables for each published table in the Oracle database. When data gets changed in the published table, the triggers on the table fire and update the required information into the tracking tables for each action. The Log Reader Agent on the SQL Server Distributor updates the information in the Distributor database from these Oracle tracking tables. The Distribution Agent then updates Subscribers with the necessary information.

Next Steps

Read this overview of Heterogeneous Database Replication - SQL Server 2008
Step by step procedures to implement Oracle as a publisher
How to create a subscription for a Non-SQL Server Subscriber
Review MSSQLTips category: Replication

About the author

Murali Krishnan is a Lead Consultant with vast experience in Database/BI Design, Development and Administration.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2011-02-16

Comments For This Article

Saturday, December 14, 2013 - 10:20:37 AM - Raja

Hi Murali.

Very good article. Can you please write another article and attach screen shots for "Non-SQL Server Subscriber". I need to configure in my development server, so please help me or if you find any links regarding this please share..

Thanks.

Monday, March 11, 2013 - 10:14:59 PM - Dhanu

Hi Murali,

Very good article. I am setting up for the similar business request for datawarehousing application. Oracle publisher and SQL distribution & subscriber. I have couple of question. Please suggest.

Publisher: Oracle 11g

Subscriber and distributor: SQL server 2008 R2 Ent 64bit (cluster Instance)

1. SQL server is a cluster instance. In which case, network share for replication folder should go through the failover cluster administrator. Any steps or suggestion would be helpful.

2. Do I have to consider any other aspect when replicating the data from oracle to SQL cluster instance?

3. Can I take a backup of subscription database on sql server? how?

4. by default, when we setup heterogeneous replication from oRacle to SQL, what type of subscription is enabled(PULL or PUSH)?

5. Can I enable Change Data Capture (CDC) on Subscription database?

Sorry for many questions. But, should be implementing straight on Production server. Thank you very much in advance.

Wednesday, February 16, 2011 - 8:59:50 PM - jeanpaul2	Back To Top (12949)
in another database to Oracle is it PossibLe ?

Wednesday, February 16, 2011 - 12:04:51 PM - Scott Shaw

Murali,

Thank you for this article. My business has been asking a lot questions about moving data between Oracle and SQL and your article has helped to clear things up. In the past I've always defaulted to an import\export model or, when moving from SQL to Oracle, database links. Both tend to be high maintenance.

The article went straight to the core concepts and gave me good information I can take back to the customer. I'll definitely follow the links to get more details.

Thanks again!

Scott