Steps to Migrate Oracle Database to Azure SQL Database

Background

In the world of paradigm shifts, many organizations are looking at the prospect of a technology migration, where capabilities are moved to a new set of technologies, supporting and enabling the business for the future.  On other hand, a technology or platform may reach the end of its life for other business reasons, like ease of use, increased cost, etc.

In this article we will discuss what the steps that can be followed to migrate on-premise Oracle database to the SQL Server cloud database, Azure SQL Database.

This technology migration requires good planning and the same thoroughness and processes as any software or hardware project. The Oracle database migration to Azure can be planned as a three phase approach and these phases can have multiple activities for a successful migration.

Migration Process

The end to end migration can be distributed in three phases and each phase consists of one or more activities. The three phases are Pre-migration, Migration and Post-migration. Each phase, with its respective activities are represented in the figure below:

Figure 1 – Oracle Database to Azure SQL Database Migration

The Pre-migration phase primarily has three activities; 1) Analysis, 2) Discover, 3) Assess and Convert. The Analysis, Discover and Assess activities are important to prepare a plan for successful migration. However, the Convert activity will be required in heterogeneous migration scenarios and to convert the source schema to work in the target schema.

The Migration phase primarily can have three activities; 1) Publish Schema, 2) Data Migration, 3) Data Sync. All three activities are important for a successful migration and to decide the cut over from the source to target environment (new source after successful migration).

The Post-migration phase consists of three activities; 1) Validation, 2) Application Remediation, 3) Performance Validation and Optimization.  All these activities will be required repetitively to validate the migration status, remediate application, validate the performance and optimization.

Tools for Migration

Microsoft provides multiple tools to achieve a successful Oracle database migration to Azure SQL Database or any other on-premise SQL Server platform. Some of the important tools are:

1. Microsoft Assessment and Planning (MAP) Toolkit is a tool to provide inventory, assessment, and reporting to simplify the Oracle database migration planning process. MAP provides detailed readiness assessment reports and recommendations with hardware and software information.
2. Microsoft SQL Server Migration Assistant (SSMA) for Oracleis a Microsoft provided tool to automate migration from Oracle database(s) to Azure SQL Database or on-premise SQL Server.
   1. SSMA for Oracle Client: A tool which connects to a source database (Oracle) and target Azure SQL Server to convert Oracle database objects to SQL Server syntax. Once database objects are ready, it loads the objects in Azure SQL Database and migrates data to Azure SQL Database or on-premise SQL Server.
   2. SSMA for Oracle Extension Pack: The SSMA extension pack includes functionalities to emulate Oracle features not natively supported in Azure SQL Database, an application to facilitate direct server-to-server data migration and a test database to support SSMA testing features.
3. Azure Database Migration Service is a fully managed service designed to enable seamless migrations from multiple database sources to Azure Data platforms.

Migration Phases and Activities

There are three phases with multiple activities to achieve end to end Oracle database migration to Azure SQL Database or on-premise SQL Server.

Pre-migration phase is the phase to verify that the source environment is supported and any other prerequisites need to be addressed before triggering the actual migration. This phase has three activities:

1. Analysis activity verifies the source and target environment feasibility for initiating the migration. Additionally, you can leverage Azure Total Cost of Ownership calculator to estimate the cost savings by migrating workloads to Azure.
2. Discover activity is useful to identify the existing data source and details about the features that are being used to prepare a plan for the migration. The MAP tool can be leveraged for the complete inventory scan.

Figure 2 – MAP Toolkit Interface

1. Assess and Convert – The Assess activity is to assess any gap between the source Oracle database and target Azure SQL Database. This can be achieved using the SSMA tool. SSMA for Oracle can review database objects and data, assess Oracle database objects, migrate Oracle database objects to Azure SQL Database and finally, migrate the data to Azure SQL Database. The Convert activity performs schema conversion. After schema conversion, it compares and reviews the structure of the schema to identify the gaps, if there are any.

Migration Phase is the phase to trigger actual migration. After completion of this phase your schema will be migrated to SQL Server and all the Oracle features will be converted to SQL Server. Migration involves publishing the schema and data migration. SSMA is the right tool to achieve this. there are three activities under this phase:

1. Publish Schema: This activity involves the publishing the schema to the target Azure SQL Database using the SSMA tool. SSMA gives you a method to assess the source and target schemas offline and make appropriate changes before publishing the schema to Azure.
2. Data Migration: The activity to migrate source (Oracle) data to the target Azure SQL Database.
3. Data Sync: The activity to confirm that all changes in the source are captured and transferred to the target immediately.

Post-migration phase is the phase to ensure that everything is functioning as expected and efficiently as possible. There are three activities:

1. Validation is the activity to validate the database migration is completed and data has migrated to target environment.
2. Application Remediation is the activity to make require changes to the application after the data is migrated to the target environment. It is expected the application should start consuming the data from the target environment after migration.
3. Performance validation and optimization is the activity to reconcile data accuracy issues and to take care of performance issues with the workloads on Azure SQL Database.

Summary

For any successful technology migration, a defined approach and a solid plan is required. A defined approach will help you to recover from unknown situations. The migration phases consist of multiple activities and each activity has its own set of tasks to accomplish. Microsoft has provided a set of tools and services to support your end to end migration. These tools are not only useful in the migration of Oracle database to Azure SQL Database, but also support other various technology migrations like DB2, MySQL, SAP ASE and Access to Azure SQL Database or on-premise SQL Server platform.

See all articles by Anoop Kumar