Tutorial: Migrate SQL Server to SQL Server on an Azure Virtual Machine with Azure DMS (online)

Before you begin the tutorial:

• Ensure that you can access the Azure portal.

• Make sure that the Microsoft.DataMigration resource provider is registered in your subscription.

• Have an Azure account that's assigned to one of the following built-in roles:

  • Contributor for the target instance of SQL Server on an Azure VM, and for the storage account where you upload your database backup files from a Server Message Block (SMB) network share.

  • Reader role for the Azure resource group that contains the target instance of SQL Server on an Azure VM or for your Azure Storage account.

  • Owner or Contributor role for the Azure subscription.

  • As an alternative to using one of these built-in roles, you can assign custom roles.

  When you use the Azure portal to migrate, the signed-in user must have Storage Blob Data Reader access on the blob container that holds the backup files, to be able to list files and folders during migration setup.

Before you begin the tutorial:

• Download and install Azure Data Studio and the Azure SQL migration extension.

• Have an Azure account that's assigned to one of the following built-in roles:

  • Contributor for the target instance of SQL Server on an Azure VM, and for the storage account where you upload your database backup files from a Server Message Block (SMB) network share.

  • Reader role for the Azure resource group that contains the target instance of SQL Server on an Azure VM or for your Azure Storage account.

  • Owner or Contributor role for the Azure subscription.

  • As an alternative to using one of these built-in roles, you can assign custom roles.

  You need an Azure account only when you configure the migration steps. You don't need an Azure account for the assessment or to view Azure recommendations in the migration wizard in Azure Data Studio.

  When you use the Azure portal to migrate, the signed-in user must have Storage Blob Data Reader access on the blob container that holds the backup files, to be able to list files and folders during migration setup.

To start a new migration:

1. Go to Azure Database Migration Service in the Azure portal. Use +Create to create a new instance of Database Migration Service, or select an existing instance. Then, go to your Database Migration Service instance.

2. On the Overview pane of your Azure DMS instance, select New migration.

   

3. Under Select new migration scenario, choose your source, target server type, backup file storage location, migration mode as Online migration, and choose Select.

   Your database backups can be located either on an on-premises network share or in an Azure Storage blob container.

   

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

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

