GitHub Copilot and SSMA: Strap a GenAI conversion booster to your Oracle to SQL Migrations

Overview 

In this blog post we will see demonstration and detailed walk through of how Generative AI capabilities of GitHub Copilot can work together with SQL Server Migration Assistant (SSMA) for Oracle and accelerate code conversion from PL/SQL to T-SQL and simplify Oracle migration journey to Azure SQL. Before we delve into how GitHub Copilot can accelerate your code conversion journey, lets get a brief overview of GitHub Copilot, SSMA for Oracle, Database migrations and the criticality of code conversion in the migration process.  

What is GitHub Copilot? 

GitHub Copilot is an AI coding assistant that helps developers write code faster with less effort, allowing developers to focus on problem solving and collaboration. It improves developer productivity by doing completions, answering coding questions, fix issues, generate unit test cases, jumpstarting your project and much more. GitHub Copilot also has Language Translation ability that can translate code from one Programming language to another, for example: Python to JavaScript or HTML to Markdown, PL/SQL to T-SQL etc. In this demo we will see how GitHub Copilot Language translation capability can simplify your Oracle to SQL Server database migration by automatically converting PL/SQL into T-SQL. 

GitHub Copilot is available as an extension in IDEs, GitHub Mobile as a chat interface, on command line as GitHub CLI and more. In this demo we are going to use Visual Studio Code extension of GitHub Copilot.  

What is SQL Server Migration Assistant (SSMA) for Oracle 

Microsoft SQL Server Migration Assistant (SSMA) for Oracle is a desktop tool to automate migration from Oracle database(s) to SQL Server, Azure SQL Database, Azure SQL Database Managed Instance and Azure SQL Data Warehouse. SSMA for Oracle converts Oracle database objects and loads those objects into SQL Server or Azure SQL, and then migrates data. For more information on how to use SSMA for Oracle, please refer to  SQL Server Migration Assistant for Oracle.

Database Migrations overview 

Database migration is a process of moving data from one or more source platforms to the desired target platforms. Data migration can happen between databases of the same database management system (DBMS) from the same provider or between databases from different database management system (DBMS) providers. For example, Migration of SQL Servers from on-premises infrastructure or non-Azure cloud platforms to Azure SQL (which includes following three products: Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VM) is called Homogenous Migrations and Migration of non-SQL server databases like Oracle, DB2, Sybase etc. to Microsoft SQL Server or Azure SQL is called Heterogenous migrations. Both homogenous and heterogenous are a multi-phase journey with the following phases: 

1. Discovery: Users must first Discover their entire source database estate either in on-prem or in other clouds and determine which of these databases need to be migrated. 

1. Total Cost of Ownership (TCO) comparison analysis between source and target platforms to quantify the potential cost savings by migrating the databases.  

1. Assess the source databases to understand the workload patterns and determine the right configuration of the target database and provision the target. 

1. Convert the code and other source database objects to make them target compatible.  

1. Migrate the data from source to target databases either.  

Conversion using SQL Server Migration Assistant for Oracle 

SQL Server Migration Assistant for Oracle provides extensive conversion rule set engine that converts most of your Oracle objects into PL/SQL code into SQL Server compatible objects and T-SQL with 100% accuracy. Additionally, SSMA provides multiple reusable customization options for mapping datatypes and extending inbuilt rule engine that help you accelerate the overall code conversion process. High-level steps for conversion in SSMA are: 

• Mapping Oracle and SQL Server data types: SSMA for Oracle offers a default set of type mappings, which meets common conversion requirements in most of the cases. This data type mapping is inherited by default at project level for all the underlying object categories and object types. Users can customize them as needed at object category level and create exceptions 

• Assessing Oracle Schemas for conversion: Before loading objects and migrate data to SQL Server, you should determine how complex the migration will be and how much time the migration will take. SSMA for Oracle creates an assessment report that shows the percentage of objects that will be successfully converted, and it also lets you view the specific issues that cause conversion failures. Additionally, SSMA also tells you the amount of manual effort required in hours to convert the objects that could not be automatically converted. 

• Converting Oracle Schemas into SQL Server Schemas: Converting database objects takes the 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 instance of SQL Server. You can then view the objects and their properties by using the SQL Server Metadata Explorer. During the conversion, SSMA prints output messages to the Output pane and error messages to the Error List pane. Use the output and error information to determine whether you have to modify your Oracle databases or your conversion process to obtain the desired conversion results. 

• Loading converted database objects into SQL Server: To load the converted database objects into SQL Server without modification, you can have SSMA directly create or recreate the database objects. To modify the Transact-SQL that is used to create objects for more control over object creation, use SSMA to create scripts. You can then modify those scripts to create each object individually, and even use SQL Server Agent to schedule creating those objects. To secure the converted database objects in SQL Server, you can grant and deny permissions on those objects. It is recommended to set the security permissions before performing data migration. 

More details about the Oracle to SQL Server migration and the conversion process can be found in Migrate Oracle to SQL Server (OracleToSQL) 

GitHub Copilot, a great companion to SSMA in code conversion 

SSMA for Oracle provides comprehensive conversion rule engine that converts majority of the datatypes and objects into SQL Server compatible type with 100 % accuracy. Objects that could not be converted automatically by SSMA, need to be converted manually and this can take multiple 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 could not be converted by SSMA for Oracle. GitHub Copilot is available as a Visual Studio extension, which facilitates conversion of large and complex Oracle procedures and functions to T-SQL procedures and functions with few clicks. Here is a step-by-step guide on how to leverage GitHub Copilot VS Code extension to automate the conversion: 

• Create and view the conversion assessment report generated by SSMA to know the list of all objects that could not be automatically converted by SSMA into SQL Server compatible objects. Here is a screenshot that shows how the assessment report would look like capturing details on number of objects and actual objects that could not be converted (Pie chart on the left) and the amount of manual effort required to convert these objects (Pie chart on the right): 

• Select an object (ex: a PL/SQL procedure or function) that could not be successfully converted by SSMA. As shown the screenshot below, we have selected get_employee_info () procedure which has ref cursor as a return type which is not supported directly in T-SQL 

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

• To setup GitHub Copilot in Visual Studio Code follow the instructions in: GitHub Copilot setup in VSCode. 

• After pasting the PL/SQL procedure in VS Code, hit Ctrl+I to invoke the GitHub Copilot inline chat that lets you ask questions or give specific commands in Natural Language. In the chat interface please type convert PL/SQL to T-SQL and hit enter.  

• In few seconds the entire PL/SQL code is rewritten to convert the return cursor type to a table type and the generated T-SQL function is also correct. We can either Accept or Discard the changes. In this case I will go ahead and accept the suggestion: 

• As a quick check, will copy the generated query into SSMS and see if it can be validated and run successfully: 

• With the T-SQL procedure validated, you can copy the generated T-SQL procedure into the SSMA project and synchronize/load it along with the other converted Oracle objects. 

 Here is the demo video that captures this entire scenario end to end: 

GitHub Copilot can handle even more complex conversion scenarios and help you save lot of manual effort and time by converting them to correct T-SQL syntax with few clicks. Here is another demo video showcasing a complex PL/SQL package with inbuilt PL/SQL procedures and a user defined data type being converted to T-SQL automatically using GitHub Copilot: 

To summarize in this blog post we have seen how the combination of SSMA's rule-based conversion and GitHub Copilot's AI-driven approach could significantly accelerate code conversion, potentially achieving high conversion success rates in the late 80s to 90s percentage range.