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 Web Services (AWS) EC2
  • Amazon Relational Database Service (AWS RDS)
  • Compute Engine (Google Cloud Platform - GCP)

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 2008 and SQL Server 2008 R2 by getting free security updates.

Choose appropriate target

Azure Virtual Machines run in many different regions of Azure and also offer a variety of 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.. To determine the VM size and storage requirements for your workload. it is recommended that these are sized through a Performance-Based Azure Migrate Assessment. If this is not 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 is 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

virtual machine migration downtime

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 very 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 additional steps required for migrating the Business Intelligence services such as SSIS, SSRS, and SSAS.
Migrate Use a migrate 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 migrate SQL Server database(s) to SQL Server on Azure virtual machines with minimal downtime.
Use when there is a requirement or desire to use features available in newer versions of SQL Server, or if there is 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 2008 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

Due 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). However, for databases starting in SQL Server 2014, that are smaller than 1 TB, and that have good connectivity to Azure, then SQL Server backup to URL is the better approach.

When migrating SQL Server databases to an instance of SQL Server on Azure VMs, it is important to choose an approach that suits when you need to cutover 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 2005 SQL Server 2008 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 both assessment and migration capabilities in a simple user interface.
Backup to a file SQL Server 2008 SP4 SQL Server 2008 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 2008 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.

Should not 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 the Azure Blob storage service and attach them to an instance of SQL Server on an Azure VM, particularly 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 2008 SP4 (Windows Only) SQL Server 2008 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
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 do not 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

          

Tip

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 Always On availability group option.
  • To minimize downtime without the overhead of configuring an availability group, use the log shipping 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 user database migrations.

These services include:

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-click 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 assists in 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 setup 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.