Tutorial: Migrate SQL Server to Azure SQL Managed Instance online in Azure Data Studio

Use the Azure SQL migration extension in Azure Data Studio to migrate database(s) from a SQL Server instance to an Azure SQL Managed Instance with minimal downtime. For methods that might require some manual effort, see the article SQL Server instance migration to Azure SQL Managed Instance.

In this tutorial, you migrate the AdventureWorks database from an on-premises instance of SQL Server to Azure SQL Managed Instance with minimal downtime by using Azure Data Studio with Azure Database Migration Service (DMS). This tutorial focuses on the online migration mode where application downtime is limited to a short cutover at the end of the migration.

In this tutorial, you learn how to:

This article describes an online database migration from SQL Server to Azure SQL Managed Instance. For an offline database migration, see Migrate SQL Server to a SQL Managed Instance offline using Azure Data Studio with DMS.

Prerequisites

To complete this tutorial, you need to:

• 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

  • The Azure SQL Migration extension for Azure Data Studio doesn't take database backups, or neither initiate any database backups on your behalf. Instead, the service uses existing database backup files for the migration.
  • 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.
  • Each backup can be written to either a separate backup file or multiple backup files. However, appending multiple backups (that is, full and t-log) into a single backup media isn't 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 you migrate data. For more information about migrating TDE-enabled databases, see Tutorial: Migrate TDE-enabled databases (preview) to Azure SQL in Azure Data Studio.

  Tip

  If your database contains sensitive data that is protected by Always Encrypted, the migration process that uses 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 a newly created data factory in the public cloud, locate the FQDN from your self-hosted integration runtime key, which is in format {datafactory}.{region}.datafactory.azure.net. For the 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 autoupdate, 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, a self-hosted integration runtime is not required during the migration process.

• When you're using a self-hosted integration runtime, make sure 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 allow access to the network file share. Also see recommendations for using a self-hosted integration runtime

• 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

Launch the Migrate to Azure SQL wizard in Azure Data Studio

1. Open Azure Data Studio and select the server icon to connect to your on-premises SQL Server (or SQL Server on Azure virtual machine).
2. On the server connection, right-click and select Manage.
3. On the server's home page, select Azure SQL Migration extension.
4. On the Azure SQL Migration dashboard, select Migrate to Azure SQL to launch the migration wizard.
5. The first page of the wizard allows you to start a new session or resume a previously saved one. Pick the first option to start a new session.

Run database assessment, collect performance data and get Azure recommendation

1. Select the database(s) to run assessment and select Next.
2. Select Azure SQL Managed Instance as the target.
3. Select on the View/Select button to view details of the assessment results for your database(s), select the database(s) to migrate, and select OK. If any issues are displayed in the assessment results, they need to be remediated before proceeding with the next steps.
4. Select the Get Azure recommendation button.
5. Pick the Collect performance data now option and enter a path for performance logs to be collected and select the Start button.
6. Azure Data Studio will now collect performance data until you either stop the collection, press the Next button in the wizard or close Azure Data Studio.
7. After 10 minutes you see a recommended configuration for your Azure SQL Managed Instance. You can also press the Refresh recommendation link after the initial 10 minutes to refresh the recommendation with the extra data collected.
8. In the above Azure SQL Managed Instance* box, select the View details button for more information about your recommendation.
9. Close the view details box and press the Next button.

Configure migration settings

1. Specify your Azure SQL Managed Instance by selecting your subscription, location, resource group from the corresponding drop-down lists and then select Next.
2. Select Online migration as the migration mode.

   Note

   In the online migration mode, the source SQL Server database can be used for read and write activity while database backups are continuously restored on target Azure SQL Managed Instance. Application downtime is limited to duration for the cutover at the end of migration.

3. Select the location of your database backups. Your database backups can either be located on an on-premises network share or in an Azure storage blob container.

   Note

   If your database backups are provided in an on-premises network share, DMS will require you to set up a self-hosted integration runtime in the next step of the wizard. If a self-hosted integration runtime is required to access your source database backups, check the validity of the backup set and upload them to your Azure storage account.
   If your database backups are already on an Azure storage blob container, you don't need to set up a self-hosted integration runtime.

