How To Migrate Oracle’s Sample HR Schema to SQL Server
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:
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\):
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.
7. Create a new project. Once the program is running, click on the New Project icon in the upper left corner to get started:
Specify the name of the project and the location of the file to save the project information:
8. Connect to Oracle. Click on the Connect to Oracle icon from the menu toolbar and provide connection information to your Oracle database.
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:
The resulting report provides information on conversion statistics:
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.
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.
We can also map type for individual objects. For example, EMPLOYEES table has several fields with NUMBER(*,0) data type.
From the Type Mapping tab, you can review the data type mapping where Oracle’s Number[*..*][*..*] is converted to SQL’s Numeric [*][*] data type.
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.
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:
13. Review conversion report and resolve error as necessary.
Click on the Error List tab at the bottom of the message windows:
Double clicking Error ID O2SS0231 updates the Oracle Metadata Explorer and SQL Server Metadata Explorer windows to the object related to the error.
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.
Right click on the JOB_HISTORY table from Oracle Metadata Explorer window and select Convert Schema.
Click Overwrite All on the warning window:
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.
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.
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:
The HR Oracle sample schema and data are now migrated to SQL Server.