Tuesday, 13 May 2008

Quick Start to Migrating your SQL Server or Sybase Database

** A better guide for SQL Developer 1.5.1 can be found here **
Ill outline the steps I would use to migrate a SQL Server or Sybase database to Oracle.

  • Download the latest version of SQL Developer
  • Download JTDS 1.2
  • 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
  • Extract the JTDS jar file (jtds-1.2.jar) to a handy directory (SQLDev is fine)
  • Create the sqldeveloper.cmd file just to make double sure
  • Double click the sqldeveloper.cmd file to launch SQL Developer
  • Setup the jtds driver with SQL Developer
  • Tools> Preferences > Database > Third Party JDBC Drivers. Add the JTDS jar file
  • Create a connection to SQL Server/Sybase
  • 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
  • Browse the SQL Server/Sybase database
  • Setup the "Is Quoted Identifier On" preference
  • Setup the DATETIME to DATE format mask if required
  • Setup the Offline BLOB data move preference if required
  • Capture the SQL Server/Sybase database (dont use Quick Migrate!) .
  • Note there is an offline capture available as well if you cannot directly connect to your SQL Server/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 the Captured Model to the Oracle Model
  • 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 Migration scripts
  • Run the Scripts using the SQL Worksheet to create your Oracle database.
  • Save the Result of the Generation Script
  • Identify and Generation Issues and Resolve them in your Oracle database.
  • Verify that all the objects in your SQL Server/Sybase database are present and valid in your Oracle database
  • Data Move for small dataset (<100mb),>
  • Data Move for large dataset (>100mb) , use the offline datamove
  • Test, Verify and Tune your database
Heres a viewlet made using SQL Dev 1.2.1 migrating a SQL Server database.