Tutorial: Migrate SQL Server to an Azure SQL Database offline using Azure Data Studio with DMS (Preview)
Note
Azure SQL Database targets are only available using the Azure Data Studio Insiders version of the Azure SQL Migration extension.
You can use the Azure SQL migration extension in Azure Data Studio to migrate the database(s) from a SQL Server instance to Azure SQL Database (Preview).
In this tutorial, you'll learn how to migrate the AdventureWorks2019 database from an on-premises instance of SQL Server to Azure SQL Database (Preview) by using the Azure SQL Migration extension for Azure Data Studio. This tutorial focuses on the offline migration mode that considers an acceptable downtime during the migration process.
In this tutorial, you learn how to:
- Launch the Migrate to Azure SQL wizard in Azure Data Studio
- Run an assessment of your source SQL Server database(s)
- Collect performance data from your source SQL Server
- Get a recommendation of the Azure SQL Database (Preview) SKU best suited for your workload
- Deploy your on-premises database schema to Azure SQL Database
- Create a new Azure Database Migration Service
- Start and, monitor the progress for your migration through to completion
Tip
When you migrate databases to Azure by using Azure Database Migration Service, you can do an offline or an online migration. With an offline migration, application downtime starts when the migration starts. With an online migration, downtime is limited to the time to cut over at the end of migration. We suggest that you test an offline migration to determine whether the downtime is acceptable; if not, do an online migration.
Important
Online migrations for Azure SQL Database targets, are not currently available.
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 Database
- Reader role for the Azure Resource Groups containing the target Azure SQL Database.
- 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 Database.
- Ensure that the SQL Server login to connect the source SQL Server is a member of the
db_datareaderand the login for the target SQL server isdb_owner. - Migrate database schema from source to target using SQL Server dacpac extension or, SQL Database Projects extension for Azure Data Studio.
- 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
- Open Azure Data Studio, navigate to the connections section, then select and connect to your on-premises SQL Server (or SQL Server on Azure Virtual Machine).
- On the server connection, right-click and select Manage.
- On the server's home page, navigate to the General panel section, then select Azure SQL Migration extension.
- The Azure SQL Migration dashboard will open, then click on the Migrate to Azure SQL button to launch the migration wizard.
- The wizard's first page will allow you to start a new session or resume a previously saved one. If no previous session is saved, the Database assessment page will be displayed.
Run database assessment, collect performance data and get right-sized recommendations
- Select the database(s) to run the assessment, then click Next.
- Select Azure SQL Database (Preview) as the target.
- Click on the View/Select button at the bottom of this page to view details of the assessment results.
- Select the database(s), and review the assessment report making sure no issues are found.
- Click the Get Azure recommendation button to open the recommendations panel.
- Select the Collect performance data now option, then choose a folder from your local drive to store the performance logs. When you're ready, then click the Start button.
- Azure Data Studio will now collect performance data until you either stop the collection or close Azure Data Studio.
- After 10 minutes, you'll see a recommendation available for your Azure SQL Database (Preview). After the first recommendation is generated, you can use the Restart data collection option to continue the data collection process to refine the SKU recommendation, especially if your usage patterns vary for an extended time.
- Navigate to your Azure SQL target section, with Azure SQL Database (Preview) selected click the View details button to open the detailed SKU recommendation report. You can also click on the save recommendation report button at the bottom of this page for later analysis.
Close the recommendations page, and press the Next** button to continue with your migration.
Configure migration settings
- Start with the upper section, specifying Azure account details. Select your subscription, location, and resource group from the corresponding drop-down lists.
- Move to the next section, selecting the target Azure SQL Database server (logical server) from the drop-down list. Specify the target username and password, then click the Connect button to verify the connectivity with the specified credentials.
- Map the source and target databases for the migration, using the drop-down list for the Azure SQL Database target. Then click Next to move to the next step in the migration wizard.
- Select Offline migration as the migration mode and click Next.
- Enter source SQL Server credentials, then click Edit to select the list of tables to migrate between source and target.
- Select the tables that you want to migrate to target. Notice the Has rows column indicates whether the target table has rows in the target database. You can select one or more tables. In the example below, a text filter was applied to select just the tables that contain the word Employee. However, you can select the list of tables based on your migration needs. When you're ready, click Update to proceed with the next step.
- The list of selected tables can be updated anytime before starting the migration. Click Next when you're ready to move to the next step in the migration wizard.
Note
If no tables are selected or credentials fields are empty, the Next button will be disabled.
Create Azure Database Migration Service
- 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.
- Select the Resource group where you have an existing DMS or need to create a new one. The Azure Database Migration Service dropdown will list any existing DMS in the selected resource group.
- To reuse an existing DMS, select it from the dropdown list and press Next to view the summary screen. When ready to begin the migration, press the Start migration button.
- To create a new DMS, select Create new. On the Create Azure Database Migration Service, the screen provides the name for your DMS, and select Create.
- After successfully creating DMS, you'll be provided details to set up integration runtime.
- Select 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 the source SQL Server.
- After the installation is complete, the Microsoft Integration Runtime Configuration Manager will automatically launch to begin the registration process.
- 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.
- After completing the registration of the self-hosted integration runtime, close the Microsoft Integration Runtime Configuration Manager and switch back to the migration wizard in Azure Data Studio.
Note
Refer Create and configure a self-hosted integration runtime for additional information regarding self-hosted integration runtime.
- 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.
- Review the summary and select Start migration to start the database migration.
Monitor your migration
On The Azure SQL Migration dashboard, navigate to the Database Migration Status section.
Using the different options in the panel below, you can track migrations in progress, completed, and failed migrations (if any) or list all database migrations together.
Click on Database migrations in progress to view ongoing migrations and get further details.
DMS will return the latest known migration status every time the migration status section gets refreshed. Use the following table to learn more about the possible statuses:
Status Description Preparing for copy Disabling autostats, triggers, and indexes for 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 final steps to return tables to original schema Rebuilding indexes Rebuilding indexes on target tables Succeeded This table has all data copied to it and, indexes rebuilt The migration details page displays the current status per database. As you can see from the screenshot below, the AdventureWorks2019 database migration is in Creating status.
Click on the Refresh link to update the status. As you can see from the screenshot below, DMS has updated the migration status to In progress.
Click on the database name link to open the table-level view. The upper section of this dashboard displays the current status of the migration, while the lower section provides a detailed status of each table.
After all table data is migrated to the Azure SQL Database (Preview) target, DMS will update the migration status from In progress to Succeeded.
Once again, you can click on the database name link to open the table-level view. The upper section of this dashboard displays the current status of the migration; the lower section provides information to verify all data is the same on both the source and the target (rows read vs. rows copied).
Note
DMS optimizes migration by skipping tables with no data (0 rows). Such tables will not show up in the list even if selected when creating the migration.
At this point, you've completed the migration to Azure SQL Database. We encourage you to go through a series of post-migration tasks to ensure everything functions smoothly and efficiently.
Important
Be sure to take advantage of the advanced cloud-based features offered by Azure SQL Database, such as built-in high availability, threat detection, and monitoring and tuning your workload.
Next steps
- For a tutorial showing you how to create an Azure SQL Database using the Azure portal, PowerShell, or AZ CLI commands, see Create a single database - Azure SQL Database.
- For information about Azure SQL Database, see What is Azure SQL Database.
- For information about connecting to Azure SQL Database, see Connect applications.
Feedback
Submit and view feedback for