Skip to main content

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:
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

Post a Comment

Popular posts from this blog

Expose your Local Development Environment to the outside world with Ngrok

When we develop on localhost, we usually use some kind of simple HTTP server like node, our Oracle database,APIs, webhooks, Callback Urls or whatever. This is all good and we are all pretty happy about that. We have access to our app using our fancy  http://localhost  url. We are happy, but  alone . What if you would like to share your app to a colleague that is not on the same network as yours ? What if you need to check your app on an SSL connection? What if you wanted an external system to pass you a process invoked by a method ? ngrok to the rescue Ngrok   is a simple “free” service that can help you with that. Here’s some of the features that it provides: Expose your locally hosted app/website to the outside world by providing you a  http(s)://{something}.ngrok.io  url. Allows you to have an SSL connection to your localhost environment. Inspect/replay the requests made to your local environment Custom subdomain (required a premium accou...
1 comment
Read more

Disabling of Password Expiration in Oracle Apex(Internal Workspace Admin)

This post was inspired by a  question  on the OTN APEX forum, which contains how to  reset set the password of the  Oracle Internal Workspace Admin    and  Set the account never to expire  The first bullet has so many blogs  talk about how to reset the password of the Internal Workspace. However, i am more intrigued with the second . To start of with It is not advisable to never expire accounts since its rudimental for user to always renew their accounts  prior to expiration. The default expiration of an account is mostly 180 days so hey whats the point going to do this again after 180 days?? . There are two methods that can be used to achieve this  Generic Never expiration of all User accounts (This should never be practiced in a production Environment All database users are assigned to something called a PROFILE . The profile controls two aspects of the users database acce...
Post a Comment
Read more

How does one add a day/hour/minute/second to a date value?

DATE is the datatype that we are all familiar  with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future.  The problem with the DATE datatype is its' granularity  when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date: SQL> SELECT  TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS')  "hiredate"   FROM employees; hiredate ------------------- 17.12.1980:00:00:00 20.02.1981:00:00:00 The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours...
5 comments
Read more