Oracle Data in SQL Server, Part 3: Oracle Replication

I’ll finish-up on my series on data migration from Oracle to SQL Server with a few notes on migrating data from Oracle to SQL Server utilizing the SQL Server replication functionality. A SQL Server 2005 & 2008 enterprise edition feature called Oracle Publisher to enable transactional or snapshot replication from Oracle to SQL Server is very useful, particularly to DBAs. Application developers can migrate, integrate and mix & match Oracle and SQL Server data through OLEDB, SSIS, .NET, etc. But DBAs who focus on database management and do not wish to get dirty in the marsh of Visual Studio or BIDS, can use SSMA or T-SQL to configure SQL Server replication with Oracle.

This is very easy to configure, can be maintained through the traditional SQL Server replication monitoring mechanisms already familiar to you and performs very well.

When I set-up my first Oracle Publisher from SQL Server 2008 replication, I used these steps outlined here. You will need to run a script that is a PL/SQL file dropped in your SQL Server instance install directory. I ran it from SQL*PLUS as “system” and it set-up a replication user in Oracle that the Oracle Publisher will use from SQL Server’s replication engine. Also please note from that link above with the steps that you need to follow, that you must have access to the Oracle server or the Oracle client libraries so that you can reach the Oracle database while you are setting up the replication.

On my latest test servers, I have Oracle co-located with SQL Server. But the SQL Server that you set-up as an Oracle replication server will not likely have the Oracle database co-located there like I do. Therefore, use the Oracle TNS and SQL*PLUS utilies to test connectivity before going through this process and getting a headache trying to troubleshoot the configuration process.

Once you’ve configure the publication, you can then monitor and manage the replication as you would with SQL Server replication sources.

Bring Oracle Data into SQL Server

This is part III of my continuing series on working with Oracle for SQL Server pros …

In this Oracle posting, I’ll focus on data integration as part of an ETL strategy where you will build a SQL Server data warehouse from Oracle data sources. This is a very common practice and allows you to take advantage of the awesome SQL Server data integration and ETL capabilities that you essentially get for free as they come with the SQL Server product by way of SSIS (SQL Server Integration Services).

What I’ll do is point out to you a few links to dive deeper, some gotchas, best practices and approaches that I have taken in the past that were successful. If you are not careful about your approach to integrating Oracle databases into SQL Server databases, you may find yourself quickly falling victim to degrading performance that fails to meet your most basic SLAs and data management needs.

The most obvious and easiest mechanism that I find to connect to Oracle data source from SSIS is to use the Microsoft OLE DB Provider to Oracle (see pic below). There are also many other providers besides Microsoft for Oracle. But with SQL Server, you get this OLEDB adapter from Microsoft out of the box. I’ve used it many times, it works just fine. There is a good paper on SQL CAT here which you can use to see some of the tests and timings recorded and published by the Microsoft SQL customer advisory team when connecting to Oracle from SQL Server, it’s very helpful for you as you set your service level agreements with your business users. A few things to watch out for include that you must know the Oracle database network name that is being broadcast from the TNSNAMES configuration on your Oracle database sources. If you do not have access to the Oracle servers, make sure that you’ve collected a read-only login to the database, the TNS NAME and the server host name (physical or virtual).

OLEDB, however, is not generally the fastest mechanism to use to access data from Oracle. That being said, not every use case where you utilize SQL Server either as a DW or for an application that needs data from Oracle, requires super-fast access to the Oracle data source. For instance, an ETL job that runs nightly may have a large window with which it can use to pull data from, not requiring lightening-speed data access to the Oracle sources.

However, if you need to access Oracle faster than OLEDB, SQL Server 2008 ships with a set of high-speed adapters for Oracle from Attunity that you can use with SQL Server. To use the Attunity-provided Oracle adapter with SSIS, go to this Microsoft download link here. Once you add the Attunity drivers, you can then select those from your SSIS data source or data target to take advantage of the improved data movement speeds.