4. On the Azure SQL Virtual Machine Online Blob Migration Wizard, follow these steps:

   1. On the Source details tab, enter details for the source SQL Server instance, then select Next: Connect to source SQL Server.

      

   2. On the Select migration target tab, enter details for the subscription, resource group, and target SQL Server VM. Then select Next: Data source configuration.

   3. In the Data source configuration step, select the location of your database backups. Your database backups can be located either on an on-premises network share or in an Azure Storage blob container.

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

      • For backups that are stored in an Azure Storage blob container, enter or select the following information:

        Name	Description
        Resource group	The resource group where backup files are located.
        Storage account details	The storage account where backup files are located.
        Blob container	The blob container where backup files are located.
        Folder	The folder where backup files are located.
        Target database name	You can change the target database name during the migration process.

        If loopback check functionality is enabled and the source SQL Server and file share are on the same computer, the source can't access the file share with the FQDN. To fix this issue, disable loopback check functionality.

        

      • For backups that are located on a network share, enter the following additional information on the respective pages.

        Name	Description
        Source server name	The FQDN or IP of the source server. Ensure that the service account running the source SQL Server instance has read privileges on the network share.
        Authentication Type	Select the authentication type: SQL or Windows
        Source Credentials - Username	The credential (Windows and SQL authentication) to connect to the source SQL Server instance and validate the backup files.
        Source Credentials - Password	The credential (Windows and 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. The migration process automatically ignores any invalid files or backup files in the network share that don't belong to the valid backup set.
        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	You can change the target database name during the migration process.

To start a new migration:

1. In Azure Data Studio, go to Connections. Select and connect to your on-premises instance of SQL Server. You can also connect to SQL Server on an Azure virtual machine.

2. Right-click the server connection and select Manage.

3. In the server menu under General, select Azure SQL Migration.

4. In the Azure SQL Migration dashboard, select Migrate to Azure SQL to open the migration wizard.

   

5. On the first page of the wizard, start a new session or resume a previously saved session.

Run a database assessment, collect performance data, and get Azure recommendations

1. In Step 1: Databases for assessment in the Migrate to Azure SQL wizard, select the databases you want to assess. Then, select Next.

2. In Step 2: Assessment results and recommendations, complete the following steps:

   1. In Choose your Azure SQL target, select SQL Server on Azure Virtual Machine.

      

   2. Select View/Select to view the assessment results.

   3. In the assessment results, select the database, then review the assessment report to make sure no issues were found.

   4. Select Get Azure recommendation to open the recommendations pane.

   5. Select Collect performance data now. Select a folder on your local computer to store the performance logs, then select Start.

      Azure Data Studio collects performance data until you either stop data collection or you close Azure Data Studio.

      After 10 minutes, Azure Data Studio indicates that a recommendation is available for SQL Server on Azure Virtual Machines. After the first recommendation is generated, you can select Restart data collection to continue the data collection process and refine the SKU recommendation. An extended assessment is especially helpful if your usage patterns vary over time.

   6. In the selected SQL Server on Azure Virtual Machines target, select View details to open the detailed SKU recommendation report:

   7. In Review SQL Server on Azure Virtual Machines Recommendations, review the recommendation. To save a copy of the recommendation, select the Save recommendation report checkbox.

3. Select Close to close the recommendations pane.

4. Select Next to continue your database migration in the wizard.

Configure migration settings

1. In Step 3: Azure SQL target in the Migrate to Azure SQL wizard, select your Azure account, Azure subscription, the Azure region or location, and the resource group that contains the target SQL Server to Azure Virtual Machines instance. Then, select Next.

2. In Step 4: Migration mode, select Offline migration, then select Next.

   In offline migration mode, the source SQL Server database shouldn't be used for write activity while database backup files are restored on the target instance of SQL Server to Azure Virtual Machines. Application downtime persists from the start of the migration process until it's finished.

3. In Step 5: Data source configuration, select the location of your database backups. Your database backups can be located either on an on-premises network share or in an Azure storage blob container.

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

   • For backups that are located on a network share, enter or select the following information:

     Name	Description
     Source Credentials - Username	The credential (Windows and SQL authentication) to connect to the source SQL Server instance and validate the backup files.
     Source Credentials - Password	The credential (Windows and 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. The migration process automatically ignores any invalid files or backup files in the network share that don't belong to the valid backup set.
     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	You can change the target database name during the migration process.

   • For backups that are stored in an Azure storage blob container, enter or select the following information:

     Name	Description
     Target database name	You can change the target database name during the migration process.
     Storage account details	The resource group, storage account, and container where backup files are located.
     Last Backup File	The file name of the last backup of the database you're migrating.

     If loopback check functionality is enabled and the source SQL Server and file share are on the same computer, the source can't access the file share by using the FQDN. To fix this issue, disable loopback check functionality.

On-premises self-hosted integration runtime (SHIR)

If you install your SHIR on your on-premises network, follow these steps:

1. Connect to the Azure portal from the SHIR machine.

2. Open your Azure Storage account, and go to the Networking pane.

3. Make sure Public network access is set to Enabled from selected virtual networks and IP addresses.

4. In the Firewall section, select the Add your client IP address checkbox.

5. Enter the client IP address of the host machine, and select Save.

Azure VM self-hosted integration runtime (SHIR)

If you host your SHIR on an Azure VM, add the virtual network of the VM to the Azure Storage account, because the VM has a non-public IP address that you can't add to the IP address range section.

1. Connect to the Azure portal, and open your Azure Storage account.

2. Open your Azure Storage account, and go to the Networking pane.

3. Select the Add existing virtual network checkbox.

4. Select the subscription, virtual network, and subnet of the Azure VM hosting the SHIR. You can find this information on the Overview page of the Azure Virtual Machine. The subnet might say Service endpoint required. If so, select Enable.

5. Select Save.

Backups stored in Azure Storage container

If you place your backups directly into an Azure Storage container, you don't need to perform the preceding steps because there's no Integration Runtime communicating with the Azure Storage account.

However, you still need to ensure that the target SQL Server instance can communicate with the Azure Storage account to restore the backups from the container.

1. Connect to the Azure portal, and open your Azure Storage account.

2. Open your Azure Storage account, and go to the Networking pane.

3. Select the Add existing virtual network checkbox.

4. Specify the target SQL Server instance virtual network, and select Save.

Backups stored in Azure Storage container (Private endpoint)

If you set up a private endpoint on your Azure Storage account, follow these steps:

1. Connect to the Azure portal, and open your Azure Storage account.

2. Open your Azure Storage account, and go to the Networking pane.

3. Select the Add existing virtual network checkbox.

4. Specify the subnet of the private endpoint, and select Save.

The private endpoint must be hosted in the same virtual network as the target SQL Server instance. If it isn't, create another private endpoint using the process in the Azure Storage account configuration section.

Step 1: In the Azure portal, navigate to the Azure Database Migration Service page. Create a new instance of Azure Database Migration Service, or reuse an existing instance that you created earlier.

Use an existing instance of Database Migration Service

To use an existing instance of Database Migration Service:

• On Azure portal, under Azure Database Migration Services, select an existing instance of Database Migration Service that you want to use, ensuring that it's present in right Resource Group and region.

  

Create a new instance of Database Migration Service

To create a new instance of Database Migration Service:

1. On Azure portal, under Azure Database Migration Service, select Create.

   

2. In Select migration scenario and Database Migration Service, select the desired input like Source and Target server type, choose Database Migration Service and choose Select.

   

3. On the next screen Create Data Migration Service, select your subscription and resource group, then select Location, and enter the Database Migration Service name. Select Review + Create. This creates the Azure Database Migration Service.

   

4. If the self-hosted integration runtime (SHIR) is required, on the overview page of your Database Migration Service and under Settings, select Integration runtime, and complete the following steps:

   1. Select Configure integration runtime and choose the Download and install integration runtime link to open the download link in a web browser. Download the integration runtime, and then install it on a computer that meets the prerequisites for connecting to the source SQL Server instance. For more information, see SHIR recommendations.

      

      When installation is finished, Microsoft Integration Runtime Configuration Manager automatically opens to begin the registration process.

   2. In the Authentication key table, copy one of the authentication keys that are provided in the wizard and paste it in Microsoft Integration Runtime Configuration Manager.

      

      If the authentication key is valid, a green check icon appears in Integration Runtime Configuration Manager. A green check indicates that you can continue to Register.

      After you register the self-hosted integration runtime, close Microsoft Integration Runtime Configuration Manager. It might take several minutes to reflect the Node details on Azure portal for Database Migration Service, under Settings > Integration runtime.

      

      Note

      For more information about the self-hosted integration runtime, see Create and configure a self-hosted integration runtime.

In Step 6: Azure Database Migration Service in the Migrate to Azure SQL wizard, create a new instance of Azure Database Migration Service or reuse an existing instance that you created earlier.

If you previously created a Database Migration Service instance by using the Azure portal, you can't reuse the instance in the migration wizard in Azure Data Studio. You can reuse an instance only if you created the instance by using Azure Data Studio.

Use an existing instance of Database Migration Service

To use an existing instance of Database Migration Service:

1. In Resource group, select the resource group that contains an existing instance of Database Migration Service.

2. In Azure Database Migration Service, select an existing instance of Database Migration Service that's in the selected resource group.

3. Select Next.

Create a new instance of Database Migration Service

To create a new instance of Database Migration Service, follow these steps:

1. In Resource group, create a new resource group to contain a new instance of Database Migration Service.

2. Under Azure Database Migration Service, select Create new.

3. In Create Azure Database Migration Service, enter a name for your Database Migration Service instance, then select Create.

4. Under Set up integration runtime, complete the following steps:

   1. Select the Download and install integration runtime link to open the download link in a web browser. Download the integration runtime, then install it on a computer that meets the prerequisites to connect to the source SQL Server instance.

      When installation finishes, Microsoft Integration Runtime Configuration Manager automatically opens to begin the registration process.

   2. In the Authentication key table, copy one of the authentication keys that the wizard provides and paste it in Azure Data Studio. If the authentication key is valid, a green check icon appears in Integration Runtime Configuration Manager. A green check indicates that you can continue to Register.

      After you register the self-hosted integration runtime, close Microsoft Integration Runtime Configuration Manager.

      For more information about how to use the self-hosted integration runtime, see Create and configure a self-hosted integration runtime.

5. In Create Azure Database Migration Service in Azure Data Studio, select Test connection to validate that the newly created Database Migration Service instance is connected to the newly registered self-hosted integration runtime.

6. Return to the migration wizard in Azure Data Studio.

On the Database migration summary tab, review the details, then select Start migration. The service starts the database migration and automatically takes you back to the Azure DMS dashboard.

In Step 7: Summary in the Migrate to Azure SQL wizard, review the configuration you created, then select Start migration to start the database migration.

1. To monitor your database migration, on the Overview pane of your Database Migration Service instance, select Monitor migrations.

   

2. To monitor your database migration, on the Overview pane of your DMS instance, select Monitor migrations.

3. Under the Migrations tab, you can track migrations that are in progress, completed, and failed (if any), or you can view all database migrations. In the menu bar, select Refresh to update the migration status.

   

1. In Azure Data Studio, in the server menu under General, select Azure SQL Migration to go to the dashboard for your Azure SQL migrations.

   Under Database migration status, you can track migrations that are in progress, completed, and failed (if any), or you can view all database migrations.

   

2. Select Database migrations in progress to view active migrations.

   To get more information about a specific migration, select the database name.