Monday, December 03, 2007

SQL Developer Migration Workbench

After years of relying on third party developers to create GUI tools for use in developing SQL on Oracle databases, the SQL Developer application was released by Oracle.

First released as Raptor, this is not new news. What is new however is version 1.2.

SQL Developer 1.2

Being a dyed-in-the-wool sqlplus and vi kind of guy, I have never made extensive use of SQL Developer, though I do use it a lot when browsing schemas and/or data.

What I discovered today however was just an amazing addition to SQL Developer.

With version 1.2 , the Migration Workbench has itself been migrated, into SQL Developer.

SQL Developer Migration Workbench

I had occasion today to make use of the migration workbench, and I was impressed with how simple it was to migrate SQL Server data to Oracle.

SQL Developer was able to connect directly to SQL Server, create a migration catalog, generate scripts to duplicate the SQL Server database objects in an Oracle Schema, and migrate the data.

The documentation for actually doing the migration is pretty good.

Setting up the native authentication was somewhat problematic however.

The SQL Developer documentation referenced earlier does a good job of showing you how to setup the JDBC connection, but does not explain how to setup Windows Authentication.

The solution was found after rooting around in the SQL Developer forum.

The JDBC driver used to connect SQL Developer to SQL Server is from the jTDS Project.

After extracting the jdbc driver from it, another file needs to be extracted where it can be used by SQL Developer.

This file is ntmlauth.dll, found in the x86/SSO path in the jtds zip file.

Assuming that SQL Developer was installed on a Windows box at C:\sqldeveloper, ntmlauth.dll needs to be copied to c:\sqldeveloper\jdk\jre\bin.

After doing that, Windows authentication from SQL Developer to SQL Server worked perfectly.

The entire process of migrating the data from SQL Server to Oracle was pretty much a point and click affair.