Migrating a SQL Server Database to Oracle 11g

After attending a session with an Oracle consultant this week, I thought I’d share with you some tips for migrating an SQL server database to the Oracle platform.

Firstly, let’s get one thing clear. I love SQL Server. I use it for 99% of projects I do. However, for a particular project I’m working on just now, it makes sense to migrate to Oracle for two reasons; rapid scalability and redunancy. Migration is simply put the conversion of an existing database into a new architecture by preserving data and functionality of the old architecture. A typical migration consists of the following steps:

  • Analysis
  • Migration
  • Testing
  • Deployment

SQLDeveloper is a wizard-driven java tool which will attempt to convert the following database elements for you:

  • Tables and Data
  • Primary Keys
  • Check Constraints
  • Foreign Keys
  • Indexes
  • Views
  • Groups / Users
  • Databases
  • Stored Procedures
  • Triggers
  • Grants
  • Rules
  • Defaults
  • User Defined Types

SQLDeveloper performs the following tasks to help you migrate your database to Oracle and is designed to execute tasks in the following order (I have outlined the exact steps at the bottom of this post).

  • Capture the source database structure into Source Model (online/offline)
  • Convert to Oracle Model
  • Create a representation of the structure of the destination database
  • Migrate the source database
  • Create the schema online or offline
  • Transfer the data online or offline

The steps below assume you have followed the steps to complete an OFA-complinant installation of Oracle 11g database on your server. I’ll cover this process in another post soon.

  1. Connect to SQLPlus using the following command: > sqlplus / as sysdba
  2. Create a new user called “sqlmig” with a password of “sqlmig” as follows > create user sqlmig identified by sqlmig default tablespace users temprary tablespace temp
  3. Give permissions required to the “sqlmig” user: > grant resource, create view, create session, create users, dba to sqlmig;
  4. Launch SQLDeveloper 1.5
  5. Create a new connection called “sqlmig” to the Oracle 11g database as sqlmig user
  6. Configure the JDBC Driver for SQL server > Tools -> Preferences -> Database -> Third Party JDBC Drivers > Map to jtds-1.2.2.jar > Copy ntlmauth.dll from the jtds directory to e:jdevelopersqldeveloperjdkjrebin
  7. Add a new connection to the SQL Server Database to your database > User: Windows Authentication and select your database
  8. Right click the SQL connection and select ‘Capture Microsoft SQL Server’
  9. Right click the captured model and select ‘Convert to Oracle Model’
  10. Right click on the converted model and select ‘Generate’
  11. Run the script that is produced
  12. Create a new connection to your new ‘dbo_xxx’ database
  13. Choose Migration -> Migrate data Watch it happen 🙂

Here are the slides from the day: