Migration guide: Oracle to Azure SQL Database

  • Article

Applies to: Azure SQL Database

In this guide, you learn how to migrate your Oracle schemas to Azure SQL Database by using SQL Server Migration Assistant for Oracle (SSMA for Oracle).

For other migration guides, see Azure Database Migration Guides.

Important

Try new Database Migration Assessment for Oracle extension in Azure Data Studio for Oracle to SQL pre-assessment and workload categorization. If you are in early phase of Oracle to SQL migration and would need to do a high level workload assessment , interested in sizing Azure SQL target for the Oracle workload or understand feature migration parity, try the new extension. For detailed code assessment and conversion, continue with SSMA for Oracle.

Prerequisites

Before you begin migrating your Oracle schema to SQL Database:

Pre-migration

After you've met the prerequisites, you're ready to discover the topology of your environment and assess the feasibility of your migration. This part of the process involves conducting an inventory of the databases that you need to migrate, assessing those databases for potential migration issues or blockers, and then resolving any items you might have uncovered.

Assess

By using SSMA for Oracle, you can review database objects and data, assess databases for migration, migrate database objects to SQL Database, and then finally migrate data to the database.

To create an assessment:

  1. Open SSMA for Oracle.

  2. Select File, and then select New Project.

  3. Enter a project name and a location to save your project. Then select Azure SQL Database as the migration target from the drop-down list and select OK.

    Screenshot that shows Connect to Oracle.

  4. Select Connect to Oracle. Enter values for Oracle connection details in the Connect to Oracle dialog box.

  5. Select the Oracle schemas you want to migrate.

    Screenshot that shows selecting Oracle schema.

  6. In Oracle Metadata Explorer, right-click the Oracle schema you want to migrate and then select Create Report to generate an HTML report. Instead, you can select a database and then select the Create Report tab.

    Screenshot that shows Create Report.

  7. Review the HTML report to understand conversion statistics and any errors or warnings. You can also open the report in Excel to get an inventory of Oracle objects and the effort required to perform schema conversions. The default location for the report is in the report folder within SSMAProjects.

    For example, see drive:\<username>\Documents\SSMAProjects\MyOracleMigration\report\report_2020_11_12T02_47_55\.

    Screenshot that shows an Assessment report.

Validate the data types

Validate the default data type mappings and change them based on requirements if necessary. To do so, follow these steps:

  1. In SSMA for Oracle, select Tools, and then select Project Settings.

  2. Select the Type Mapping tab.

    Screenshot that shows Type Mapping.

  3. You can change the type mapping for each table by selecting the table in Oracle Metadata Explorer.

Convert the schema

To convert the schema:

  1. (Optional) Add dynamic or ad hoc queries to statements. Right-click the node, and then select Add statements.

  2. Select the Connect to Azure SQL Database tab.

    1. In SQL Database, enter connection details to connect your database.
    2. Select your target SQL Database instance from the drop-down list, or enter a new name, in which case a database will be created on the target server.
    3. Enter authentication details, and select Connect.

    Screenshot that shows Connect to Azure SQL Database.

  3. In Oracle Metadata Explorer, right-click the Oracle schema and then select Convert Schema. Or, you can select your schema and then select the Convert Schema tab.

    Screenshot that shows Convert Schema.

  4. After the conversion finishes, compare and review the converted objects to the original objects to identify potential problems and address them based on the recommendations.

    Screenshot that shows the Review recommendations schema.

  5. Compare the converted Transact-SQL text to the original stored procedures, and review the recommendations.

    Screenshot that shows the Review recommendations.

  6. In the output pane, select Review results and review the errors in the Error List pane.

  7. Save the project locally for an offline schema remediation exercise. On the File menu, select Save Project. This step gives you an opportunity to evaluate the source and target schemas offline and perform remediation before you publish the schema to SQL Database.

Migrate

After you've assessed your databases and addressed any discrepancies, the next step is to run the migration process. Migration involves two steps: publishing the schema and migrating the data.

To publish your schema and migrate your data:

  1. Publish the schema by right-clicking the database from the Databases node in Azure SQL Database Metadata Explorer and selecting Synchronize with Database.

    Screenshot that shows Synchronize with Database.

  2. Review the mapping between your source project and your target.

    Screenshot that shows Synchronize with the Database review.

  3. Migrate the data by right-clicking the database or object you want to migrate in Oracle Metadata Explorer and selecting Migrate Data. Or, you can select the Migrate Data tab. To migrate data for an entire database, select the check box next to the database name. To migrate data from individual tables, expand the database, expand Tables, and then select the checkboxes next to the tables. To omit data from individual tables, clear the checkboxes.

    Screenshot that shows Migrate Data.

  4. Enter connection details for both Oracle and SQL Database.

  5. After the migration is completed, view the Data Migration Report.

    Screenshot that shows the Data Migration Report.

  6. Connect to your SQL Database instance by using SQL Server Management Studio, and validate the migration by reviewing the data and schema.

    Screenshot that shows validation in SQL Server Management Studio.

Or, you can also use SQL Server Integration Services to perform the migration. To learn more, see:

Post-migration

After you've successfully completed the migration stage, you need to complete a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

Remediate applications

After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this task will require changes to the applications in some cases.

The Data Access Migration Toolkit is an extension for Visual Studio Code that allows you to analyze your Java source code and detect data access API calls and queries. The toolkit provides you with a single-pane view of what needs to be addressed to support the new database back end. To learn more, see the Migrate your Java applications from Oracle blog post.

Perform tests

The test approach to database migration consists of the following activities:

  1. Develop validation tests: To test the database migration, you need to use SQL queries. You must create the validation queries to run against both the source and the target databases. Your validation queries should cover the scope you've defined.
  2. Set up a test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
  3. Run validation tests: Run validation tests against the source and the target, and then analyze the results.
  4. Run performance tests: Run performance tests against the source and the target, and then analyze and compare the results.

Validate migrated objects

Microsoft SQL Server Migration Assistant for Oracle Tester (SSMA Tester) allows you to test migrated database objects. The SSMA Tester is used to verify that converted objects behave in the same way.

Create test case

  1. Open SSMA for Oracle, select Tester followed by New Test Case. Screenshot that shows to create new test case.

  2. Provide the following information for the new test case:

    Name: Enter the name to identify the test case.

    Creation date: Today's current date, defined automatically.

    Last Modified date: Filled in automatically, should not be changed.

    Description: Enter any additional information to identify the purpose of the test case.

    Screenshot that shows steps to initialize a test case .

  3. Select the objects that are part of the test case from the Oracle object tree located in the left side.

    Screenshot that shows step to select and configure object.

    In this example, stored procedure ADD_REGION and table REGION is selected.

    To learn more, see Selecting and configuring objects to test.

  4. Next, select the tables, foreign keys, and other dependent objects from the Oracle object tree in the left window.

    Screenshot that shows step to select and configure affected object.

    To learn more, see Selecting and configuring affected objects.

  5. Review the evaluation sequence of objects. Change the order by clicking the buttons in the grid.

    Screenshot that shows step to sequence test object execution.

  6. Finalize the test case by reviewing the information provided in the previous steps. Configure the test execution options based on the test scenario.

    Screenshot that shows step to finalize object.

    For more information on test case settings,Finishing test case preparation

  7. Click on finish to create the test case.

    Screenshot that shows step to test repo.

Run test case

When SSMA Tester runs a test case, the test engine executes the objects selected for testing and generates a verification report.

  1. Select the test case from test repository and then click run.

    Screenshot that shows to review test repo.

  2. Review the launch test case and click run.

    Screenshot that shows step to run test case

  3. Next, provide Oracle source credentials. Click connect after entering the credentials.

    Screenshot that shows step to connect to oracle source

  4. Provide target SQL Server credentials and click connect.

    Screenshot that shows step to connect to sql target.

    On success, the test case moves to initialization stage.

  5. A real-time progress bar shows the execution status of the test run.

    Screenshot that shows tester test progress.

  6. Review the report after the test is completed. The report provides the statistics, any errors during the test run and a detail report.

    Screenshot that shows a sample tester test report

  7. Click details to get more information.

    Example of positive data validation. Screenshot that shows a sample tester success report.

    Example of failed data validation.

    Screenshot that shows tester failure report.

Optimize

The post-migration phase is crucial for reconciling any data accuracy issues, verifying completeness, and addressing performance issues with the workload.

Note

For more information about these issues and the steps to mitigate them, see the Post-migration validation and optimization guide.

Migration assets

For more assistance with completing this migration scenario, see the following resources. They were developed in support of a real-world migration project engagement.

Title/link Description
Data Workload Assessment Model and Tool This tool provides suggested "best fit" target platforms, cloud readiness, and application or database remediation level for a given workload. It offers simple, one-click calculation and report generation that helps to accelerate large estate assessments by providing an automated and uniform target platform decision process.
Oracle Inventory Script Artifacts This asset includes a PL/SQL query that hits Oracle system tables and provides a count of objects by schema type, object type, and status. It also provides a rough estimate of raw data in each schema and the sizing of tables in each schema, with results stored in a CSV format.
Automate SSMA Oracle Assessment Collection & Consolidation This set of resources uses a .csv file as entry (sources.csv in the project folders) to produce the xml files that are needed to run an SSMA assessment in console mode. The source.csv is provided by the customer based on an inventory of existing Oracle instances. The output files are AssessmentReportGeneration_source_1.xml, ServersConnectionFile.xml, and VariableValueFile.xml.
Oracle to SQL DB - Database Compare utility SSMA for Oracle Tester is the recommended tool to automatically validate the database object conversion and data migration, and it's a superset of Database Compare functionality.

If you're looking for an alternative data validation option, you can use the Database Compare utility to compare data down to the row or column level in all or selected tables, rows, and columns.

The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Azure data platform.

Next steps