• For backups located on a network share, provide the following details of your source SQL Server, source backup location, target database name, and Azure storage account for the backup files to be uploaded to:

  Field	Description
  Source Credentials - Username	The credential (Windows / SQL authentication) to connect to the source SQL Server instance and validate the backup files.
  Source Credentials - Password	The credential (Windows / SQL authentication) to connect to the source SQL Server instance and validate the backup files.
  Network share location that contains backups	The network share location that contains the full and transaction log backup files. Any invalid files or backups files in the network share that don't belong to the valid backup set will be automatically ignored during the migration process.
  Windows user account with read access to the network share location	The Windows credential (username) that has read access to the network share to retrieve the backup files.
  Password	The Windows credential (password) that has read access to the network share to retrieve the backup files.
  Target database name	The target database name can be modified if you wish to change the database name on the target during the migration process.
  Storage account details	The resource group and storage account where backup files are uploaded to. You don't need to create a container as DMS will automatically create a blob container in the specified storage account during the upload process.

• For backups stored in an Azure storage blob container, specify the below details of the Target database name, Resource group, Azure storage account, and Blob container from the corresponding drop-down lists.

  Field	Description
  Target database name	The target database name can be modified if you wish to change the database name on the target during the migration process.
  Storage account details	The resource group, storage account and container where backup files are located.

  Important

  If loopback check functionality is enabled and the source SQL Server and file share are on the same computer, then source won't be able to access the file share using FQDN. To fix this issue, disable loopback check functionality using the instructions here

• The Azure SQL migration extension for Azure Data Studio no longer requires specific configurations on your Azure Storage account network settings to migrate your SQL Server databases to Azure. However, depending on your database backup location and desired storage account network settings, there are a few steps needed to ensure your resources can access the Azure Storage account. See the following table for the various migration scenarios and network configurations:

  Scenario	SMB network share	Azure Storage account container
  Enabled from all networks	No extra steps	No extra steps
  Enabled from selected virtual networks and IP addresses	See 1a	See 2a
  Enabled from selected virtual networks and IP addresses + private endpoint	See 1b	See 2b

  1a - Azure Blob storage network configuration

  If you have your Self-Hosted Integration Runtime (SHIR) installed on an Azure VM, see section 1b - Azure Blob storage network configuration. If you have your Self-Hosted Integration Runtime (SHIR) installed on your on-premises network, you need to add your client IP address of the hosting machine in your Azure Storage account as so:

  

  To apply this specific configuration, connect to the Azure portal from the SHIR machine, open the Azure Storage account configuration, select Networking, and then mark the Add your client IP address checkbox. Select Save to make the change persistent. See section 2a - Azure Blob storage network configuration (Private endpoint) for the remaining steps.

  1b - Azure Blob storage network configuration

  If your SHIR is hosted on an Azure VM, you need to add the virtual network of the VM to the Azure Storage account since the Virtual Machine has a nonpublic IP address that can't be added to the IP address range section.

  

  To apply this specific configuration, locate your Azure Storage account, from the Data storage panel select Networking, then mark the Add existing virtual network checkbox. A new panel opens up, select the subscription, virtual network, and subnet of the Azure VM hosting the Integration Runtime. This information can be found on the Overview page of the Azure Virtual Machine. The subnet may say Service endpoint required if so, select Enable. Once everything is ready, save the updates. Refer to section 2a - Azure Blob storage network configuration (Private endpoint)a for the remaining required steps.

  2a - Azure Blob storage network configuration (Private endpoint)

  If your backups are placed directly into an Azure Storage Container, all the above steps are unnecessary since there's no Integration Runtime communicating with the Azure Storage account. However, we still need to ensure that the target SQL Server instance can communicate with the Azure Storage account to restore the backups from the container. To apply this specific configuration, follow the instructions in section 1b - Azure Blob storage network configuration, specifying the target SQL instance Virtual Network when filling out the "Add existing virtual network" popup.

  2b - Azure Blob storage network configuration (Private endpoint)

  If you have a private endpoint set up on your Azure Storage account, follow the steps outlined in section 2a - Azure Blob storage network configuration (Private endpoint). However, you need to select the subnet of the private endpoint, not just the target SQL Server subnet. Ensure the private endpoint is hosted in the same VNet as the target SQL Server instance. If it isn't, create another private endpoint using the process in the Azure Storage account configuration section.

Create Azure Database Migration Service

1. Create a new Azure Database Migration Service or reuse an existing Service that you previously created.

   Note

   If you had previously created DMS using the Azure Portal, you cannot reuse it in the migration wizard in Azure Data Studio. Only DMS created previously using Azure Data Studio can be reused.

