Partager via

How To Migrate Oracle’s Sample HR Schema to SQL Server

  • Article

 

The following are step-by-step instructions on using SSMA to migrate Oracle’s sample HR schema to SQL Server 2008:

1. Download the SQL Server Migration Assistant for Oracle from here. After download, extract the installation files.

2. Install SSMA for Oracle on your client machine

3. Install SSMA for Oracle extension pack on the target SQL server machine.

4. Start the application by double-clicking the shortcut on your desktop:

SSMA

5. Obtain a license key. The first time you use SSMA for Oracle, you will be directed to a registration site from which you can obtain a license key (stored in a file called oracle-ssma.license). You will have to indicate the directory in which you will save this file (for example: C:\install\):

SSMA_LicenseWindow

Click Refresh License and SSMA tool will open.

6. Change default project setting. By default, SSMA loads only basic Oracle system schemas and packages. You need to customize project settings to allow loading of HR sample schema. Click on Tools from the menu and select Default Project Settings. On the Default Project Setting menu, click on Loading System Objects and check HR system object.

SSMA_DefaultGlobalSetting

SSMA_SchemaSelection

Note: Refer to the following instruction to install Oracle sample schema :Oracle9i, Oracle 10g Release 1 (10.1), Oracle 10g Release 2 (10.2) or Oracle 11g Release 1 (11.1).

7. Create a new project. Once the program is running, click on the New Project icon in the upper left corner to get started:

SSMA_NewProject

Specify the name of the project and the location of the file to save the project information:

SSMA_ProjectName

8. Connect to Oracle. Click on the Connect to Oracle icon from the menu toolbar and provide connection information to your Oracle database.

SSMA_ConnectToOracle SSMA_OracleLogin

Note: You can connect to Oracle using the following mode :

- Standard mode to connect using server name, port number and SID

- TNSName mode to connect using connection identifier

- Connecting string mode to use full connecting string

9. Create a schema migration report. Select a HR schema, then right-click the schema then select Create Report:

SSMA_MigrationReport

The resulting report provides information on conversion statistics:

SSMA_downloadstats

10. Connect to SQL Server. Click on the Connect to SQL Server icon from the File Menu. Specify the server name (e.g. localhost if SSMA is running on the SQL server machine) and port number (if using other than default 1433 SQL Server port number). Type the name of the database you are migrating to (e.g. HR). If the database does not exist, SSMA will create a new database using the default setting. Specify authentication information and click Connect to continue.

SSMA_SQLLogon SSMA_dbwarning

11. Map Schema and Type. In the Oracle Metadata Explorer, check HR schema and expand. You can select (or deselect) objects to be migrated as well as map schema. Schema mapping can be done at the Oracle schema level or at the individual object (such as specific table in Oracle) to SQL Server schema. In our example, we will leave the default setting to map Oracle HR schema to SQL Server dbo schema in the newly created HR database.

SSMA_MapSchema

We can also map type for individual objects. For example, EMPLOYEES table has several fields with NUMBER(*,0) data type.

SSMA_EMPLOYEESchema

From the Type Mapping tab, you can review the data type mapping where Oracle’s Number[*..*][*..*] is converted to SQL’s Numeric [*][*] data type.

SSMA_Mapping

You can update the mapping by clicking the Edit button and change the target type to int. Specify the range in the scale from 0 to 0 for conversion to integer. This option restricts to only convert when the Oracle data type has 0 scale. Click Apply button from the main data type mapping window to save the changes.

SSMA_NumberSetting1 SSMA_NumberSetting2

Apply the same data type mapping changes to DEPARTMENTS and LOCATIONS tables.

12. Convert the schema. In the Oracle Metadata Explorer, right-click the HR and select Convert Schema:

SSMA_ConvertSchema

13. Review conversion report and resolve error as necessary.

SSMA_Report

Click on the Error List tab at the bottom of the message windows:

SSMA_Error

Double clicking Error ID O2SS0231 updates the Oracle Metadata Explorer and SQL Server Metadata Explorer windows to the object related to the error.

SSMA_ErrorO2SS0231

In this case, the JOB_HISTORY table contains foreign key columns to the EMPLOYEE_ID in the EMPLOYEE table which we converted to int data type in step 10 above. However, data type mapping in this table still refers to the default mapping to number(*,0) data type. To correct the issue, locate the number(*,0) data type in this table and click Edit to update the mapping.

SSMA_TypeMapping

Right click on the JOB_HISTORY table from Oracle Metadata Explorer window and select Convert Schema.

SSMA_JobHistory

Click Overwrite All on the warning window:

SSMA_OverwriteAll

14. Synchronize the SQL Server database. To deploy the changes to the SQL server, right-click the database in the SQL Server metadata explorer and select Synchronize with Database.

SSMA_SynchronizeWithDatabase

Note: If the HR database does not exist in the SQL Server, the synchronization will fail.

15. Migrate the data. From Oracle Metadata Explorer window, right-click on the HR schema and select Migrate Data. Provide connection information to both the Oracle source database and the target SQL server.

SSMA_MigrateData

Note: By default, SSMA performs migration through client machine where SSMA is running. You can change the migration mode to Server Side Data Migration to allow data to flow directly from Oracle Source to SQL Server Source. To change the setting to use Server Side Mode, click Tools from the menu then select Project Setting. Click on General tab then click Migration. Select Server Side Data Migration Engine from the drop down.

The Server Side Data Migration Engine requires SSMA for Oracle Extension Pack installed on SQL Server. If the SSMA for Oracle Extension Pack is not installed on the instance of SQL Server 2008, and if Server Side Data Migration Engine is selected, then while migrating the data to the target database, the following error is encountered: ‘The SSMA Extension Pack was not found on the database server. Only client-side data migration is possible’

16. Review Migration Report.   After the data migrated, a report will be displayed with migration statistics below:

SSMA_MigrationReportFinal

The HR Oracle sample schema and data are now migrated to SQL Server.

Comments

  • Anonymous
    May 01, 2011
    During the migration, will SSMA take of the autmatically generated primary keys using Oracle Sequences? I mean, will i get same values again in the SQL Server tables as they are in Oracle tables?

  • Anonymous
    September 10, 2012
    yes it will

  • Anonymous
    July 06, 2014
    Really good and understandable  !!!!!!!! <a href="http://www.oracletraininginchennai.in">Visit Oracle Training</a>

  • Anonymous
    October 27, 2014
    Really nice article on migration from Oracle to SQL Server. My Question here is, what about other objects like Views, Stored Procedures, Functions etc. will those be migrated with the same steps ? Regards, Kapil Relan.