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.


5 comments:

Jože Senegačnik - Joc said...

Jared,
I was always wondering when you will start to blog. Nice start, very helpful post! Joze

Jared said...

Thanks Joze.

I need to start recording blogging ideas. I've had several, and can't remember all of them.

More to follow...

Unknown said...

Jared:

All afternoon trying to get SQL Developer installed and configured. I think this was the last problem that need fixing. Thanks.

Rohit Gupta said...

Hi Jared, I am planning to use migration workbench to migrate a sybase db to oracle 11g. I am not familiar with this tool. The data models of source and target are different. Do you have any idea if this kind of migration is possible in workbench manager where mapping of data elements (many-many, one-many or many-one) between source and target is possible? Please help, kind of urgent

Thanks
Rohit

Jared said...

Rohit,

I have not used this tool in about 2 years now, so I don't recall the mapping details.

In any case, it it likely that the tool is different now than it was when I blogged about.

My best recommendation is to install the tool, setup a test case and determine if it does what you need.