Wednesday, 11 June 2008

SQL Developer Migration Workbench 1.5.1 for Sybase / SQL Server Quick Guide

This Quick Guide will run through the main steps you should take to perform a Sybase or SQL Server migration to Oracle.

Installation
  • Download the latest version of SQL Developer ( using 1.5.1 today), to your Development PC which can access your Oracle database and your SQL Server/ Sybase database. SQL Developer ships with the migration extensions built in.
  • Download and Install Oracle XE if you dont have access to an exiting Oracle database.
  • UnZip SQL Developer into its own directory (for example "SQLDev")
  • Don't use an existing ORACLE_HOME or ORACLE directory
  • Run sqldeveloper
  • Setup the jtds driver (open source JDBC driver for Sybase and SQL Server) with SQL Developer
  • JTDS can be got using the Check For Updates feature. Tools > Prefereneces > Extensions > Check For Updates. Walk through the wizard and choose any new migration updates and the JTDS driver.
Setup Connections & Migration Repository
  • Create a connection to Sybase or SQL Server
  • Create a connection to your Oracle database
  • Create a new schema for the migration repository (MIGREP)
  • GRANT DBA TO MIGREP IDENTIFIED BY MIGREP
  • Create a connection to your migration repository schema (MIGREP)
  • Associate the migration repository with this schema
  • Right Click the migration repository connection (MIGREP), Migration Repository > Associate Migration Repository
Setup the Migration Preferences
Capture your Database
Creates a "snapshot" of the Sybase / SQL Server database in our migration repository.
  • Capture the Sybase / SQL Server database (dont use Quick Migrate!) .
  • Note there is an offline capture available as well if you cannot directly connect to your Sybase database
  • Save the Captured Dialog Text (Handy to have a list of captured number or objects)
  • Identify any Capture Issues and Resolve (there shouldn't be any!)
  • Browse the Captured Model
Convert your Captured Database to Oracle
  • Convert the Captured Model to the Oracle Model
  • Choose the datatype mapping (best to use the default mapping)
  • Save the Converted Dialog Text (Handy to have a list of converted number of objects)
  • Save the Migration Log
  • Identify any Convert Issues and Resolve
Generate The Oracle Database (Schema and Objects)
  • "Generate" the Migration scripts
  • Run the Scripts using the SQL Worksheet to create your Oracle database.
  • Save the Result of the Generation Script
Project Plan
Errm. It looks like Im doing the plan in the middle of the project!
But in reality the above steps can be completed for one database within hours, or a day or two. You havent converted any data, we have just been working on the database definition (tables, views, procedures,...). You may have lots of issues remaining, but you now know what has been automated for you and what manual work remains. This is the time to step back and think of the entire project and plan.
  • How are you going to validate the Oracle database?
  • How are you going to rectify conversion issues or objects which didnt convert?
  • How are you going to switch from Sybase / SQL Server to Oracle. Will there be downtime for users?
  • What apps are affected by the switch? What work is required to move them to Oracle?

Enhance your Oracle Database
  • Identify and Generation Issues and Resolve them in your Oracle database. Not in the converted model as there are better tools to work with real Oracle objects.
  • Verify that all the objects in your Sybase / SQL Server database are present and valid in your Oracle database.
Move the Data from Sybase / SQL Server to Oracle
  • Data Move for small dataset (<100mb),>
  • Data Move for large dataset (>100mb) , use the offline datamove
Test & Tune
The buck stops with you to validate that the migration is complete and succefull. Note that this part can take longer than the migration itself. How do you test your Sybase / SQL Server database currently? How will you verifiy that the Oracle database works as expected?

Deploy
  • Will the database definition have changed since you started the migration?
  • Will the data have changed? How to move it to Oracle. Using a delta or perform an entire data move again.
Your migration approach will differ, depending on the complexity of the database, the applications that run on top, your company standards, .... . Hopefully the above steps will help.