Migrating SQL Server Database to Oracle Database 11g

In this article I will show you the steps to migrate a Microsoft SQL Server database to Oracle Database 11g using Oracle SQL Developer.

This tutorial covers the following topics:

• Overview
• Prerequisites
• Creating the mwrep User
• Creating the Migration Repository
• Capturing the Microsoft SQL Server Exported Files
• Checking Conversion Preferences
• Converting to the Oracle Model
• Generating and Executing the Script to Create the Oracle Database Objects
• Checking Offline Data Move Preferences
• Analysis and Estimation
• Migrating the Data
• Testing and Deployment
• Summary

 

Overview

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft SQL Server Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your third-party database to Oracle. 

There are four main steps in the database migration process:

Capture the Source Database	The first step is to capture a "snapshot" of the Microsoft SQL Server database. This can be done in two ways. Online Capture: This requires creating a connection in SQL Developer to a live Microsoft SQL Server database. Using JDBC, the Microsoft SQL Server database metadata can be accessed and the Capture Model created. Offline Capture: This involves BCP scripts generated by SQL Developer to extract the Microsoft SQL Server database metadata to files. These files can then be "captured" by SQL Developer to create the Captured Model. The second method is what you will perform in this tutorial. Using SQL Developers Offline Capture feature, the Microsoft SQL Server Northwind sample database has been extracted into offline data files. The SQLServer2005.ocp file generated by the Capture tool contains the database schema information for the Microsoft SQL Server Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft SQL Server database. This structure is called the Captured Model.
Convert the Captured Database	Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database	Oracle SQL Developer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data	The last step in the process is to migrate the data. You can do this in one of two ways. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft SQL Server source database and migrate the data. Offline Data Move: You can export the data from Microsoft SQL Server. SQL Developer</font> will create a series of BCP and sqlldr files that you can run from a batch file. The second method is what you will perform in this tutorial.

Prerequisites

Before you perform this tutorial, you should:

1.	Install the Oracle Database 10g or later, or Oracle Database XE
2.	Download and unzip Oracle SQL Developer here.
3.	Download and unzip the mssqlservermigration.zip file into your working directory (i.e.wkdir)

Creating the mwrep User

To create a new database user, perform the following steps:

Note: If you already have a system_orcl connection and a mwrep user, you can skip these steps.

1.	Open Oracle SQL Developer from the icon on your desktop.
2.	Select View > Connections.
3.	In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.
4.	Enter system_orcl in the Connection Name field (or any other name that identifies your connection), system for the Username field, and <your password> for the Password field. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
5.	Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.
6.	The connection is saved and you can see it listed under Connections in the Connections tab.
7.	Expand the system_orcl connection. Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.
8.	Enter the following code in the SQL Worksheet to create a user for the migration repository CREATE USER MWREP IDENTIFIED BY mwrep DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;
9.	Run the script , using the "Run Script (F5)" icon.
10.	The mwrep user was created successfully.

Creating the Migration Repository

To convert the Microsoft SQL Server database to Oracle, you need to create a repository to store the required repository tables and PL/SQL packages. To do this, perform the following steps: 

Note: If you already have a mwrep_orcl connection and a migration repository for it, you can skip these steps.

1.	Before you create the repository, you need to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear. Note: If this tab is not visible, select View > Connections.
2.	Enter mwrep_orcl in the Connection Name field (or any other name that identifies your connection), mwrep for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
3.	Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.
4.	The connection is saved and you can see it listed under Connections in the Connections tab.

5.	Right-click the mwrep_orcl connection and select Migration Repository > Associate Migration Repository.
6.	A progress window appears.
7.	When the install is completed, click Close.
8.	Click OK.

Capturing the Microsoft SQL Server Exported Files

The procedure for creating the Microsoft SQL Server database scripts has been completed for you and the files are available in the zip file provided in the prerequisites.

To load the captured Microsoft SQL Server database scripts into Oracle SQL Developer, perform the following steps:

1.	Select Migration > Third Party Database Offline Capture > Load Database Capture Script Output.
2.	Browse the Capture directory and select the sqlserver2005.ocp file.
3.	The objects are being captured. When done, click Close.
4.	SQLServer2005 is listed in the Captured Models tab. Expand SQLServer2005.
5.	Expand dbo to see the list of objects that were captured.

Checking Conversion Preferences

It is important to review the conversion preferences at this point. To do so, perform the following steps:

1.	Select Tools > Preferences.
2.	Expand Migration and select Identifier Options.
3.	Make sure Is Quoted Identifier On is selected. Click OK.

Converting to the Oracle Model

To convert the captured model to the Oracle model, perform the following steps:

1.	Right-click the captured model SQLServer2005 and select Convert to Oracle Model.
2.	The Set Data Map window appears, which shows you the Source Data Type and what it will be converted to in the Oracle Model. Click Apply.
3.	The conversion is performed. When done, click Close.
4.	Expand Converted:SQLServer2005 listed in the Converted Models tab.
5.	Expand dbo_Northwind to view the converted objects.

Generating and Executing the Script to Create the Oracle Database Objects

To generate the SQL script with DDL statements that will be executed to create the objects in an Oracle Database, perform the following steps: 

1.	Right-click Converted:SQLServer2005 and select Generate.
2.	The Oracle SQL is being generated. When done, click Close.
3.	The SQL from the script is shown. Select system_orcl from the drop-down on the right. icon.
4.	Click the Run Script (F5).
5.	The results are display from the script execution.
6.	Now that your scripts have run successfully, you can create a connection for the dbo_Northwind user. Right-click Connection and select New Connection.
7.	Enter dbo_northwind-migrated_orcl in the Connection Name field (or any other name that identifies your connection), dbo_Northwind for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.
8.	Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.
9.	Expand the dbo_Northwind-migrated_orcl connection.
10.	Expand Tables.
11.	The database tables that were converted to Oracle are listed. Select the EMPLOYEES table.
12.	Select the Data tab. Notice that currently there is no data in the table. You will migrate the data later in this tutorial.

Checking Offline Data Move Preferences

A date format masks can be specified in the preferences so that the Offline Data Move scripts and in particular the Oracle SQL*Loader control files can reference the correct format. To do so, perform the following steps:

1.	Select Tool > Preferences.
2.	Expand Migration and select Data Move Options.
3.	Make sure the Date Mask format is same as the following. Mon dd yyyy HH:mi:ssAM
4.	Make sure the Timestamp Mask is same as the following. Mon dd yyyy HH:mi:ss:ff3AM Click OK.

Analysis and Estimation

The migration from Microsoft SQL Server to an Oracle database can be straightforward. But it is worth noting that some objects and syntax are not automatically migrated. Therefore manual intervention is required. 

Analyzing the Capture Model, identifying the number, type and complexity of objects , can help calculate the estimated time required for manual tasks. 

The following Reports can be used with your own task and project estimation.

• Migration Reports> Migration Summary
• Migration Reports> Migration Details
• Migration Reports> Automatic Name Changes

Estimate time to:

• Resolve each object the failed to convert manually
• Verify and test each object
• Changes to the application due to name changes

Migrating the Data

The data has already been generated from Microsoft SQL Server.

To import the data using the scripts provided, perform the following steps:

1.	Open a DOS command prompt and execute the following commands: <prompt> cd <location where files are> <prompt> oracle_ctl The files are located in the Data directory where you unzipped the files provided in the prerequisites section. oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.
2.	The command executes successfully.
3.	Switch to Oracle SQL Developer and Refresh the connection.
4.	The data for the table has been loaded successfully.
5.	Select the CATEGORIES table.
6.	Double-click the first cell under the PICTURE column and click ....
7.	Select the Image check box and click OK.

Testing and Deployment

Depending on the complexity of the database being migrated, a large part of the migration project could be testing. 

Currently Oracle does not provide any tools that can automatically validate a migrated database for you. This phase of the migration should be planned and taken into account before the migration begins. 

The following should be part of your project plan

• Verify Database Structure
• Verify Database Security
• Verify Data
• Verify Logical Correctness of Views
• Test Application