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