Migrate from SQL Server: Pre-migration

Applies to: Azure SQL Managed Instance SQL Server on Azure Virtual Machines Azure SQL Database

This article provides steps to prepare your environment to migrate from SQL Server to Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.

Supported sources and targets for migration

You can migrate SQL Server running on-premises or on:

• SQL Server on virtual machines (VMs).
• Amazon Web Services (AWS) EC2.
• Amazon Relational Database Service (AWS RDS).
• Compute Engine - Google Cloud Platform (GCP).

In this article, you learn how to discover and assess your user databases before migrating them from SQL Server to Azure SQL.

• Migration overview: SQL Server to Azure SQL Managed Instance
• Migration overview: SQL Server to SQL Server on Azure VMs
• Migration overview: SQL Server to Azure SQL Database

For other migration guides, see Azure Database Migration Guides.

After you verify that your source environment is supported, start with the pre-migration stage. Discover all of the existing data sources, assess migration feasibility, and identify any blocking issues that might prevent your Azure cloud migration.

Prerequisites

Azure SQL Managed Instance

To migrate your SQL Server to Azure SQL Managed Instance, make sure you have:

• Chosen a migration method and the corresponding tools for your method.
• Install the Azure SQL migration extension for Azure Data Studio.
• Created a target Quickstart: Create Azure SQL Managed Instance.
• Configured connectivity and proper permissions to access both source and target.
• Reviewed the SQL Server database engine features available in Azure SQL Managed Instance.

SQL Server on Azure VM

Migrating to SQL Server on Azure Virtual Machines requires the following resources:

• Azure SQL migration extension for Azure Data Studio.
• An Create and manage projects (only required for SQL Server discovery in your data estate).
• A prepared target Provision SQL Server on Azure VM (Azure portal) instance that's the same or greater version than the SQL Server source.
• Connectivity between Azure and on-premises.
• Choosing an appropriate migration strategy.

Azure SQL Database

For your SQL Server migration to Azure SQL Database, make sure you have:

• Chosen your migration method and corresponding tools.
• Install the Azure SQL migration extension for Azure Data Studio.
• Created a target Quickstart: Create a single database - Azure SQL Database.
• Configured connectivity and proper permissions to access both source and target.
• Reviewed the database engine features available in Azure SQL Database.

Discover

In the discover phase, scan the network to identify all SQL Server instances and features used by your organization.

Azure SQL Managed Instance

Use About Azure Migrate to assess migration suitability of on-premises servers, perform performance-based sizing, and provide cost estimations for running them in Azure.

Alternatively, use the Microsoft Assessment and Planning Toolkit (MAP Toolkit) to assess your current IT infrastructure. The toolkit provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.

For more information about tools available to use for the discover phase, see Services and tools available for data migration scenarios.

After data sources are discovered, assess any on-premises SQL Server instances that can be migrated to Azure SQL Managed Instance to identify migration blockers or compatibility issues. Proceed to the following steps to assess and migrate databases to Azure SQL Managed Instance:

• Assess SQL Managed Instance compatibility where you should ensure that there are no blocking issues that can prevent your migrations. This step also includes creating a performance baseline to determine resource usage on your source SQL Server instance. This step is needed if you want to deploy a properly sized managed instance and verify that performance after migration isn't affected.
• Connect your application to Azure SQL Managed Instance.
• Deploy to an optimally sized managed instance where you choose technical characteristics (number of vCores, amount of memory) and performance tier (Business Critical, General Purpose) of your managed instance.
• Select migration method and migrate where you migrate your databases using offline migration or online migration options.
• Monitor and remediate applications to ensure that you see the expected performance.

SQL Server on Azure VM

Azure Migrate assesses migration suitability of on-premises computers, performs performance-based sizing, and provides cost estimations for running on-premises. To plan for the migration, use Azure Migrate to identify existing data sources and details about the features your SQL Server instances use. This process involves scanning the network to identify all of your SQL Server instances in your organization with the version and features in use.

Important

When you choose a target Azure virtual machine for your SQL Server instance, be sure to review Checklist: Best practices for SQL Server on Azure VMs.

For more discovery tools, see the services and tools available for data migration scenarios.

Azure SQL Database

Use About Azure Migrate to assess migration suitability of on-premises servers, perform performance-based sizing, and provide cost estimations for running them in Azure.

Alternatively, use the Microsoft Assessment and Planning Toolkit (the "MAP Toolkit") to assess your current IT infrastructure. The toolkit provides a powerful inventory, assessment, and reporting tool to simplify the migration planning process.

For more information about tools available to use for the discover phase, see Services and tools available for data migration scenarios.

Assess

Note

If you're assessing the entire SQL Server data estate at scale on VMware, see Create an Azure SQL assessment to get Azure SQL deployment recommendations, target sizing, and monthly estimates.

Azure SQL Managed Instance

Determine whether SQL Managed Instance is compatible with the database requirements of your application. SQL Managed Instance is designed to provide easy lift and shift migration for most existing applications that use SQL Server. However, you might sometimes require features or capabilities that aren't yet supported and the cost of implementing a workaround is too high.

