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.