Migration overview: SQL Server to SQL Server on Azure VMs
Applies to: 
SQL Server on Azure VM
Learn about the different migration strategies to migrate your SQL Server to SQL Server on Azure Virtual Machines (VMs).
You can migrate SQL Server running on-premises or on:
- SQL Server on Virtual Machines
- Amazon Elastic Compute Cloud (Amazon EC2)
- Amazon Relational Database Service (Amazon RDS)
- Google Compute Engine
For other migration guides, see Database Migration.
Overview
Migrate to SQL Server on Azure Virtual Machines (VMs) when you want to use the familiar SQL Server environment with OS control, and want to take advantage of cloud-provided features such as built-in VM high availability, automated backups, and automated patching.
Save on costs by bringing your own license with the Azure Hybrid Benefit licensing model or extend support for SQL Server 2012 by getting free security updates.
Choose appropriate target
Azure Virtual Machines run in many different regions of Azure and also offer various machine sizes and Storage options. When determining the correct size of VM and Storage for your SQL Server workload, refer to the Performance Guidelines for SQL Server on Azure Virtual Machines..
You can use the Azure SQL migration extension for Azure Data Studio to get right-sized SQL Server on Azure Virtual Machines recommendation. The extension collects performance data from your source SQL Server instance to provide right-sized Azure recommendation that meets your workload's performance needs with minimal cost. To learn more, see Get right-sized Azure recommendation for your on-premises SQL Server database(s)
To determine the VM size and storage requirements for all your workloads in your data estate, it's recommended that these are sized through a Performance-Based Azure Migrate Assessment. If this isn't an available option, see the following article on creating your own baseline for performance.
Consideration should also be made on the correct installation and configuration of SQL Server on a VM. It's recommended to use the Azure SQL virtual machine image gallery as this allows you to create a SQL Server VM with the right version, edition, and operating system. This will also register the Azure VM with the SQL Server Resource Provider automatically, enabling features such as Automated Backups and Automated Patching.
Migration strategies
There are two migration strategies to migrate your user databases to an instance of SQL Server on Azure VMs: migrate, and lift and shift.
The appropriate approach for your business typically depends on the following factors:
- Size and scale of migration
- Speed of migration
- Application support for code change
- Need to change SQL Server Version, Operating System, or both.
- Supportability life cycle of your existing products
- Window for application downtime during migration
The following table describes differences in the two migration strategies:
| Migration strategy | Description | When to use |
|---|---|---|
| Lift & shift | Use the lift and shift migration strategy to move the entire physical or virtual SQL Server from its current location onto an instance of SQL Server on Azure VM without any changes to the operating system, or SQL Server version. To complete a lift and shift migration, see Azure Migrate. The source server remains online and services requests while the source and destination server synchronize data allowing for an almost seamless migration. |
Use for single to large-scale migrations, even applicable to scenarios such as data center exit. Minimal to no code changes required to user SQL databases or applications, allowing for faster overall migrations. No extra steps required for migrating the Business Intelligence services such as SSIS, SSRS, and SSAS. |
| Migrate | Use a migration strategy when you want to upgrade the target SQL Server and/or operating system version. Select an Azure VM from Azure Marketplace or a prepared SQL Server image that matches the source SQL Server version. Use the Azure SQL migration extension for Azure Data Studio to assess, get recommendations for right-sized Azure configuration (VM series, compute and storage) and migrate SQL Server database(s) to SQL Server on Azure virtual machines with minimal downtime. |
Use when there's a requirement or desire to migrate to SQL Server on Azure Virtual Machines, or if there's a requirement to upgrade legacy SQL Server and/or OS versions that are no longer in support. May require some application or user database changes to support the SQL Server upgrade. There may be additional considerations for migrating Business Intelligence services if in the scope of migration. |
Lift and shift
The following table details the available method for the lift and shift migration strategy to migrate your SQL Server database to SQL Server on Azure VMs:
| Method | Minimum source version | Minimum target version | Source backup size constraint | Notes |
|---|---|---|---|---|
| Azure Migrate | SQL Server 2008 SP4 | SQL Server 2012 SP4 | Azure VM storage limit | Existing SQL Server to be moved as-is to instance of SQL Server on an Azure VM. Can scale migration workloads of up to 35,000 VMs. Source server(s) remain online and servicing requests during synchronization of server data, minimizing downtime. Automation & scripting: Azure Site Recovery Scripts and Example of scaled migration and planning for Azure |
Note
It's now possible to lift and shift both your failover cluster instance and availability group solution to SQL Server on Azure VMs using Azure Migrate.
Migrate
Owing to the ease of setup, the recommended migration approach is to take a native SQL Server backup locally and then copy the file to Azure. This method supports larger databases (>1 TB) for all versions of SQL Server starting from 2008 and larger database backups (>1 TB). Starting with SQL Server 2014, for database smaller than 1 TB that have good connectivity to Azure, SQL Server backup to URL is the better approach.
When migrating SQL Server databases to an instance of SQL Server on Azure VMs, it's important to choose an approach that suits when you need to cut over to the target server as this affects the application downtime window.
The following table details all available methods to migrate your SQL Server database to SQL Server on Azure VMs:
| Method | Minimum source version | Minimum target version | Source backup size constraint | Notes |
|---|---|---|---|---|
| Azure SQL migration extension for Azure Data Studio | SQL Server 2008 | SQL Server 2012 | Azure VM storage limit | This is an easy to use wizard based extension in Azure Data Studio for migrating SQL Server database(s) to SQL Server on Azure virtual machines. Use compression to minimize backup size for transfer. The Azure SQL migration extension for Azure Data Studio provides assessment, Azure recommendation and migration capabilities in a simple user interface and supports minimal downtime migrations. |
| Distributed availability group | SQL Server 2016 | SQL Server 2016 | Azure VM storage limit | A distributed availability group is a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group don't need to be in the same location and include cross-domain support. This method minimizes downtime, use when you have an availability group configured on-premises. Automation & scripting: T-SQL |
| Backup to a file | SQL Server 2008 SP4 | SQL Server 2012 SP4 | Azure VM storage limit | This is a simple and well-tested technique for moving databases across machines. Use compression to minimize backup size for transfer. Automation & scripting: Transact-SQL (T-SQL) and AzCopy to Blob storage |
| Backup to URL | SQL Server 2012 SP1 CU2 | SQL Server 2012 SP1 CU2 | 12.8 TB for SQL Server 2016, otherwise 1 TB | An alternative way to move the backup file to the VM using Azure storage. Use compression to minimize backup size for transfer. Automation & scripting: T-SQL or maintenance plan |
| Database Migration Assistant (DMA) | SQL Server 2005 | SQL Server 2012 SP4 | Azure VM storage limit | The DMA assesses SQL Server on-premises and then seamlessly upgrades to later versions of SQL Server or migrates to SQL Server on Azure VMs, Azure SQL Database or Azure SQL Managed Instance. Shouldn't be used on FILESTREAM-enabled user databases. DMA also includes capability to migrate SQL and Windows logins and assess SSIS Packages. Automation & scripting: Command line interface |
| Detach and attach | SQL Server 2008 SP4 | SQL Server 2014 | Azure VM storage limit | Use this method when you plan to store these files using Azure Blob Storage and attach them to an instance of SQL Server on an Azure VM, useful with very large databases or when the time to backup and restore is too long. Automation & scripting: T-SQL and AzCopy to Blob storage |
| Log shipping | SQL Server 2012 SP4 (Windows Only) | SQL Server 2012 SP4 (Windows Only) | Azure VM storage limit | Log shipping replicates transactional log files from on-premises on to an instance of SQL Server on an Azure VM. This provides minimal downtime during failover and has less configuration overhead than setting up an Always On availability group. Automation & scripting: T-SQL |
| Convert on-premises machine to Hyper-V VHDs, upload to Azure Blob storage, and then deploy a new virtual machine using uploaded VHD | SQL Server 2012 or greater | SQL Server 2012 or greater | Azure VM storage limit | Use when bringing your own SQL Server license, when migrating a database that you'll run on an older version of SQL Server, or when migrating system and user databases together as part of the migration of database dependent on other user databases and/or system databases. |
| Ship hard drive using Windows Import/Export Service | SQL Server 2012 or greater | SQL Server 2012 or greater | Azure VM storage limit | Use the Windows Import/Export Service when manual copy method is too slow, such as with very large databases |
Tip
- For large data transfers with limited to no network options, see Large data transfers with limited connectivity.
- It's now possible to lift and shift both your failover cluster instance and availability group solution to SQL Server on Azure VMs using Azure Migrate.
Considerations
The following is a list of key points to consider when reviewing migration methods:
- For optimum data transfer performance, migrate databases and files onto an instance of SQL Server on Azure VM using a compressed backup file. For larger databases, in addition to compression, split the backup file into smaller files for increased performance during backup and transfer.
- If migrating from SQL Server 2014 or higher, consider encrypting the backups to protect data during network transfer.
- To minimize downtime during database migration, use the Azure SQL migration extension in Azure Data Studio or Always On availability group option.
- For limited to no network options, use offline migration methods such as backup and restore, or disk transfer services available in Azure.
- To also change the version of SQL Server on a SQL Server on Azure VM, see change SQL Server edition.
Business Intelligence
There may be additional considerations when migrating SQL Server Business Intelligence services outside the scope of database migrations.
SQL Server Integration Services
You can migrate SQL Server Integration Services (SSIS) packages and projects in SSISDB to SQL Server on Azure VM using one of the two methods below.
- Backup and restore the SSISDB from the source SQL Server instance to SQL Server on Azure VM. This will restore your packages in the SSISDB to the Integration Services Catalog on your target SQL Server on Azure VM.
- Redeploy your SSIS packages on your target SQL Server on Azure VM using one of the deployment options.
If you have SSIS packages deployed as package deployment model, you can convert them before migration. See the project conversion tutorial to learn more.
SQL Server Reporting Services
To migrate your SQL Server Reporting Services (SSRS) reports to your target SQL Server on Azure VM, see Migrate a Reporting Services Installation (Native Mode)
Alternatively, you can also migrate SSRS reports to paginated reports in Power BI. Use the RDL Migration Tool to help prepare and migrate your reports. Microsoft developed this tool to help customers migrate Report Definition Language (RDL) reports from their SSRS servers to Power BI. It's available on GitHub, and it documents an end-to-end walkthrough of the migration scenario.
SQL Server Analysis Services
SQL Server Analysis Services databases (multidimensional or tabular models) can be migrated from your source SQL Server to SQL Server on Azure VM using one of the following options:
- Interactively using SSMS
- Programmatically using Analysis Management Objects (AMO)
- By script using XMLA (XML for Analysis)
See Move an Analysis Services Database to learn more.
Alternatively, you can consider migrating your on-premises Analysis Services tabular models to Azure Analysis Services or to Power BI Premium by using the new XMLA read/write endpoints.
Server objects
Depending on the setup in your source SQL Server, there may be additional SQL Server features that will require manual intervention to migrate them to SQL Server on Azure VM by generating scripts in Transact-SQL (T-SQL) using SQL Server Management Studio and then running the scripts on the target SQL Server on Azure VM. Some of the commonly used features are:
- Logins and roles
- Linked server(s)
- External Data Sources
- Agent jobs
- Alerts
- Database Mail
- Replication
For a complete list of metadata and server objects that you need to move, see Manage Metadata When Making a Database Available on Another Server.
Supported versions
As you prepare for migrating SQL Server databases to SQL Server on Azure VMs, be sure to consider the versions of SQL Server that are supported. For a list of current supported SQL Server versions on Azure VMs, please see SQL Server on Azure VMs.
Migration assets
For additional assistance, see the following resources that were developed for real world migration projects.
| Asset | Description |
|---|---|
| Data workload assessment model and tool | This tool provides suggested "best fit" target platforms, cloud readiness, and application/database remediation level for a given workload. It offers simple, one-select calculation and report generation that helps to accelerate large estate assessments by providing and automated and uniform target platform decision process. |
| Perfmon data collection automation using Logman | A tool that collects Perform data to understand baseline performance that helps the migration target recommendation. This tool that uses logman.exe to create the command that will create, start, stop, and delete performance counters set on a remote SQL Server. |
| Multiple-SQL-VM-VNet-ILB | This whitepaper outlines the steps to set up multiple Azure virtual machines in a SQL Server Always On Availability Group configuration. |
| Azure virtual machines supporting Ultra SSD per Region | These PowerShell scripts provide a programmatic option to retrieve the list of regions that support Azure virtual machines supporting Ultra SSDs. |
The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.
Next steps
To start migrating your SQL Server databases to SQL Server on Azure VMs, see the Individual database migration guide.
For a matrix of the Microsoft and third-party services and tools that are available to assist you with various database and data migration scenarios as well as specialty tasks, see the article Service and tools for data migration.
To learn more about Azure SQL see:
To learn more about the framework and adoption cycle for Cloud migrations, see:
For information about licensing, see:
- Bring your own license with the Azure Hybrid Benefit
- Get free extended support for SQL Server
- To assess the Application access layer, see Data Access Migration Toolkit (Preview)
- For details on how to perform Data Access Layer A/B testing see Database Experimentation Assistant.
Feedback
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under: https://aka.ms/ContentUserFeedback.
Indsend og få vist feedback om