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, or 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.

Migration steps

This section provides an overview of the steps to take to migrate your SQL Server to Azure SQL Database, Azure SQL Managed Instance, or SQL Server on Azure VMs.

Azure SQL Managed Instance

To migrate your SQL Server to Azure SQL Managed Instance, follow these steps:

• Review the SQL Server database engine features available in Azure SQL Managed Instance.
• Choose a migration method and the corresponding tools for your method.
• Create a performance baseline to determine resource usage on your source SQL Server instance. This step helps you deploy a properly sized managed instance so that performance after migration isn't affected.
• Discover all SQL Server instances and features used by your organization.
• Assess your SQL Server databases to identify migration blockers or compatibility issues.
• Create a target SQL Managed Instance. Deploy 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.
• Configure connectivity and proper permissions to access both source and target.
• Migrate your database where you migrate your databases using offline migration or online migration options.
• Connect your application to Azure SQL Managed Instance.
• Monitor and remediate applications to ensure that you see the expected performance.

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.

SQL Server on Azure VM

To migrate your SQL Server to SQL Server on Azure VMs, follow these steps:

• Choose an appropriate migration strategy.
• Discover all SQL Server instances and features used by your organization.
• Assess your SQL Server databases to identify migration blockers or compatibility issues.
• Prepare a target SQL Server on Azure VM that's the same or greater version than the SQL Server source.
• Configure connectivity and proper permissions to access both source and target, such as, for example, with hybrid networking.
• Migrate your database where you migrate your databases using offline migration or online migration options.
• Monitor and remediate applications to ensure that you see the expected performance.

When migrating from SQL Server on-premises to SQL Server on Azure Virtual Machines, it's unlikely to have any compatibility or feature parity issues if the source and target SQL Server versions are the same. However, before migration, it's still a good practice to run an assessment of your SQL Server databases to identify migration blockers (if any).

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.

Upgrade databases with Data Migration Assistant

If the target SQL Server on Azure VM instance is a higher version, use Overview of Data Migration Assistant to assess on-premises SQL Server instances to understand the gaps between the source and target versions.

If you're upgrading the SQL Server version, you might have a series of recommendations to ensure your user databases perform and function correctly after the 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

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

• Review the database engine features available in Azure SQL Database.
• Discover all SQL Server instances and features used by your organization.
• Assess your SQL Server databases to identify migration blockers or compatibility issues.
• Choose your migration method and corresponding tools.
• Create a target Azure SQL Database.
• Configure connectivity and proper permissions to access both source and target.
• Monitor and remediate applications.

Discover

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

Use the following tools to discover your SQL Server instances:

• Azure Migrate to assess migration suitability of on-premises servers, perform performance-based sizing, and provide cost estimations for running them in Azure.
• 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.

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.

If your assessment encounters multiple blockers, consider migrating to one of the Azure SQL targets as an alternative, such as Azure SQL Managed Instance or SQL Server on Azure Virtual Machines.

Assess with Azure Data Studio

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 Azure. Besides highlighting any migration blockers or warnings, the extension also includes an option for Azure recommendations to collect your databases' performance data and recommends a right-sized Azure SQL target to meet the performance needs of your workload (with the lowest 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 migration targets
• 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.

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.
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.
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.

For specific Azure SQL migration target tutorials, see:

• Tutorial: Migrate SQL Server to Azure SQL Managed Instance with DMS
• Tutorial: Migrate SQL Server to Azure SQL Database (offline)

Assess with SQL Server enabled by Arc

To assess your SQL Server instances for migration to Azure, use SQL Server enabled by Azure Arc. This feature, currently in preview, automatically produces an assessment for migration to Azure, simplifying the discovery process and readiness assessment for migration.

To assess your instances using SQL Server enabled by Azure Arc, follow these steps:

1. Automatically connect SQL Server machines to Azure Arc.
2. Verify your Azure Extension for SQL Server (WindowsAgent.SqlServer) version is 1.1.2594.118 or later.
3. Go your SQL Server enabled by Azure Arc resource in the Azure portal.
4. Under Migration, select Assessments (Preview) to open the Assessments page and review results.

For details, see Assess instances for migration with SQL Server enabled by Azure arc.

Scaled assessments and analysis

The Azure SQL migration extension for Azure Data Studio and Azure Migrate can perform scaled assessments and consolidate 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 with DMS (Preview)
• Performing scaled assessments using PowerShell - Azure Migrate
• Analyzing assessment reports using Power BI - Azure Migrate

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.

You can automate the process by using scripts with 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.

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.

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