Migrate databases with Azure SQL migration extension for Azure Data Studio

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.

The key benefits of using the Azure SQL migration extension for Azure Data Studio are:

  • A responsive user interface that provides you with an end-to-end migration experience that starts with a migration readiness assessment, SKU recommendation (based on performance data).
  • An enhanced assessment mechanism that can evaluate SQL Server instances, identifying databases ready for migration to the different Azure SQL targets.

    Note

    You can assess SQL Server databases running on Windows and Linux Operating systems using the Azure SQL migration extension.

  • An SKU recommendation engine (Preview) that collects performance data from the source SQL Server instance on-premises, generating right-sized SKU recommendations based on your Azure SQL target.
  • A reliable Azure service powered by Azure Database Migration Service that orchestrates data movement activities to deliver a seamless migration experience.
  • The ability to run online (for migrations requiring minimal downtime) or offline (for migrations where downtime persists through the migration) migration modes to suit your business requirements.
  • The flexibility to create and configure a self-hosted integration runtime to provide your own compute for accessing the source SQL Server and backups in your on-premises environment.

Check the following step-by-step tutorials for more information about each specific migration scenario by Azure SQL target:

Migration scenario Migration mode
SQL Server to Azure SQL Managed Instance Online / Offline
SQL Server to SQL Server on Azure Virtual Machine Online / Offline
SQL Server to Azure SQL Database (Preview) Offline

Important

If your target is Azure SQL Database (Preview), make sure to deploy the database schema before starting the migration. You can use tools as SQL Server dacpac extension or, SQL Database Projects extension for Azure Data Studio.

The following 16-minute video explains recent updates and features added to the Azure SQL migration extension in Azure Data Studio. Including the new workflow for SQL Server database assessments and SKU recommendations.

Architecture of Azure SQL migration extension for Azure Data Studio

Azure Database Migration Service (DMS) is one of the core components in the overall architecture. DMS provides a reliable migration orchestrator to enable database migrations to Azure SQL. Create or reuse an existing DMS using the Azure SQL migration extension in Azure Data Studio (ADS). DMS uses Azure Data Factory's self-hosted integration runtime to access and upload valid backup files from your on-premises network share or your Azure Storage account.

The workflow of the migration process is illustrated below. Architecture

  1. Source SQL Server: SQL Server instance on-premises, private cloud, or any public cloud virtual machine. All versions of SQL Server 2008 and above are supported.

  2. Target Azure SQL: Supported Azure SQL targets are Azure SQL Managed Instance, SQL Server on Azure Virtual Machines (registered with SQL IaaS extension - full management mode), or Azure SQL Database (Preview).

  3. Network File Share: Server Message Block (SMB) network file share where backup files are stored for the database(s) to be migrated. Azure Storage blob containers and Azure Storage file share are also supported.

  4. Azure Data Studio: Download and install the Azure SQL migration extension in Azure Data Studio.

  5. Azure DMS: Azure service that orchestrates migration pipelines to do data movement activities from on-premises to Azure. DMS is associated with Azure Data Factory's (ADF) self-hosted integration runtime (IR) and provides the capability to register and monitor the self-hosted IR.

  6. Self-hosted integration runtime (IR): Self-hosted IR should be installed on a machine that can connect to the source SQL Server and the location of the backup file. DMS provides the authentication keys and registers the self-hosted IR.

  7. Backup files upload to Azure Storage: DMS uses self-hosted IR to upload valid backup files from the on-premises backup location to your Azure Storage account. Data movement activities and pipelines are automatically created in the migration workflow to upload the backup files.

  8. Restore backups on target Azure SQL: DMS restores backup files from your Azure Storage account to the supported target Azure SQL.

    Note

    If your migration target is Azure SQL Database (Preview), you don't need backups to perform this migration. The migration to Azure SQL Database is considered a logical migration involving the database's pre-creation and data movement (performed by DMS).

    Important

    With online migration mode, DMS continuously uploads the backup source files to Azure Storage and restores them to the target until you complete the final step of cutting over to the target.

    In offline migration mode, DMS uploads the backup source files to Azure Storage and restores them to the target without requiring you to perform a cutover.

