SSMA, copilot, DMS, Migration

outline

In this blog post, we will walk through a demo and detailed explanation of how the Generative AI capabilities in GitHub Copilot work with SQL Server Migration Assistant (SSMA) for Oracle to accelerate code transformation from PL/SQL to T-SQL and simplify your migration journey from Oracle to Azure SQL. Before diving into how GitHub Copilot can accelerate your code transformation journey, let’s do a quick overview of GitHub Copilot, SSMA for Oracle, database migration, and the importance of code transformation in the migration process.

What is GitHub Copilot?

GitHub Copilot An AI coding assistant that helps developers write code faster with less effort, allowing developers to focus on solving problems and collaborating. It improves developer productivity by completing tasks, answering coding questions, resolving issues, generating unit test cases, and starting projects. GitHub Copilot also has a language conversion feature that lets you convert code from one programming language to another (e.g., Python to JavaScript, HTML to Markdown, PL/SQL to T-SQL, etc.). In this demo, we’ll see how GitHub Copilot’s language conversion feature simplifies migration from Oracle to SQL Server databases by automatically converting PL/SQL to T-SQL.

GitHub Copilot is available as an extension in your IDE, as a chat interface in GitHub Mobile, and as the GitHub CLI from the command line. This demo uses the Visual Studio Code extension for GitHub Copilot.

What is SQL Server Migration Assistant (SSMA) for Oracle?

Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a desktop tool that automates migrations from Oracle databases to SQL Server, Azure SQL Database, Azure SQL Database Managed Instance, and Azure SQL Data Warehouse. SSMA for Oracle converts Oracle database objects, loads those objects into SQL Server or Azure SQL, and then migrates the data. For more information about how to use SSMA for Oracle, see: SQL Server Migration Assistant for Oracle.

Database Migration Overview

Database migration is the process of moving data from one or more source platforms to a desired target platform. Data migration can occur between databases of the same database management system (DBMS) from the same provider or between databases of different database management system (DBMS) providers. For example, when migrating SQL Server from on-premises infrastructure or non-Azure cloud platforms. Azure SQL (Includes the following three products: Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VM) is called homogeneous migration, while migrating non-SQL Server databases like Oracle, DB2, Sybase, etc. to Microsoft SQL Server or Azure SQL is called heterogeneous migration. Both homogeneous and heterogeneous migrations are multi-step journeys that involve the following steps:

1. find: Users must first discover their entire source database assets, either on-premises or in another cloud, and determine which of these databases need to be migrated.

1. Total Cost of Ownership (TCO) Comparison We conduct analysis between source and target platforms to quantify potential cost savings through database migration.

1. evaluate that Understand workload patterns from your source database, determine the correct configuration for your target database, and provision the target.

1. Switch Makes code and other source database objects compatible with the target.

1. Move Transfers data from a source database to a target database.

Conversion using SQL Server Migration Assistant for Oracle

SQL Server Migration Assistant for Oracle provides an extensive set of conversion rules engine that converts most Oracle objects to PL/SQL code, SQL Server compatible objects, and T-SQL with 100% accuracy. Additionally, SSMA provides several reusable customization options to map data types and extend the built-in rules engine, which helps accelerate the overall code transformation process. The high-level steps for transformation in SSMA are as follows:

• Oracle and SQL Server Data Type Mapping: SSMA for Oracle provides: A set of basic type mappingsIn most cases, it meets the general conversion requirements. This data type mapping is inherited by default at the project level for all base object categories and object types. Users can customize it and make exceptions at the object category level as needed.

• Oracle Schema Evaluation for Conversion: Before you load objects and migrate data to SQL Server, you need to decide how complex the migration is and how long it will take. SSMA for Oracle Generate an evaluation report It shows you the percentage of objects that will be converted successfully, and also shows you the specific issues that caused the conversion to fail. SSMA also reports the amount of manual work (in hours) required to convert objects that could not be converted automatically.

• Convert Oracle schema to SQL Server schema: Converting database objects Imports object definitions from Oracle, converts them to similar SQL Server objects, and then loads this information into the SSMA metadata. It does not load the information into the SQL Server instance. You can then use SQL Server Metadata Explorer to view the objects and their properties. During the conversion, SSMA prints output messages to the Output window and error messages to the Error List window. Use the output and error information to determine whether you need to modify your Oracle database or the conversion process to achieve the desired conversion results.

• Load the converted database objects into SQL Server: to Load the converted database objects into SQL Server. You can create or re-create database objects directly in SSMA without modifying them. To improve control over object creation, you can use SSMA to create scripts to modify the Transact-SQL used to create objects. You can then modify those scripts to create each object individually and use SQL Server Agent to schedule the creation of those objects. To secure the converted database objects in SQL Server, you can grant and deny permissions on those objects. It is recommended that you set security permissions before you perform a data migration.

More detailed information about the migration and conversion process from Oracle to SQL Server can be found here: Migrate Oracle to SQL Server (OracleToSQL)

GitHub Copilot is a great companion for SSMA code conversion

SSMA for Oracle provides a comprehensive conversion rules engine that converts most data types and objects to SQL Server compatible types with 100% accuracy. Objects that SSMA cannot automatically convert must be converted manually, which can take hours of manual effort. Users can leverage the full power of Generative AI capabilities available in GitHub Copilot to automate the conversion of Oracle database objects that SSMA for Oracle cannot convert. GitHub Copilot is available as a Visual Studio extension and can convert large and complex Oracle procedures and functions to T-SQL procedures and functions in just a few clicks. Here is a step-by-step guide on how to automate conversions using the GitHub Copilot VS Code extension.

• Generate and view conversion evaluation reports You can see a list of all objects generated by SSMA that cannot be automatically converted to SQL Server compatible objects in SSMA. A screenshot of what the assessment report looks like, capturing details about the number of objects that cannot be converted, the actual number of objects (pie chart on the left), and the amount of manual effort required to convert these objects (pie chart on the right).

• Select the object (for example, a PL/SQL procedure or function) that SSMA cannot successfully convert. As you can see in the screenshot below, I have selected the get_employee_info() procedure, which has a return type of ref cursor, which is not directly supported in T-SQL.

• Next step is to copy the query, open the GitHub Copilot extension in VSCode and paste it into a new file saved with a .sql extension. In this case, I saved the file with the PL/SQL code as ora2sql.sql.

• After pasting a PL/SQL procedure in VS Code, press Ctrl+I to invoke the GitHub Copilot inline chat, where you can ask questions or issue specific commands in natural language. In the chat interface, type convert PL/SQL to T-SQL and press Enter.

• In a few seconds, the entire PL/SQL code is rewritten, converting the return cursor type to a table type, and the generated T-SQL function is also correct. You can accept or cancel the change. In this case, I will accept the suggestion.

• For a quick check, copy the generated query into SSMS to verify and see if it can be executed successfully.

• Once the T-SQL procedures are validated, you can copy the generated T-SQL procedures into an SSMA project and synchronize/load them along with other converted Oracle objects.

Here’s a demo video of this entire scenario from start to finish:

GitHub Copilot can handle much more complex conversion scenarios and save you a lot of manual effort and time by converting it to the correct T-SQL syntax with just a few clicks. Here is a demo video of a complex PL/SQL package with built-in PL/SQL procedures and user-defined data types being automatically converted to T-SQL using GitHub Copilot.

To summarize in this blog post, we looked at: Combining SSMA’s rules-based transformations with GitHub Copilot’s AI-driven approach can significantly accelerate code transformation, potentially achieving high transformation success rates in the high 80s to high 90s.