The Migrate databases by using the Azure SQL Migration extension for Azure Data Studio provides a seamless wizard based experience to assess, get Azure recommendations and migrate your SQL Server databases on-premises to SQL Server on Azure Virtual Machines. Besides highlighting any migration blockers or warnings, the extension also includes an option for Azure recommendations to collect your databases' performance data to recommend a right-sized Azure SQL Managed Instance to meet the performance needs of your workload (with the least price).

You can use the Azure SQL Migration extension for Azure Data Studio to assess databases to get:

• Assessment rules for SQL Server to Azure SQL Managed Instance migration
• Get Azure recommendations to migrate your SQL Server database

To assess your environment using the Azure SQL Migration extension, follow these steps:

1. Open the Azure SQL migration extension for Azure Data Studio.
2. Connect to your source SQL Server instance.
3. Select Migrate to Azure SQL, in the Azure SQL Migration wizard in Azure Data Studio.
4. Select databases for assessment, then select Next.
5. Select your Azure SQL target, in this case, Azure SQL Managed Instance.
6. Select View/Select to review the assessment report.
7. Look for migration blocking and feature parity issues. The assessment report can also be exported to a file that can be shared with other teams or personnel in your organization.
8. Determine the database compatibility level that minimizes post-migration efforts.

To get an Azure recommendation using the Azure SQL Migration extension, follow these steps:

1. Open the Azure SQL migration extension for Azure Data Studio.
2. Connect to your source SQL Server instance.
3. Select Migrate to Azure SQL, in the Azure SQL Migration wizard in Azure Data Studio.
4. Select databases for assessment, then select Next.
5. Select your Azure SQL target, in this case, Azure SQL Managed Instance.
6. Navigate to the Azure recommendations sections, and select Get Azure recommendation.
7. Select Collect performance data now. Choose a folder on your local computer to store the performance logs, and then select Start.
8. After 10 minutes, Azure Data Studio indicates that a recommendation is available for Azure SQL Managed Instance.
9. Check the Azure SQL Managed Instance card, in the Azure SQL target panel to review your Azure SQL Managed Instance SKU recommendation.

To learn more, see Tutorial: Migrate SQL Server to Azure SQL Managed Instance with DMS.

If the assessment encounters multiple blockers to confirm that your database isn't ready for an Azure SQL Managed Instance, then alternatively consider:

• SQL Server on Azure Virtual Machines if both SQL Database and SQL Managed Instance fail to be suitable targets.

Scaled assessments and analysis

The Azure SQL migration extension for Azure Data Studio and Azure Migrate supports performing scaled assessments and consolidation of the assessment reports for analysis.

If you have multiple servers and databases that need to be assessed and analyzed at scale to provide a wider view of the data estate, see the following links to learn more:

• Migrate databases at scale using automation (Preview)
• Performing scaled assessments using PowerShell - Azure Migrate
• Analyzing assessment reports using Power BI - Azure Migrate

Important

Running assessments at scale for multiple databases can also be automated using Run Data Migration Assistant from the command line which also allows the results to be uploaded to Azure Migrate for further analysis and target readiness.

SQL Server on Azure VM

When migrating from SQL Server on-premises to SQL Server on Azure Virtual Machines, it's unlikely that you have any compatibility or feature parity issues, if the source and target SQL Server versions are the same. If you're not upgrading the version of SQL Server, skip this step and move to the Migrate section.

Before migration, it's still a good practice to run an assessment of your SQL Server databases to identify migration blockers (if any) and the Migrate databases by using the Azure SQL Migration extension for Azure Data Studio does that before migration.

Assess user databases

The Migrate databases by using the Azure SQL Migration extension for Azure Data Studio provides a seamless wizard based experience to assess, get Azure recommendations and migrate your SQL Server databases on-premises to SQL Server on Azure Virtual Machines. Besides highlighting any migration blockers or warnings, the extension also includes an option for Azure recommendations to collect your databases' performance data to recommend a right-sized SQL Server on Azure Virtual Machines to meet the performance needs of your workload (with the least price).

To learn more about Azure recommendations, see Get Azure recommendations to migrate your SQL Server database.

Important

To assess databases using the Azure SQL migration extension, ensure that the logins used to connect the source SQL Server are members of the sysadmin server role or have CONTROL SERVER permission.

For a version upgrade, use Overview of Data Migration Assistant to assess on-premises SQL Server instances if you're upgrading to an instance of SQL Server on Azure Virtual Machines with a higher version to understand the gaps between the source and target versions.

Assess the applications

Typically, an application layer accesses user databases to persist and modify data. Data Migration Assistant can assess the data access layer of an application in two ways:

