Data Modernization has become important nowadays. Organizations are looking into options to migrate their on-premises database to a cloud and some other heterogeneous databases. In this article, we will see the strategy and options to migrate the Oracle database to the Azure SQL database. We will be leveraging the SSMA tool for migrating the database.
While doing the database migration we need to take care of multiple things to do a successful migration. I have categorized those in the below few stages which we can follow to execute the migration process in general.
Planning: While doing the migration lot of planning is required to execute the entire process successfully. In this stage or phase, an analysis of the source and target system needs to be done. Check for common issues between the two systems. Check the amount of data that needs to be moved.
Data Backup: Before starting the migration process, take a backup of all your data, if you face any error or issue and your data gets corrupted you will have a backup to restore the data to its original point.
Assessment: In this phase, both the environment needs to be assessed thoroughly. Find out the compatibility between the target systems and layout the plans for schema changes or data type transformation if any. we can generate a report using various tools to see the comparison of both source and target systems. This report can help you to understand if there are any errors or warnings between system datatypes or any other constraints.
Execute Migration: After all the above steps, it’s time to do actual migration, in this phase, we will execute Migration. First, we will move database objects schemas and various other dependencies to the destination database and then will migrate the data. We can leverage multiple tools or ways to do this activity.
Audit or Testing: Once the migration is complete, ensure there are no connectivity problems with source and target systems. The goal is to ensure all data migrated is correct, secure, and in the proper location.
Tools for Data Migration
There are multiple tools for a successful Oracle database migration to Azure SQL Database or on-premises SQL Server. Below are the important tools that can be used for doing Oracle data migration to SQL Server.
Microsoft recommends using Azure Migrate to simplify the migration process. Azure Migrate provides discovery, assessment, and migration capabilities for applications, infrastructure, and data.
Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a Microsoft-provided tool to automate migration from Oracle database(s) to Azure SQL Database or on-premises SQL Server. SSMA for Oracle Client: it connects to a source database (Oracle) and targets Azure SQL Server to convert Oracle database objects to SQL Server syntax. Once the object’s schema is ready, it loads the object’s schema in Azure SQL Database and migrates data to Azure SQL Database.
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.
Also, Azure DMS (Database Migration Service) is a fully managed service to enable migrations from multiple sources to Azure platforms. You can leverage it for doing various types of data migration.
Oracle to Azure SQL Database Migration
Now it is time to do some practical. we will use SSMA and see how this tool works and what various steps are required to perform the migration.
Download SSMA from the Microsoft website. There are multiple SSMA versions are available. Download the correct version. https://www.microsoft.com/en-us/download/details.aspx?id=54258
In SSMA we need to create a migration project. To create a migration project, click on the file new project. As shown in below screenshot in the new project dialog box, we need to provide the name of the project, the location to save project metadata or files related to the project and most important option Migrate to. In the migrate to option you can see we have various versions of SQL Server and Azure Pass databases. We can also see the option of Managed Instance and Azure SQL data warehouse, which is Azure Synapse dedicated SQL pool.
For this migration, we are using the Azure SQL database as the destination database. We will select Azure SQL Database in migrate dropdown.
Once we say ok to creating the project, we can see in the below screenshot, there is an option of connecting to the Azure SQL database.
Now we need to connect to the Oracle database and select the database objects which need to be migrated to the Azure SQL database.
In the below screenshot to connect to the oracle server, we need to provide the values like server name, server port, Oracle SID, username and password. You need to provide the correct Oracle SID to connect to the proper SQL server.
Once connected to the Oracle server we can see the list of the Schema and database objects as shown in the below screenshot. Out of these schemas, we can check only schemas which we want to migrate. For our demo, we have selected a basic schema which is having a few tables.
Once we will say ok, we can see there are all the tables or database objects for that schema in the left menu of the oracle as shown in the below screenshot.
Now we need to connect to the Azure SQL database as our destination DB is the Azure SQL database. Click on connect to Azure SQL database. We can see the dialog box with various details required to connect to the Azure SQL database as shown in the below screenshot.
If you notice, there are multiple authentication options available to connect to SQL Server. We will be using the SQL Server Authentication method.
After providing server details and the database name which you have created on Azure. we will connect using the SQL Server Authentication method. If you are connecting using your on-premises machine or laptop, then allow your IP in the Azure SQL network option.
Once the connection is established, we can see the connection details like server name, Database name in the left pane SQL Database Metadata explorer.
Now we need to click on create a report to see the schema conversion. It will generate a report which will show a detailed analysis of all the tables in the schema and how they are compatible with the destination types.
The below screenshot is from the generated report. It shows the tables which are having an issue and the tables which are compatible with the database 100 percent.
This report will be a detailed report for all the database objects highlighting the error for each of the tables and the column. We can utilize it to see what the result of our schema conversion could be and what changes we need to do to move forward. As per the recommendation, we need to either fix issues or move the object which is permissible and will not impact.
Now once we are convinced that we can move the schema to Azure SQL Database click on convert schema. We can see now the table schema’s metadata is created in Azure SQL Database shown in the below screenshot.
We need to publish those schemas to the Azure SQL database before moving the data. Right-click on Azure SQL database to publish.
Below is the dialog box which will appear after clicking on synchronize the database. We will check and verify the database objects which we want to publish to the Azure SQL database.
Once the synchronization is completed, we need to login into the Azure portal and check in the Azure SQL database for the object which we have moved. In the below screenshot you can see there are 4 tables that we have moved from the oracle that is created in the Azure SQL demo Database.
As of now the schema and other related dependencies are migrated to the Azure SQL database. To Migrate data, after selecting oracle schema we need to click on Migrate Data on top. See the below screenshot for reference.
It will migrate the data for the tables which are selected. Once the entire migration process is completed. There will be a report generated for the same. For this example, are tables system tables and those are blank that’s why the no of rows is showing zero. However, below is the report which you will get once the data will be migrated to the Azure SQL database.
As we can see database schema and data are migrated to Azure SQL Database, and we can validate the object and database.
Conclusion
We have seen how we can easily migrate the oracle database to the Azure SQL Server database using SSMA. You can leverage SSMA (SQL Server Migration Assistant) and do the data modernization of your organization’s Oracle databases and move them to the cloud. SSMS provides where types of reports and details so you can evaluate the risk well in advance.
Skill summary:
1. SQL Server DB Administration
2. SQL Server DB Model Development
3. SQL Server TSQL or complex query writing
4. MSBI SSIS, SSRS, and SSAS expertise
5. Various other BI tool Expertise
- Understanding the Scalability in Oracle Database - March 9, 2023
- Migrating Oracle Database to Azure SQL Database - February 9, 2023
- Common SQL Interview Questions and Answers - January 6, 2023