Migrate your Oracle database to Azure SQL Database Managed Instance using SSMA 8.0

Posted by

This post has been republished via RSS; it originally appeared at: MSDN Blogs.

SQL Server Migration Assistant 8.0 introduces support for Azure SQL Database Managed Instance as a target. Whether you were planning to migrate or already in the process of converting your database schema – now you have all the tool under your belt!

Before you start converting schemas in the SQL Server Migration Assistant, you need to install SSMA for Oracle Extension Pack on your target Azure SQL Database Managed Instance. This will add additional functionality to your instance that will be used by the converted code produced by SSMA and it only needs to be done once for each instance. To install the extension pack, download Extension Pack installer and follow usual installation steps. Once you get to the second part of the installation process, select Remote instance (Linux or Azure):

Provide connection credentials to your Azure SQL Database Managed Instance, and the install the Utilities database and Extension Pack libraries.

After installation is done, you can jump to the SSMA client tool to start working on your schema conversion. For this walk-through, let's move the well-known HR schema from Oracle to Azure SQL Database Managed Instance.

Open the SSMA client tool, and then create a new project targeting Azure SQL Database Managed Instance:

Connect to source and target servers.

After you have your source and target connected, right-click desired schema in the Oracle Metadata Explorer tree, and then select Convert Schema:

SSMA will load additional information about the objects and convert them to their SQL Server representations. Depending on the size of your schema this may take some time. For our sample HR schema this takes less than 10 seconds and once done you will be presented with the results in a form of conversion errors and warnings. In this simple case we only have five warnings that we need to look into:

As you may notice, four of them are related to the NUMBER datatype and on is about the READ ONLY clause that was ignored. Let’s start with the NUMBER datatype conversion: double-click on of the warnings and it will take you to the place that requires attention.

Here you can see that p_emp_id is defined as job_history.employee_id column’s type, which resolves to an arbitrary NUMBER, since Oracle doesn’t allow precision and scale for procedure arguments. SSMA cannot tell which value will be passed in there (integer or decimal) at runtime, so it assumes anything and uses float(53) datatype on the SQL Server side. Given that float in SQL Server and NUMBER in Oracle support different scale and precision, SSMA notifies you that there might be a potential data loss, depending on the values you decide to store in the column. In our case, since we know that we always pass an integer here – we can just update it to int datatype in SQL Server and move on. Other three issues from this bucket are the same – ID is being passed as an argument – let’s just update all places to use int.

Lastly, we have “WITH READ ONLY” clause on the view that SSMA does not convert, so it notifies you that it was ignored. To make the view non-updatable in SQL Server you can tweak user’s permissions after you deploy the schema. Given that it is not a functional blocker for the migration, lets proceed and deploy our converted schema to the target Azure SQL Database Managed Instance.

Make sure you have converted schema (HR, in our case) as well as ssma_oracle schema checked, then right-click the database node in the target metadata explorer and select Synchronize with Database:

SSMA will analyze existing database state and present you with the changes that are going to be applied:

Review the changes, and then click OK to proceed. SSMA will start loading new objects to the database. This may take some time, depending on the size of your schema. Wait until you see synchronization completion message in the Output window and now your Oracle schema is successfully migrated to Azure SQL Database Managed Instance!

The next step in the migration process is to move your data, and SSMA can assist you with this as well. All you need to do after the schema is migrated is to right-click the Tables node in the Oracle Metadata Explorer, and the select Migrate Data:

Re-enter you source and target credentials and wait until it’s done. At the end you will be presented with the summary, indicating how many rows were moved for each table and whether there were any issues:

At this point your database is fully migrated and ready to use!

For those who are already familiar with SSMA and wondering if there are any limitations when targeting Azure SQL Database Managed Instance, compared to on-premises SQL Server – unfortunately, there are a few. In this initial release, the Tester feature and Server-side data migration are not supported. We will continue working with the Azure SQL Database Managed Instance team to enable all functionality in future versions.

One comment

  1. Hi, This is very useful article and well explained. One doubt i have is regarding the installation of SSMA for Oracle Extension Pack. If our target database is Azure SQL Database instead of Azure SQL Database managed instance then which option we need to select while installing the extension pack. Will it be local instance or Remote instance ?

This articles are republished, there may be more discussion at the original link. But if you found this helpful, you're more than welcome to let us know!

This site uses Akismet to reduce spam. Learn how your comment data is processed.