2. Select the Resource group where you have an existing DMS or need to create a new one. The Azure Database Migration Service dropdown lists any existing DMS in the selected resource group.
3. To reuse an existing DMS, select it from the dropdown list and the status of the self-hosted integration runtime will be displayed at the bottom of the page.
4. To create a new DMS, select Create new. On the Create Azure Database Migration Service, screen provide the name for your DMS and select Create.
5. After successful creation of DMS, you'll be provided with details to set up integration runtime.
6. Select on Download and install integration runtime to open the download link in a web browser. Complete the download. Install the integration runtime on a machine that meets the prerequisites of connecting to source SQL Server and the location containing the source backup.
7. After the installation is complete, the Microsoft Integration Runtime Configuration Manager will automatically launch to begin the registration process.
8. Copy and paste one of the authentication keys provided in the wizard screen in Azure Data Studio. If the authentication key is valid, a green check icon is displayed in the Integration Runtime Configuration Manager indicating that you can continue to Register.
9. After successfully completing the registration of self-hosted integration runtime, close the Microsoft Integration Runtime Configuration Manager and switch back to the migration wizard in Azure Data Studio.
10. Select Test connection in the Create Azure Database Migration Service screen in Azure Data Studio to validate that the newly created DMS is connected to the newly registered self-hosted integration runtime.
11. Review the migration summary and select Done to start the database migration.

Monitor your migration

1. On the Database Migration Status, you can track the migrations in progress, migrations completed, and migrations failed (if any).

   

2. Select Database migrations in progress to view ongoing migrations and get further details by selecting the database name.

3. The migration details page displays the backup files and the corresponding status:

   Status	Description
   Arrived	Backup file arrived in the source backup location and validated
   Uploading	Integration runtime is currently uploading the backup file to Azure storage
   Uploaded	Backup file is uploaded to Azure storage
   Restoring	Azure Database Migration Service is currently restoring the backup file to Azure SQL Managed Instance
   Restored	Backup file is successfully restored on Azure SQL Managed Instance
   Canceled	Migration process was canceled
   Ignored	Backup file was ignored as it doesn't belong to a valid database backup chain

   

Complete migration cutover

The final step of the tutorial is to complete the migration cutover to ensure the migrated database in Azure SQL Managed Instance is ready for use. This process is the only part that requires downtime for applications that connect to the database and hence the timing of the cutover needs to be carefully planned with business or application stakeholders.

To complete the cutover:

1. Stop all incoming transactions to the source database.
2. Make application configuration changes to point to the target database in Azure SQL Managed Instance.
3. Take a final log backup of the source database in the backup location specified
4. Put the source database in read-only mode. Therefore, users can read data from the database but not modify it.
5. Ensure all database backups have the status Restored in the monitoring details page.
6. Select Complete cutover in the monitoring details page.

During the cutover process, the migration status changes from in progress to completing. When the cutover process is completed, the migration status changes to succeeded to indicate that the database migration is successful and that the migrated database is ready for use.

Limitations

Migrating to Azure SQL Managed Instance by using the Azure SQL extension for Azure Data Studio has the following limitations:

• If migrating a single database, the database backups must be placed in a flat-file structure inside a database folder (including the container root folder), and the folders can't be nested, as it's not supported.
• If migrating multiple databases using the same Azure Blob Storage container, you must place backup files for different databases in separate folders inside the container.
• Overwriting existing databases using DMS in your target Azure SQL Managed Instance isn't supported.
• DMS doesn't support configuring high availability and disaster recovery on your target to match the source topology.
• The following server objects aren't supported:
  • SQL Server Agent jobs
  • Credentials
  • SSIS packages
  • Server audit
• 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.
• A single LRS job (created by DMS) can run for a maximum of 30 days. When this period expires, the job is automatically canceled thus your target database gets automatically deleted.
• If you received the following error: Memory-optimized filegroup must be empty in order to be restored on General Purpose tier of SQL Database Managed Instance. This issue is by design, Hekaton (also known as SQL Server In-Memory OLTP) isn't supported on General Purpose tier of Azure SQL Managed Instance. To continue migration, one way is to upgrade to Business Critical tier, which supports Hekaton. Another way is to make sure the source database isn't using it while the Azure SQL Managed Instance is General Purpose.

Next steps

• For a tutorial showing you how to migrate a database to SQL Managed Instance using the T-SQL RESTORE command, see Restore a backup to SQL Managed Instance using the restore command.
• For information about SQL Managed Instance, see What is SQL Managed Instance.
• For information about connecting apps to SQL Managed Instance, see Connect applications.
• To troubleshoot, review Known issues.