I’ve been referring customers to SQL Developer’s Migration Workbench for a few months now. I thought it might be a good idea to give it a try myself before I send any more children out to play with the wolves.

Kudos to Oracle for using Foreign Keys!

This is NOT a formalized review. This is a quick glance at a cool tool from a cool vendor. I did what all geeks do – I ran it without reading ANY of the product documentation. And I got it to work in about 15 minutes. Not bad. Not bad at all. I also played around with Microsoft’s counterpart, the SQL Server Migration Assistant (SSMA) a few months ago. I was also successful there, but it did take a few trips to the help to figure it out and to get data successfully moved. More on that in a follow-up post.

For the test I was using SQL Developer 3.0.04.34 (64 bit) on my Windows 7 laptop. I migrated from MySQL 5.0 to Oracle 11gR2.

If you need SQL Developer to perform migrations, you can get it here.

Wizards to the Rescue!

Step One: Create A Repository
One repository will work for all of your Oracle instances, assuming you are OK with the project mingling together. Give your projects different names, and you should be good to go. Pick the database you want to build it in, then use the Wizard to build the repository.

Whatever user you use to login to the instance in the wizard is apparently where the repository gets built. Don’t supply a login for any schema you do not want these 40+ tables to be created in. You can find the complete data model here if you are morbidly curious.

Repository Creation Wizard

Step Two: Migrate
For this example I chose to migrate a MySQL database. What happens is SQL Developer will take the ENTIRE MySQL database and move it to a new SCHEMA of the same name in the target Oracle database. It would be nice if the tool allowed you to choose the tables you wanted to migrate and whether you wanted to append to an existing SCHEMA, but I can live with this. If you can’t, then I recommend trying the ‘Copy to Oracle’ mode.

Once the Migration Repository is available, you can start the 7-step ‘Migrate’ wizard. I’m not going to post screen shots of every step, just enough to give you an idea of what’s happening.

Migration Workbench 'Migrate' Wizard

Make sure your repository database is available, or you’re not going to make it past Step 2!

In Step 3 we are going to create a new ‘project.’ We’ll need a project name, description, and work space (output) directory.

In Step 4, we need to tell Oracle where the data is coming from.

Where Are Getting the Data From?

Where Are Getting the Data From?

  • Ensure the JDBC drivers for connectivity are installed
  • Select or Define your connection

If you don’t see the source you are looking for, try the ‘Help – Check for Updates’ engine. Be sure to install the ‘jtds driver.’ This will give you access to MySQL, SQL Server & Sybase. If you are migrating from SQL Server, be aware that the tool only supports migrations from SQL Server 2005 and older. Wouldn’t be surprised if this changes once Denali is formally released and ‘older’ 2008 installs are ripe for migrations. As it stands now, 2008 is pretty bleeding edge in a lot of SQL shops and I don’t see big demand for migrations there.

Now we get to select our database. You will notice that SQL Developer ignores the default MySQL data dictionary databases, kudos!

After we select the data to be migrated, we need to let the engine know how to treat the data type conversions that will happen. For example, will we

The Most Important Step!

If you need to make any changes, you can edit the default rules to create your own ‘user’ rules

Fun with data types!

If you are tempted to jump through this wizard as quickly as possible, I strongly urge you to stop here and think a bit. It doesn’t matter how easy the tool makes it to move your data if the date is truncated or converted in such a state as to be useless. It might make sense to test your migrations on smaller datasets before attempting a very large one to save you a lot of frustration later.

The last page is basically the ‘go’ page where you can double-check everything you have set and confirm the target database.

Where Are We Reading From, Where Are We Writing Too?

Alright, just one more click and we are good to go! Let’s confirm what we’ve asked Oracle to move for us.

Just one more click!

Move Tables Manually

If you want to control the tables migrated and where they are sent, you can simply connect to the source database, find your table, right-click, and say ‘Copy to Oracle.’

Copy Just One Table To Oracle

You’ll select your Oracle database to copy the table to. You do this by choosing a SQL Developer connection. Whichever schema the connection is tied to will determine where the table is copied. This ran fairly quickly for me, say 40,000 records in about a minute.

It’s not clear if this requires the repository to be setup, but I’m going to go with ‘No.’

Managing Migration Projects

You can open the ‘Migration Projects’ panel in SQL Developer under the ‘View’ menu. It will populate assuming you are connected to the database that houses the respository. You can view your migrations here, check out logs, space information, and even check the quality of the data involved? I tried this last piece and didn’t get very far, but I think that’s due to the migration I was working on not being 100% successful.

Migration Projects Panel

Parting Words

This worked very well the first time I ran through the wizard. I attempted to migrate the data a second time to a separate database and ran into a problem. The capture activity seems to get ‘stuck’. I recommend if you run into a problem and need to cancel the capture, to completely exit out of SQL Developer before trying anything else. I did not do this two times, and both times SQL Developer hung when going to Step 5 in the wizard.

Even with these problems, I would have little reservation recommending the tool. It’s free. It’s pretty intuitive. I’m going to assume the problems I ran into were my fault as I skipped all of the user docs. If I find out what the issue is, I’ll update the review accordingly.

If you’re on Twitter and have questions, feel free to contact me. @krisrice and @bamcgill from Oracle are also around and are very responsive.