Step-by-Step Guide to Running SSMA for Oracle

This document is meant to be used as a Quick Start Guide to migrate schema & data from Oracle to Azure SQL DB using SSMA for Oracle v7.6.0. The complete documentation can be found at https://docs.microsoft.com/en-us/sql/ssma/oracle/sql-server-migration-assistant-for-oracle-oracletosql

Steps

  1. Ensure Oracle Client Drivers are already installed on the Windows machine
    1. For example, if connecting to Oracle 11g, ensure Oracle Client Driver for 11g or higher is installed
  2. Install SSMA for Oracle v7.6.0 from this link: https://www.microsoft.com/en-us/download/details.aspx?id=54258
    1. Install on a Windows machine with network access to the Oracle instances.
    2. When the target is SQL Server On-Prem or IaaS, then in addition to installing SSMA, the SSMA Extension pack needs to be installed on the computer that is running SQL Server: https://docs.microsoft.com/en-us/sql/ssma/oracle/installing-ssma-components-on-sql-server-oracletosql
  3. Open SSMA for Oracle v7.6.0
  4. Create a new project using the File menu

In the New Project dialog, specify the Project Name & Location. Make sure to select the Migrate To drop down as SQL Azure as the target platform and click OK.

Click Connect to Oracle

Enter your Oracle server info then click Connect

Once the connection is established, all the Oracle Schemas and Objects are listed in Oracle Metadata Explorer

Click on the schema that you want to convert, to show the details of the schema on the right details pane

Click on Type Mapping to confirm the source to target data type mapping. Use the Edit button to make changes (if needed) to the default mapping shown.

Expand Schemas and select the schema you want to migrate. Use the Create Report button on the top ribbon or right click on the schema name and choose Create Report to generate the SSMA migration report. (You may be prompted to re-enter the credentials and reconnect to the source system).

Once the report is created, the HTML report will be opened in a Web Browser

At this point you can save the project if you want to do some offline analysis. Choose Save Project from the File menu.

This will bring up the Save Metadata dialog box which will let you select the schemas that you want offline information about so that if you share the SSMA project with others they will not need to connect to the source system for offline analysis. Select the schemas you want and choose Save. (You may be prompted to re-enter the credentials and reconnect to the source system).

Once the report is generated, the next step is to Convert Schema which will create the corresponding schema code for the target environment – SQL Azure DB. Click on Connect to SQL Azure on the top ribbon to connect to your target environment.

Specify the SQL Azure DB connection details in the dialog box

Once the connection is successful, all the objects in SQL Azure DB are listed in SQL Azure Metadata Explorer

Select the schema you want to convert in Oracle Metadata Explorer and then click on Convert Schema button on the top ribbon or right click on the schema and choose Convert Schema

Once the schema is converted, the converted objects will be listed under the SQL Azure Metadata Explorer. The schemas are just created in SSMA but are not actually committed/persisted in the target SQL Azure DB at this point.

The schema creation script for the target SQL Azure DB can now be saved at this point for future reference or offline reading. Select the schemas in SQL Azure Metadata Explorer for which you want to save the script then right click on Schemas and choose Save as Script

Now that the schema is converted to target Azure SQL DB, the same can be committed/persisted into the target system. Under SQL Azure Metadata Explorer, choose the schemas that you want to commit and the 2 assemblies starting with the name SSMA4Oracle, right click on the database name then choose Synchronize with Database

This will bring up the dialogue box showing the differences in Local Metadata vs Database. Click on the button Hide Equal Objects at the top to see only the difference.

Click on OK to initiate synchronizing with database

Once the Synchronize with Database operation is complete, connect to the target Azure SQL DB using a different tool like SSMS. Expanding the different objects under the database should now list all the new objects that were converted from Oracle.

Now that the schema is created in the target Azure SQL DB, we can proceed to migrating the actual data from Oracle. Under Oracle Metadata Explorer, select the schema for which you want to migrate the data. Right click on the schema name and choose Migrate Data. (May be prompted to connect to Oracle & Azure SQL DB)

Note: When the target is SQL Server On-Prem or IaaS, follow the instructions provided at https://docs.microsoft.com/en-us/sql/ssma/oracle/migrating-oracle-data-into-sql-server-oracletosql to ensure Server Side Data Migration settings are setup up correctly before initiating data migration.

Once the data migration is complete, a Data Migration Report is shown listing all the tables and the total rows that was migrated.

Click on Save Report if you want an offline copy of the Data Migration Report which can be save in a CSV format

Once the data migration is complete, connect to the target Azure SQL DB using a different tool like SSMS and query a table to confirm that the data was transferred successfully from Oracle to Azure SQL DB.