Introducing Azure SQL Database offline migrations for the Azure SQL migration extension

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Today, we're announcing the public preview of Azure SQL Database offline migrations in the Azure SQL Migration extension for Azure Data Studio.  

 

With this new feature, you can assess, get right-sized Azure recommendations for Azure migration targets, and migrate databases offline from on-premises SQL Server, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database.

 

Azure SQL Migrations extension - Architecture overviewAzure SQL Migrations extension - Architecture overview

Prerequisites 

The following list has all prerequisites you need to complete before migrating to Azure SQL Database using the Azure Migration extension for Azure Data Studio: 

 

  • Download and install Azure Data Studio 
  • Install the Azure SQL migration extension from the Azure Data Studio marketplace. 
  • Have an Azure account that is assigned one of the following built-in roles:  
    • Contributor for the target Azure SQL Database 
    • Reader role for the Azure Resource Groups having the target Azure SQL Database. 
    • Owner or Contributor role for the Azure subscription (needed if creating a new DMS (Azure Database Migration Service). 

As an alternative to using the above built-in roles, you can assign a custom role as defined in this article. 

It's essential to note that an Azure account is not required to complete the database assessment or Azure recommendation steps in the migration wizard. An Azure account is only required to configure the migration steps.

 

Migration assessment and right-sized recommendations 

The new migration capability in the Azure SQL Migration extension provides an end-to-end experience to modernize SQL Server to Azure SQL Database. This allows you to perform a migration readiness check with actions to remediate migration blockers, export the assessment results, and get right-sized Azure recommendations. 

 

Assessment - Azure SQL Database targetAssessment - Azure SQL Database target

 

We're also introducing an all-new elastic recommendation model for generating right-sized Azure recommendations. The model uses a novel price-performance methodology developed in collaboration with Microsoft Research. By analyzing existing, already-migrated Azure workloads, this new recommendation model has been shown to correctly identify cost-saving opportunities compared to alternate recommendation approaches.

 

Azure SQL Database offline migrations 

Perform offline migrations of your SQL Server databases running on-premises, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database using the Azure SQL Migration extension. 

 

With an offline migration, application downtime starts when the migration starts. This new migration experience in the Azure SQL Migration extension considers an acceptable downtime during the migration process. 

 

As mentioned in the prerequisites section, deploy the database schema from source to target using the SQL Server dacpac extension or SQL Database Projects extension for Azure Data Studio before starting your migration.  

 

The migration to Azure SQL Database is considered a logical migration. Thus, backups are not required for this type of migration. The migration process performed by the Azure SQL extension understands just the data movement (performed by DMS) from source to target. 

 

You can check the latest known migration status returned by DMS using the SQL Migration dashboard (within the Azure SQL Migration extension) or the Azure Portal.

SQL Migration dashboardSQL Migration dashboard

 

The following table has more details about the possible statuses: 

 

Status 

Description 

Preparing for copy 

Disabling autostats, triggers, and indexes for the target table 

Copying 

Data is being copied from source to target 

Copy finished 

Data copy has finished, and, waiting on other tables to finish copying to begin the final steps to return tables to the original schema 

Rebuilding indexes 

Rebuilding indexes on target tables 

Succeeded 

This table has all data copied to it and indexes rebuilt

 

Perform migrations at scale using PowerShell and Azure CLI 

Using Azure PowerShell Az.DataMigration or Azure CLI az datamigration, you can migrate databases by automating the creation of the Azure Database Migration Service and configuring offline database migrations for Azure SQL Database targets.  

 

Check out our Azure Samples GitHub repository to learn more about scaling your migrations through PowerShell and Azure CLI. You can use these examples to automate your migration process, whether an online or offline migration to Azure SQL Managed instance or SQL Server on Virtual Machines using file share or Azure storage. 

 

Migration scenario 

Scripting language 

SQL Server assessment 

PowerShell / Azure CLI 

SQL Server to Azure SQL Managed Instance (using file share) 

PowerShell / Azure CLI 

SQL Server to Azure SQL Managed Instance (using Azure storage) 

PowerShell / Azure CLI 

SQL Server to SQL Server on Azure Virtual Machines (using file share) 

PowerShell / Azure CLI 

SQL Server to SQL Server on Azure Virtual Machines (using Azure Storage) 

PowerShell / Azure CLI 

SQL Server to Azure SQL Database (Preview) 

PowerShell / Azure CLI 

SKU recommendations (Preview) 

PowerShell / Azure CLI 

End-to-End migration automation 

PowerShell / Azure CLI 

End-to-End migration automation for multiple databases 

PowerShell / Azure CLI 

 

Conclusion 

The Azure SQL migration extension for Azure Data Studio provides you with the ability to assess, get right-sized Azure recommendations for Azure migration targets, and migrate databases offline from on-premises SQL Server, SQL Server on Azure Virtual Machines, or any virtual machine running in the cloud (private, public) to Azure SQL Database. 

 

To learn more about Azure SQL Database Offline migrations, see the tutorial Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio with DMS. Take advantage of this One-click SQL Migration PoC GitHub repository to accelerate your migration to Azure SQL.

 

We look forward to hearing about your experience using the Azure Database Migration extension for Azure Data Studio. Please don't hesitate to contact us with questions or suggestions at the Azure Community forum — Azure Database Migration Service forum.

Leave a Reply

Your email address will not be published. Required fields are marked *

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