Leveraging GitHub Copilot for T-SQL Code Conversion: A Deep Dive into Code Explainability
Converting database code between different relational database management systems (RDBMS) is an essential part of database migration , especially when moving from Oracle’s PL/SQL to SQL Server’s T-SQL. Both Oracle and SQL Server have unique syntax, functions, and programming conventions that require significant adjustments when converting complex SQL queries, stored procedures, or functions. While manual conversion can be a painstaking process, GitHub Copilot, an AI-powered code assistant, can ease this burden by offering real-time suggestions and automating many aspects of the conversion. In the first part of this two-part blog, we explore how GitHub Copilot can be a powerful tool for code conversion, helping developers quickly adapt code from one language or framework to another. By providing context-aware suggestions and completions, Copilot simplifies the process of rewriting and refactoring code. In the second part, we dive deeper into Copilot’s explainability feature, showcasing how it enhances the code conversion process from Oracle to Azure SQL. Code explainability in GitHub Copilot refers to the tool’s ability to offer suggestions and comments that help developers understand what a given piece of code is doing. When dealing with database migrations, such as converting Oracle SQL code to Azure SQL (SQL Server), GitHub Copilot’s ability to explain, suggest, and refactor code can ease the transition. It can assist in making the conversion process smoother, more efficient, and less error-prone by explaining the logic behind Oracle-specific queries and suggesting corresponding changes in Azure SQL’s syntax We'll go through multiple examples, analyze the differences between the two languages, and show how GitHub Copilot handles these challenges. Understanding the Key Differences: PL/SQL vs T-SQL Before jumping into examples, it's important to understand few of the fundamental differences between PL/SQL (Oracle) and T-SQL (SQL Server): Syntax: While both are procedural SQL dialects, there are key differences in the way they handle variables, control structures, and flow control. Functions: Each platform has its own set of built-in functions (e.g., string manipulation, date handling, etc.), and these need to be mapped correctly during conversion. Error Handling: Error handling and exception management differ between the two, with PL/SQL using EXCEPTION blocks and T-SQL using TRY...CATCH. Cursor Handling: While both support cursors for iterating through results, their syntax differs. Leveraging GitHub Copilot for Oracle PL/SQL to SQL Server T-SQL Code Conversion: A Deep Dive into Complex Examples with Explainability Converting database code between different relational database management systems (RDBMS) is an essential part of database migration , especially when moving from Oracle’s PL/SQL to SQL Server’s T-SQL. Both Oracle and SQL Server have unique syntax, functions, and programming conventions that require significant adjustments when converting complex SQL queries, stored procedures, or functions. While manual conversion can be a painstaking process, GitHub Copilot, an AI-powered code assistant, can ease this burden by offering real-time suggestions and automating many aspects of the conversion. In the first part of this two-part blog, we explore how GitHub Copilot can be a powerful tool for code conversion, helping developers quickly adapt code from one language or framework to another. By providing context-aware suggestions and completions, Copilot simplifies the process of rewriting and refactoring code. In the second part, we dive deeper into Copilot’s explainability feature, showcasing how it enhances the code conversion process from Oracle to Azure SQL. Code explainability in GitHub Copilot refers to the tool’s ability to offer suggestions and comments that help developers understand what a given piece of code is doing. When dealing with database migrations, such as converting Oracle SQL code to Azure SQL (SQL Server), GitHub Copilot’s ability to explain, suggest, and refactor code can ease the transition. It can assist in making the conversion process smoother, more efficient, and less error-prone by explaining the logic behind Oracle-specific queries and suggesting corresponding changes in Azure SQL’s syntax We'll go through multiple examples, analyse the differences between the two languages, and show how GitHub Copilot handles these challenges. Understanding the Key Differences: PL/SQL vs T-SQL Before jumping into examples, it's important to understand few of the fundamental differences between PL/SQL (Oracle) and T-SQL (SQL Server): Syntax: While both are procedural SQL dialects, there are key differences in the way they handle variables, control structures, and flow control. Functions: Each platform has its own set of built-in functions (e.g., string manipulation, date handling, etc.), and these need to be mapped correctly during conversion. Error Handling: Error handling and exception management differ between the two, with PL/SQL using EXCEPTION blocks and T-SQL using TRY...CATCH. Cursor Handling: While both support cursors for iterating through results, their syntax differs. Example 1: Complex Stored Procedure Conversion Let’s start with a complex example that involves handling parameters, cursors, and error handling. We'll look at a PL/SQL stored procedure in Oracle that processes employee records and outputs their details. CREATE OR REPLACE PROCEDURE GetEmployeeDetails (emp_id IN NUMBER) IS CURSOR emp_cursor IS SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE employee_id = emp_id; emp_record emp_cursor%ROWTYPE; emp_full_name VARCHAR2(100); BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; -- Concatenate first and last name emp_full_name := emp_record.first_name || ' ' || emp_record.last_name; -- Output employee details DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id); DBMS_OUTPUT.PUT_LINE('Full Name: ' || emp_full_name); DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(emp_record.hire_date, 'YYYY-MM-DD')); END LOOP; CLOSE emp_cursor; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('An error occurred.'); END; Oracle PL/SQL Example In Oracle, we might have a procedure that uses a cursor to fetch employee records and print them. The procedure also includes error handling for potential issues during execution. Key Points in Oracle PL/SQL: Cursor: PL/SQL uses CURSOR for iterating through rows. Concatenation: Oracle uses || for string concatenation. Error Handling: PL/SQL uses EXCEPTION blocks for handling errors such as NO_DATA_FOUND. Converting to SQL Server T-SQL with GitHub Copilot When you type the PL/SQL code into an IDE with GitHub Copilot enabled (e.g., Visual Studio Code), Copilot will start suggesting the equivalent SQL Server T-SQL code. Here’s how Copilot converts the procedure. Copilot’s Suggested SQL Server T-SQL Code CREATE PROCEDURE GetEmployeeDetails (@emp_id INT) AS BEGIN DECLARE @emp_id INT, @first_name NVARCHAR(50), @last_name NVARCHAR(50); DECLARE @emp_full_name NVARCHAR(100); DECLARE emp_cursor CURSOR FOR SELECT employee_id, first_name, last_name, hire_date FROM employees WHERE employee_id = @emp_id; OPEN emp_cursor; FETCH NEXT FROM emp_cursor INTO @emp_id, @first_name, @last_name, @hire_date; WHILE @@FETCH_STATUS = 0 BEGIN -- Concatenate first and last name SET @emp_full_name = @first_name + ' ' + @last_name; -- Output employee details PRINT 'Employee ID: ' + CAST(@emp_id AS NVARCHAR); PRINT 'Full Name: ' + @emp_full_name; PRINT 'Hire Date: ' + CONVERT(NVARCHAR, @hire_date, 23); FETCH NEXT FROM emp_cursor INTO @emp_id, @first_name, @last_name, @hire_date; END CLOSE emp_cursor; DEALLOCATE emp_cursor; BEGIN TRY -- Error handling code (example) END TRY BEGIN CATCH PRINT 'An error occurred.'; END CATCH END; Key Points in T-SQL: Cursor: SQL Server uses DECLARE CURSOR to declare a cursor, similar to PL/SQL but with different syntax for opening and fetching. Concatenation: SQL Server uses + for string concatenation. Error Handling: SQL Server uses TRY...CATCH for error handling, unlike PL/SQL’s EXCEPTION block. GitHub Copilot provides a T-SQL equivalent for an Oracle PL/SQL stored procedure. Explanation of the Conversion Process Cursor Declaration and Fetching: PL/SQL uses CURSOR with OPEN, FETCH, and CLOSE to process rows. T-SQL uses DECLARE CURSOR to define the cursor and fetches rows with FETCH NEXT. Both approaches are similar, but T-SQL requires explicit DEALLOCATE to release the cursor. Error Handling: PL/SQL uses EXCEPTION blocks to handle errors like NO_DATA_FOUND. T-SQL uses TRY...CATCH blocks to capture and handle errors, a major difference in error handling between the two systems. String Concatenation: PL/SQL uses || for concatenating strings, while T-SQL uses +. This is a simple yet crucial change when converting between the two dialects. Example 2: Complex Query with Date Handling Let’s now look at a query that involves date handling and conditional logic. These are often tricky when converting between PL/SQL and T-SQL due to differences in how each platform handles dates. Oracle PL/SQL Query Example SELECT employee_id, first_name, hire_date, CASE WHEN hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD') THEN 'Veteran' ELSE 'New Hire' END AS employee_status FROM employees WHERE hire_date BETWEEN TO_DATE('1990-01-01', 'YYYY-MM-DD') AND SYSDATE; Key Points in PL/SQL: TO_DATE: Used to convert strings into dates. SYSDATE: Returns the current system date. CASE: Used for conditional logic to categorize employees as either "Veteran" or "New Hire". Copilot’s Suggested SQL Server T-SQL Code SELECT employee_id, first_name, hire_date, CASE WHEN hire_date < CONVERT(DATE, '2000-01-01', 23) THEN 'Veteran' ELSE 'New Hire' END AS employee_status FROM employees WHERE hire_date BETWEEN CONVERT(DATE, '1990-01-01', 23) AND GETDATE(); Key Points in T-SQL: CONVERT: SQL Server uses CONVERT for date formatting, similar to Oracle’s TO_DATE, but with different syntax and style codes. GETDATE(): Equivalent to Oracle’s TO_DATE In conclusion, GitHub Copilot streamlines code conversion by offering both quick suggestions and detailed explanations. The first part highlighted its ability to assist with code transitions, while the second part focused on how its explainability feature enhances the Oracle to Azure SQL migration, providing guided, efficient, and error-free conversion. This combination accelerates development and reduces potential issues during migration.
Release: Azure SQL Migration extension for Azure Data Studio v1.5.6
We're delighted to announce the release of the latest version of the Azure SQL Migration extension for Azure Data Studio, v1.5.6. This release provides you with Azure Database Migration Service’s new features like: 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview. 3) Enhanced login migration experience - Public Preview. What is new in Azure SQL Migration extension v1.5.6? 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance: The Next-gen General Purpose service tier is an architectural upgrade to the existing General Purpose service tier that can be used for new and existing instances. Now, the Azure Data Studio extension for Azure Database migration service – Azure SQL Migration support Next-gen General Purpose as SKU recommendation for Azure SQL Managed Instance. For details, refer here. This service tier provides better performance, throughput, greater storage capacity and support more than 100 databases on a single instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview: With Azure SQL Migration v1.5.6, now users can generate ARM templates directly based upon the SKU recommendation generated using performance data collected from the source. User can use these ARM templates for all the Azure SQL offerings – Azure SQL VM, Azure SQL MI and Azure SQL DB and easily create the Azure SQL Target for the migrations. To create and deploy the Azure SQL Target, users have two options: a) Copy or save the ARM template in JSON and use Azure CLI, PowerShell and other deployment operations. b) Using Deploy-to-Azure button, then provide the Azure blob storage account details to store the template and deploying it though the Azure Portal. This feature is in Public Preview and will help you to streamline the Azure SQL target creation using ARM templates, can automate deployments and use the practice of infrastructure as code, deploy them quickly and CI/CD integration. 3) Enhanced login migration experience - Public Preview: After completing the data migration, the next critical step is to setup the authentication and authorization for the databases and thus login migration becomes the critical step in the migration journey. Azure SQL Migration extension supports Login migration (Public Preview) and now we have enhanced its experience by adding Pre-requisites validation checks to ensure all the requirements are in place for successful login migrations. Currently, only Azure SQL Managed Instance and SQL Server on Azure Virtual Machines targets are supported. Resources For more information about the extension and Azure Database Migration Service, see the following resources. Azure Database Migration Service documentation Migrate databases using the Azure SQL Migration extension One-click SQL Migration PoC environment Architecture of Azure Database Migration Service | Microsoft Community HubGitHub Copilot and SSMA: Strap a GenAI conversion booster to your Oracle to SQL Migrations
In this blog, we’ll explore how GitHub Copilot and SQL Server Migration Assistant (SSMA) for Oracle can supercharge your code conversion journey from PL/SQL to T-SQL. Learn how both these tools bring comprehensive conversion rule set and Generative AI capabilities together to simplify and expedite your migration journey from Oracle to Azure SQL.General Availability: Online migration for Azure Database for MySQL using Azure DMS
We're pleased to announce general availability of online migration for Azure Database for MySQL using Azure Database Migration Service (DMS). With an online migration, businesses can now migrate an instance of Azure Database for MySQL - Single Server or their on-premises MySQL instance to Azure Database for MySQL - Flexible Server with minimal downtime for critical applications, limiting the impact to service level availability.Migrate Amazon RDS SQL DB to Azure SQL Database using Migration Extension for Azure Data Studio
In this blog post, I will demonstrate how to use the Azure Migration extension of Azure Data Studio to migrate AWS RDS SQL Server database to Azure SQL Database offline. The Azure SQL migration extension for Azure Data Studio enables you to assess, get right-sized Azure recommendations and migrate your SQL Server databases to Azure.Azure DMS - MySQL Consistent Backup now in Preview
We are pleased to announce preview of MySQL Data Migration - Consistent Backup feature, which allows users to take a Consistent Backup of a MySQL server without losing data integrity at source because of ongoing CRUD (Create, Read, Update, and Delete) operations.An end-to-end process for lifting and shifting your applications to Azure
This blog describes how to streamline the migration journey for Microsoft Azure web workloads across your application and relevant databases. By taking advantage of Azure Migrate, the Azure App Service Migration Assistant, the Data Migration Assistant, and Azure Database Migration Service, we simplify the migration of your web apps and databases to Azure with minimal or no code changes.
