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.

Oracle Objects and PL/SQL in SQL Server

As a continuation of one of my primary themes at the MSSQLDUDE blog here, I would like to provide some more info for those Oracle DBAs and developers out there reading this. In this instance today, I’ll touch a little bit on a few things that you should be aware of when converting from Oracle databases to SQL Server. Keeping in mind that at its core, both are indeed relational databases and have grown up together. When Microsoft or Oracle adds capabilities to their database product, you can be the other will do the same shorlty thereafter.

There are 2 primary links that I would like to point you toward to kick things off: the first one here is for the SQL Server Migration Assistant (SSMA) tool which is a free download from Microsoft. It is a great tool and has been around for quite awhile. The original makers of that tool, DB Best, are excellent Microsoft partners and the IP was sold to Microsoft years ago and is now available for you as a free download to convert Oracle PL/SQL, objects and data into T-SQL and SQL Server. It does many other things as well such as estimate what will or won’t automatically convert, how much effort is needed to complete the migration and many options, too many to note here, that you can configure to control how similar, but not identical, features will convert from Oracle to SQL Server.

The other link here is to a 160+ page document from the DB Best team and is an invaluable tool to have in your toolbox in addition to the SSMA tool for your migration projects. Of course, the Help documentation that is part of the SSMA is also useful, including details on how to use the data testing capabilities in SSMA which are invaluable before you decide to enact the code & object changes on the target SQL Server schema. Both the free guide and free tool, though, I use quite a bit on these projects.

Here, then are a few highlights that I would like to point out for both DBAs and developers to keep in mind when converting from Oracle to SQL Server. The links above are the tools and documents that list the full set that you should be aware of when embarking on an Oracle migration project. But I run into these a lot and picked out a few for you as gotchas and that should be of interest to you:

1. Oracle Packages – To emulate Oracle’s “packages”, which allow Oracle developers to package-up Oracle database objects like variables, functions, procedures, etc, you need to place those objects into the SQL Server “programmability” category separately in SQL Server. The SSMA tool and DB Best documents use naming conventions and schema names to categorize the package contents.

2. Oracle Sequences – A long-time favorite of database pros has been arguing over table-agnostic sequences vs. SQL Server/Sybase style identity columns (look for sequences in SQL 11!). To convert from Oracle sequences to SQL Server, you can either convert to identity columns in your target SQL Server tables or use the SSMA option which generates a SQL Server stored proc to emulate the Oracle sequence NEXTVAL and CURRVAL functionality is in this example from the DB Best doc:


INSERT INTO employees (id, name)

VALUES(employees_seq.nextval, ‘David Miller’);


DECLARE @nextval numeric(38, 0)

EXECUTE sysdb.ssma_oracle.db_sp_get_next_sequence_value ‘customers’,’dbo’,’employees_seq’, @nextval OUTPUT

INSERT employees (id, name) VALUES(@nextval, ‘David Miller’)

3. CURSORS – The dreaded cursor. This is where my earlier posts here @ my blog around the evil cusror originated. I used to cursors a LOT when I was an Oracle developer. And I ran several Oracle-to-SQL migration projects which lead to quite a bit a dealing with cursors. T-SQL does not require the use of cursors the way that PL/SQL procedure do. And SQL Server T-SQL does not optimize cursors very well or in the same manner that sets returned from selects are optimized. Therfore, if you can, rewrite cursors into new T-SQL statements. But if you cannot and want to convert, use SSMA to convert the PL/SQL code to T-SQL and then consult the document I’ve reference in this posting about how to emulate Oracle’s cursor arguments and remember that SQL Server supports different types of cursors such as FORWARD, READ ONLY, etc., and go back and understand which one is best for each use of the cursor to ensure optimal performance of the cursor T-SQL code.