• By using captured extended events or SQL Server Profiler traces of your user databases. You can also use the Capture a trace in Database Experimentation Assistant to create a trace log that can also be used for A/B testing.
• By using the Data Access Migration Toolkit (preview), which provides discovery and assessment of SQL queries within the code and is used to migrate application source code from one database platform to another. This tool supports popular file types like C#, Java, XML, and plain text. For a guide on how to perform a Data Access Migration Toolkit assessment, see the Use Data Migration Assistant blog post.

During the assessment of user databases, use Data Migration Assistant to import captured trace files or Data Access Migration Toolkit files.

Assessments at scale

If you have multiple servers that require Azure readiness assessment, you can automate the process by using scripts using one of the following options. To learn more about using scripting see Migrate databases at scale using automation (Preview).

• Az.DataMigration PowerShell module
• az datamigration CLI extension
• Run Data Migration Assistant from the command line

For summary reporting across large estates, Data Migration Assistant assessments can also be consolidated into Azure Migrate.

Upgrade databases with Data Migration Assistant

For upgrade scenario, you might have a series of recommendations to ensure your user databases perform and function correctly after upgrade. Data Migration Assistant provides details on the affected objects and resources for how to resolve each issue. Make sure to resolve all breaking changes and behavior changes before you start production upgrade.

For deprecated features, you can choose to run your user databases in their original compatibility mode if you want to avoid making these changes and speed up migration. This action prevents upgrading your database compatibility until the deprecated items are resolved.

Caution

Not all SQL Server versions support all compatibility modes. Check that your target SQL Server version supports your chosen database compatibility. For example, SQL Server 2019 doesn't support databases with level 90 compatibility (which is SQL Server 2005). These databases would require, at least, an upgrade to compatibility level 100.

Azure SQL Database

After data sources are discovered, assess any on-premises SQL Server databases that can be migrated to Azure SQL Database to identify migration blockers or compatibility issues.

The Migrate databases by using the Azure SQL Migration extension for Azure Data Studio provides a seamless wizard based experience to assess, get Azure recommendations and migrate your SQL Server databases on-premises to SQL Server on Azure Virtual Machines. Besides highlighting any migration blockers or warnings, the extension also includes an option for Azure recommendations to collect your databases' performance data to recommend a right-sized Azure SQL Managed Instance to meet the performance needs of your workload (with the least price).

You can use the Azure SQL Migration extension for Azure Data Studio to assess databases to get:

• Assessment rules for SQL Server to Azure SQL Database migration
• Get Azure recommendations to migrate your SQL Server database

To assess your environment using the Azure SQL Migration extension, follow these steps:

1. Open the Azure SQL migration extension for Azure Data Studio.
2. Connect to your source SQL Server instance.
3. Select Migrate to Azure SQL, in the Azure SQL Migration wizard in Azure Data Studio.
4. Select databases for assessment, then select Next.
5. Select your Azure SQL target, in this case, Azure SQL Database.
6. Select View/Select to review the assessment report.
7. Look for migration blocking and feature parity issues. The assessment report can also be exported to a file that can be shared with other teams or personnel in your organization.
8. Determine the database compatibility level that minimizes post-migration efforts.

To get an Azure recommendation using the Azure SQL Migration extension, follow these steps:

1. Open the Azure SQL migration extension for Azure Data Studio.
2. Connect to your source SQL Server instance.
3. Select Migrate to Azure SQL, in the Azure SQL Migration wizard in Azure Data Studio.
4. Select databases for assessment, then select Next
5. Select your Azure SQL target, in this case, Azure SQL Database.
6. Navigate to the Azure recommendations sections, select Get Azure recommendation.
7. Select Collect performance data now. Select a folder on your local computer to store the performance logs, and then select Start.
8. After 10 minutes, Azure Data Studio indicates that a recommendation is available for Azure SQL Database.
9. Check the Azure SQL Database card, in the Azure SQL target panel to review your Azure SQL Database SKU recommendation.

To learn more, see Tutorial: Migrate SQL Server to Azure SQL Database (offline).

If the assessment encounters multiple blockers to confirm that your database isn't ready for an Azure SQL Database migration, then alternatively consider:

• Migration overview: SQL Server to Azure SQL Managed Instance if there are multiple instance-scoped dependencies
• SQL Server on Azure Virtual Machines if both SQL Database and SQL Managed Instance fail to be suitable targets.

Scaled assessments and analysis

The Azure SQL migration extension for Azure Data Studio and Azure Migrate supports performing scaled assessments and consolidation of the assessment reports for analysis.

If you have multiple servers and databases that need to be assessed and analyzed at scale to provide a wider view of the data estate, see the following links to learn more:

• Migrate databases at scale using automation (Preview)
• Performing scaled assessments using PowerShell - Azure Migrate
• Analyzing assessment reports using Power BI - Azure Migrate

Important

Running assessments at scale for multiple databases, especially large ones, can also be automated using the Run Data Migration Assistant from the command line and uploaded to Azure Migrate for further analysis and target readiness.

Related content

• Migration guide: SQL Server to Azure SQL Managed Instance
• Migration guide: SQL Server to SQL Server on Azure Virtual Machines
• Migration guide: SQL Server to Azure SQL Database