Data Migration - Azure SQL MI and Azure SQL DB
Data Migration Between Azure SQL Managed Instance and Azure SQL Database: Methods and Considerations As of this writing, Azure Database Migration Service (DMS) doesn't support SQL MI nor SQL DB as the source database (Supported migration scenarios). As an alternative to use DMS, one of the common support requests we receive from Azure SQL customers is about the best ways to migrate data between Azure SQL Managed Instance (SQL MI) and Azure SQL Database (SQL DB), or vice versa. This post outlines the primary methods available, their pros and cons, and key considerations to help you choose the right approach for your scenario. 1. BACPAC Export/Import Overview: This is a straightforward method where you export your database schema and data to a BACPAC file, then import it into the target environment. Key Points: Simplicity: Easy to use for small to medium-sized databases. Limitations: The BACPAC file size is limited to 200GB when exporting to Azure Blob Storage. Considerations: Larger databases may require splitting or alternative methods. Some features and objects (like certain types of indexes or security settings) may not be fully supported in BACPAC exports/imports. 2. Transactional Replication Overview: If your business requires data to be online during the migration, transactional replication may be your choice. Key Points: Online Migration: Enables near real-time data replication, making it suitable for scenarios where minimal downtime is required. Initialization Challenges: Large tables (having size of hundreds of GBs or more) can make the initial snapshot and data transfer challenging. One-way Only: This method only supports migration from SQL MI to SQL DB, not the other way around. SQL DB cannot act as a publisher due to the lack of Replication Agent (SQL Server Agent) support. 3. Azure Data Factory (ADF) Overview: ADF is a flexible, cloud-based ETL service that can orchestrate data movement between SQL MI and SQL DB. Key Points: Flexibility: Supports both offline and (with limitations) online migrations. Learning Curve: Some initial setup and learning may be required if you’re new to ADF. Online Migration Limitations: Online migration using ADF requires configuring Change Data Capture (CDC) on the source, which is not supported for SQL DB as a source. This means online migration is only feasible from SQL MI to SQL DB, not the reverse Here’s a sample decision matrix to help you choose the best migration method between SQL MI and SQL DB Criteria / Scenario BACPAC Export/Import Transactional Replication Azure Data Factory Direction: SQL MI -> SQL DB ✔️ ✔️ ✔️ Direction: SQL DB -> SQL MI ✔️ ❌ ✔️ Database Size < 200GB ✔️ ✔️ ✔️ Database Size > 200GB ❌ ✔️ ✔️ Minimal Downtime Needed ❌ ✔️ Partial* Online Migration ❌ ✔️ Partial* Learning Curve Low Medium Medium *ADF can support near-online migration if Change Data Capture (CDC) is enabled on the source, but CDC is not supported for SQL DB as a source.Announcing retirement of Microsoft Data Migration Assistant (DMA) Tool
Announcement As part of our ongoing commitment to deliver streamlined and modern migration solutions, Microsoft regularly evaluates its tools and services to ensure they meet evolving customer needs. In line with this effort, we are announcing the retirement of the Microsoft Data Migration Assistant (DMA) tool, effective July 16, 2025. After this date, the DMA tool will no longer be available for download from the Microsoft Download Center. Important Dates Announcement date: June 16, 2025 Retirement date: July 16, 2025 Alternative Options for Migration and Assessment As DMA reaches the end of its lifecycle, customers are encouraged to leverage alternative tools and features for assessment and data migration. Below, we outline the alternatives: Upgrade to higher version of SQL Server If you are seeking to upgrade to higher versions of SQL Server you can now use the new Migration component integrated into SQL Server Management Studio (SSMS). For detailed steps please see documentation for version upgrade using SSMS here: https://learn.microsoft.com/ssms/migrate-sql-server-component Migration to Azure SQL If you relied on DMA for assessments and migration to Azure SQL you have several options: SQL Server enabled by Azure Arc (Assessments) In addition to all the core benefits of enabling Azure Arc, SQL Server enabled by Azure Arc also provides automatic migration assessments that are continuously updated on a weekly schedule by default. See more details of the continuous Migration Assessment in SQL Server enabled by Azure Arc here : https://learn.microsoft.com/sql/sql-server/azure-arc/migration-assessment Azure Database Migration Service (DMS) automation using Azure Powershell and Azure CLI You can use the datamigration module in Azure PowerShell and Azure CLI to perform assessments and migration. The ability to script and automate enables migration factory pattern approaches. See the documentation for automating assessments and migrations here : https://learn.microsoft.com/ azure/dms/migration-dms-powershell-cli. You can also find more information and sample scripts here: https://github.com/Azure-Samples/data-migration-sql Azure Portal Azure Migrate (Assessments) For large scale assessments such as datacenter exit scenarios, the Azure Migrate provides SQL Assessment at scale. Deploying the Azure Migrate appliance facilitates discovery, assessment, and business case evaluation at large scale for not just SQL Server but many other workloads as well. Learn more about SQL assessments in Azure Migrate here: https://learn.microsoft.com/en-us/azure/migrate/how-to-create-azure-sql-assessment Azure Database Migration Service (Migration) For SQL Server migration to Azure SQL offerings, you can use the Azure Database Migration Service (DMS). This service supports migration to all Azure SQL offerings. For example, see how you can migrate to Azure SQL DB using DMS portal here : https://learn.microsoft.com/data-migration/sql-server/database/database-migration-service?tabs=portal. Additional Information Azure Data Studio is on the deprecation path and we are building the replacement assessment and migration experiences in SQL Server Management Studio (SSMS) 1 . Till then, for smaller scale scenarios, you can continue to use the Azure SQL Migration extension in Azure Data Studio (ADS) for assessment and migration to Azure SQL. Learn more about Azure SQL Assessment and Migration in Azure Data Studio here: https://learn.microsoft.com/azure-data-studio/extensions/azure-sql-migration-extension For more details and detailed migration guides, refer to the Azure SQL Migration Guide here: https://learn.microsoft.com/en-us/data-migration/sql-server/overview Next Steps We encourage you to begin transitioning to alternative migration tools and solutions well ahead of the DMA tool's retirement date (July 16, 2025). This will ensure smooth continuity in your migration and assessment workflows. Thank you for your understanding and cooperation during this transition. Disclaimer The information provided in this announcement is for general informational purposes only and reflects Microsoft’s current plans as of the announcement date. All product features, timelines, and retirement schedules are subject to change without notice. Users are responsible for verifying the accuracy of product capabilities and ensuring that all migration activities are conducted in accordance with applicable technical documentation and licensing terms. Microsoft does not guarantee the performance or availability of any third-party tools or scripts referenced herein. For the most up-to-date guidance, please refer to official Microsoft documentation and support channels. References Version upgrade assessment in SSMS: https://learn.microsoft.com/ssms/migrate-sql-server-component General Availability of SQL Server Migration Component in SSMS: General Availability of SQL Server Migration Component in SSMS 21 | Microsoft Community Hub Azure SQL Assessment and Migration in ADS: https://learn.microsoft.com/azure-data-studio/extensions/azure-sql-migration-extension Continuous Migration Assessment in SQL Server enabled by Azure Arc: https://learn.microsoft.com/sql/sql-server/azure-arc/migration-assessment Automate assessment and migration using datamigration modules: https://learn.microsoft.com/en-us/azure/migrate/how-to-create-azure-sql-assessment Sample scripts for Azure SQL Assessment and Migration using Azure Powershell and CLI: https://github.com/Azure-Samples/data-migration-sql SQL Assessment in Azure Migrate: https://learn.microsoft.com/en-us/azure/migrate/how-to-create-azure-sql-assessment Migration to Azure SQL Database using Azure Database Migration Service Portal: https://learn.microsoft.com/data-migration/sql-server/database/database-migration-service?tabs=portal Azure SQL Migration Guide: https://learn.microsoft.com/en-us/data-migration/sql-server/overview
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.Migration from Cosmos DB to Cosmos DB
A few weeks ago, I was looking for how to migrate, data from a Cosmos DB NoSQL type account to a second Cosmos DB NoSQL account too. On paper it seems at first glance rather simple, but ultimately not so much. So some might ask why? In fact, for one of my critical projects, we initially decided to deploy a Cosmos DB account in Serverless mode, because we had to have users exclusively in Western Europe. But a few months later, the scope of the project radically changed. Now data must be accessible worldwide: Ok, no worries. 1. Potential solution: Geo-replication That's good, the https://learn.microsoft.com/en-us/azure/cosmos-db/introduction?WT.mc_id=AZ-MVP-5005062 service offers a geo-replication feature. The problem is that this feature is not available with Serverless mode, only with Provisioned Throughput mode, which ultimately seems consistent. So I cannot use that way. 2. Potential solution: Data restoration After a few minutes of thinking, I tell myself that it does not matter, just restore the data via the Point In Time Restore (PiTR) option. But I meet a new disappointment, because during the restore, the new Cosmos DB account created, is the same as the initial one, in my case a Serverless account. Ok, for now, I am not lucky. 3. Potential solution: Well I have to look, but why not a migration? So I start my research like https://en.wikipedia.org/wiki/Sherlock_Holmes with my pipe, my magnifying glass and my K-way (sorry I didn't have a raincoat handy). After a few minutes, I come across the https://learn.microsoft.com/en-us/azure/cosmos-db/migration-choices?WT.mc_id=AZ-MVP-5005062 page whose title is Options to migrate your on-premises or cloud data to Azure Cosmos DB Hum, given the title, I might be interested, so I'm starting to take off my K-way because it's really hot. The documentation is quite well done, as often to be honest with Microsoft, it offers different scenarios, and in addition, two types of migration are offered, namely "Online" and/or "Offline. 4. Potential solution: Migration proposed by Microsoft I find many migration use cases there, with as a source, different types of DB such as Azure Cosmos DB of course, but also json or csv files, not to mention Oracle and Apache Cassandra. After a few moments, I list what seems to work for my use case: Offline mode: Using Azure Data Factory Using Azure Cosmos DB Spark connector Using Azure Cosmos DB Spark connector + Change Feed sample Using Custom tool with Azure Cosmos DB bulk executor library Online way: Using a Spark Azure Cosmos DB Connector + Change Feed Using Azure Cosmos DB Functions + ChangeFeed API With my magnifying glass, I look at the various proposed solutions available to me... ... and the more I advance, the more I realize that they require a lot of efforts and for some of them, the deployment of new services is required. Hm, okay ! Before going any further, I go back to my Cosmos DB account to see what it contains. Then I count 1 DB with 3 containers, and in addition, it contains relatively little data. When I weigh the pros and cons of each solutions, I quickly see that it almost takes a gas plant for a relatively simple need. But on the other hand, I have no choice, this migration is mandatory, and as Terence Hill and Bud Spencer said in their movie: Go for It! But there is no urgency, so I'll see if I can find something simpler, and in the worst case, I'll always have a reversal solution with those seen previously. 5. Considered solution: Migration with Azure Cosmos DB data migration tool Continuing my research, I came across an announcement from Microsoft dating from April 2015, talking about the https://azure.microsoft.com/en-us/updates/documentdb-data-migration-tool/?WT.mc_id=AZ-MVP-5005062. Well I recognize that 2015 is far, but I'm going to dig a little so I exchange my pipe, against a small shovel. This open source tool allows to import data to Azure Cosmos DB, from different data sources like: JSON files CSV files SQLServer MongoDB Azure Table storage Amazon DynamoDB HBase Azure Cosmos containers You saw like me, Cosmos DB to Cosmos DB! The pupil of my eyes has started to dilate, my hair (well what I have left of it) has fallen out, and I find myself in my underwear saying: My precious! Once back to my normal appearance, well, my appearance at all, I start to browse the various links mentioned in the announcement and come across the https://github.com/azure/azure-documentdb-datamigrationtool/?WT.mc_id=AZ-MVP-5005062 of the tool. I have the impression that luck has finally changed side, but when I come across the 1st sentence and I read: The Azure Cosmos DB data migration tool is undergoing a full refactor to restructure the project... Ahhhhhhhhhhhhhhh this is driving me crazy, someone is playing with me, there's no other way! But as I'm tenacious, I still decide to visit the https://github.com/Azure/azure-documentdb-datamigrationtool/tree/archive/?WT.mc_id=AZ-MVP-5005062 of the project and end up downloading https://github.com/Azure/azure-documentdb-datamigrationtool/releases/download/1.8.3/azure-documentdb-datamigrationtool-1.8.3.zip which dates from August 2021, which isn't so bad when you think about it. 6. Azure Cosmos DB data migration tool testing I launch the tool via the executable dtui.exe (Yes, I work on Windows, and I'm proud of it ), I go through the doc and the operation seems very simple. There are some prerequisites: A source Azure Cosmos DB account A destination Azure Cosmos DB account Connection strings for each account The name of your databases (DB) The name of your containers As you can see from my example below, my source is aziedb1amo008 and my destination is aziedb1amo900: So I wish to migrate my DB StarWars as well as the various containers, which it has namely People, Planets and Species. What? I told you that it was a critical project Step 1: The first thing to do is therefore to define our source account by specifying the connection string, accompanied by the name of the database at the end of the fields, as well as the collection which is none other than our container. We click on Verify in order to validate that the connection to the account is established correctly. Bingo, we can go to the next step. Step 2: Next, we will define our destination account. As in the previous step, we define the connection string, the name of the DB which will be created automatically if it does not exist, the collection and the partition key. Step 3: If you want, you can define a log file in csv format. Step 4: And finally the last step allows you to have a small summary, and you just have to click on Import. Et voila! Well, not quite because I also wanted to migrate the Planets and Species containers, so I follow the same steps to achieve my goal. After a minute or two, you can therefore see that I find my DB, my containers, and even my data on the new Cosmos DB account, which is quite nice. And of course, it also works with data other than Star Wars, like Pikachu or Marvel! But you can also try with your own dataset