Prerequisites

The following sections walk through the prerequisites for each supported Azure SQL target.

  • 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 to one of the built-in roles listed below:

    • Contributor for the target Azure SQL Managed Instance (and Storage Account to upload your database backup files from SMB network share).
    • Reader role for the Azure Resource Groups containing the target Azure SQL Managed Instance or the Azure storage account.
    • Owner or Contributor role for the Azure subscription (required if creating a new DMS service).
    • As an alternative to using the above built-in roles, you can assign a custom role as defined in this article.

    Important

    Azure account is only required when configuring the migration steps and is not required for assessment or Azure recommendation steps in the migration wizard.

  • Create a target Azure SQL Managed Instance.

  • Ensure that the logins used to connect the source SQL Server are members of the sysadmin server role or have CONTROL SERVER permission.

  • Use one of the following storage options for the full database and transaction log backup files:

    • SMB network share
    • Azure storage account file share or blob container

    Important

    • If your database backup files are provided in an SMB network share, Create an Azure storage account that allows the DMS service to upload the database backup files. Make sure to create the Azure Storage Account in the same region as the Azure Database Migration Service instance is created.
    • Azure Database Migration Service does not initiate any backups, and instead uses existing backups, which you may already have as part of your disaster recovery plan, for the migration.
    • You need to take backups using the WITH CHECKSUM option.
    • Each backup can be written to either a separate backup file or multiple backup files. However, appending multiple backups (i.e. full and t-log) into a single backup media is not supported.
    • Use compressed backups to reduce the likelihood of experiencing potential issues associated with migrating large backups.
  • Ensure that the service account running the source SQL Server instance has read and write permissions on the SMB network share that contains database backup files.

  • The source SQL Server instance certificate from a database protected by Transparent Data Encryption (TDE) needs to be migrated to the target Azure SQL Managed Instance or SQL Server on Azure Virtual Machine before migrating data. To learn more, see Migrate a certificate of a TDE-protected database to Azure SQL Managed Instance and Move a TDE Protected Database to Another SQL Server.

    Tip

    If your database contains sensitive data that is protected by Always Encrypted, migration process using Azure Data Studio with DMS will automatically migrate your Always Encrypted keys to your target Azure SQL Managed Instance or SQL Server on Azure Virtual Machine.

  • If your database backups are in a network file share, provide a machine to install self-hosted integration runtime to access and migrate database backups. The migration wizard provides the download link and authentication keys to download and install your self-hosted integration runtime. In preparation for the migration, ensure that the machine where you plan to install the self-hosted integration runtime has the following outbound firewall rules and domain names enabled:

    Domain names Outbound ports Description
    Public Cloud: {datafactory}.{region}.datafactory.azure.net
    or *.frontend.clouddatahub.net
    Azure Government: {datafactory}.{region}.datafactory.azure.us
    China: {datafactory}.{region}.datafactory.azure.cn
    443 Required by the self-hosted integration runtime to connect to the Data Migration service.
    For new created Data Factory in public cloud, locate the FQDN from your Self-hosted Integration Runtime key, which is in format {datafactory}.{region}.datafactory.azure.net. For old Data factory, if you don't see the FQDN in your Self-hosted Integration key, use *.frontend.clouddatahub.net instead.
    download.microsoft.com 443 Required by the self-hosted integration runtime for downloading the updates. If you have disabled auto-update, you can skip configuring this domain.
    *.core.windows.net 443 Used by the self-hosted integration runtime that connects to the Azure storage account for uploading database backups from your network share

    Tip

    If your database backup files are already provided in an Azure storage account, self-hosted integration runtime is not required during the migration process.

  • When using self-hosted integration runtime, ensure that the machine where the runtime is installed can connect to the source SQL Server instance and the network file share where backup files are located. Outbound port 445 should be enabled to access the network file share. Also, see recommendations for using self-hosted integration runtime](migration-using-azure-data-studio.md#recommendations-for-using-self-hosted-integration-runtime-for-database-migrations)

  • If you're using the Azure Database Migration Service for the first time, ensure that Microsoft.DataMigration resource provider is registered in your subscription. You can follow the steps to register the resource provider

Recommendations for using self-hosted integration runtime for database migrations

  • Use a single self-hosted integration runtime for multiple source SQL Server databases.
  • Install only one instance of self-hosted integration runtime on any single machine.
  • Associate only one self-hosted integration runtime with one DMS.
  • The self-hosted integration runtime uses resources (memory / CPU) on the machine where it's installed. Install the self-hosted integration runtime on a machine different from your source SQL Server. However, having the self-hosted integration runtime close to the data source reduces the time for the self-hosted integration runtime to connect to the data source.
  • Use the self-hosted integration runtime only when you have your database backups in an on-premises SMB network share. Self-hosted integration runtime isn't required for database migrations if your source database backups are already in the Azure storage blob container.
  • We recommend up to 10 concurrent database migrations per self-hosted integration runtime on a single machine. To increase the number of concurrent database migrations, scale-out self-hosted runtime up to four nodes or create separate self-hosted integration runtime on different machines.
  • Configure self-hosted integration runtime to auto-update to automatically apply any new features, bug fixes, and enhancements that are released. To learn more, see Self-hosted Integration Runtime Auto-update.

Monitor database migration progress from the Azure portal

When you migrate the database(s) using the Azure SQL migration extension for Azure Data Studio, the migrations are orchestrated by the Azure Database Migration Service that was selected in the wizard. To monitor database migrations from the Azure portal,

  • Open the Azure portal
  • Search for your Azure Database Migration Service by the resource name Search Azure Database Migration Service resource in portal
  • Select the Monitor migrations tile on the Overview page to view the details of your database migrations. Monitor migrations in Azure portal

Known issues and limitations

  • Overwriting existing databases using DMS in your target Azure SQL Managed Instance or SQL Server on Azure Virtual Machine isn't supported.
  • Configuring high availability and disaster recovery on your target to match source topology isn't supported by DMS.
  • The following server objects aren't supported:
    • Logins
    • SQL Server Agent jobs
    • Credentials
    • SSIS packages
    • Server roles
    • Server audit
  • SQL Server 2008 and below as target versions aren't supported when migrating to SQL Server on Azure Virtual Machines.
  • If you're using SQL Server 2012 or SQL Server 2014, you need to store your source database backup files on an Azure Storage Blob Container instead of using the network share option. Store the backup files as page blobs since block blobs are only supported in SQL 2016 and after.
  • You can't use an existing self-hosted integration runtime created from Azure Data Factory for database migrations with DMS. Initially, the self-hosted integration runtime should be created using the Azure SQL migration extension in Azure Data Studio and can be reused for further database migrations.

Pricing

  • Azure Database Migration Service is free to use with the Azure SQL migration extension in Azure Data Studio. You can migrate multiple SQL Server databases using the Azure Database Migration Service at no charge using the service or the Azure SQL migration extension.
  • There's no data movement or data ingress cost for migrating your databases from on-premises to Azure. If the source database is moved from another region or an Azure VM, you may incur bandwidth charges based on your bandwidth provider and routing scenario.
  • Provide your machine or on-premises server to install Azure Data Studio.
  • A self-hosted integration runtime is needed to access database backups from your on-premises network share.

Regional Availability

For the list of Azure regions that support database migrations using the Azure SQL migration extension for Azure Data Studio (powered by Azure DMS), see Azure products available by region

Next steps