Edit

Migration guide: SQL Server to SQL Server on Azure Virtual Machines

  • Article

Applies to: SQL Server on Azure VM

In this guide, you learn how to discover, assess, and migrate your user databases from SQL Server to an instance of SQL Server on Azure Virtual Machines by tools and techniques based on your requirements.

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

For information about extra migration strategies, see the SQL Server VM migration overview. For other migration guides, see Azure Database Migration Guides.

Diagram that shows a migration process flow.

Prerequisites

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

Pre-migration

Before you begin your migration, you need to discover the topology of your SQL environment and assess the feasibility of your intended migration.

Discover

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 consider the Performance guidelines for SQL Server on Azure Virtual Machines.

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

Assess

When migrating from SQL Server on-premises to SQL Server on Azure Virtual Machines, it is unlikely that you'll 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 Azure SQL migration extension for Azure Data Studio does that before migration.

Note

If you are assessing the entire SQL Server data estate at scale on VMware, use Azure Migrate to get Azure SQL deployment recommendations, target sizing, and monthly estimates.

Assess user databases

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 right-sized Azure recommendation for your on-premises SQL Server database(s).

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 Data Migration Assistant to assess on-premises SQL Server instances if you are 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:

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.

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 impacted 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 will prevent upgrading your database compatibility until the deprecated items have been 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.

Migrate

After you've completed the pre-migration steps, you're ready to migrate the user databases and components. Migrate your databases by using your preferred migration method.

The following sections provide options for performing a migration in order of preference:

Migrate using the Azure SQL migration extension for Azure Data Studio (minimal downtime)

To perform a minimal downtime migration using Azure Data Studio, follow the high level steps below. For a detailed step-by-step tutorial, see Migrate SQL Server to SQL Server on Azure Virtual Machine online using Azure Data Studio:

  1. Download and install Azure Data Studio and the Azure SQL migration extension.
  2. Launch the Migrate to Azure SQL wizard in the extension in Azure Data Studio.
  3. Select databases for assessment and view migration readiness or issues (if any). Additionally, collect performance data and get right-sized Azure recommendation.
  4. Select your Azure account and your target SQL Server on Azure Machine from your subscription.
  5. Select the location of your database backups. Your database backups can either be located on an on-premises network share or in an Azure Blob Storage container.
  6. Create a new Azure Database Migration Service using the wizard in Azure Data Studio. If you have previously created an Azure Database Migration Service using Azure Data Studio, you can reuse the same if desired.
  7. Optional: If your backups are on an on-premises network share, download and install self-hosted integration runtime on a machine that can connect to source SQL Server and the location containing the backup files.
  8. Start the database migration and monitor the progress in Azure Data Studio. You can also monitor the progress under the Azure Database Migration Service resource in Azure portal.
  9. Complete the cutover.
    1. Stop all incoming transactions to the source database.
    2. Make application configuration changes to point to the target database in SQL Server on Azure Virtual Machine.
    3. Take any tail log backups for the source database in the backup location specified.
    4. Ensure all database backups have the status Restored in the monitoring details page.
    5. Select Complete cutover in the monitoring details page.

Backup and restore

To perform a standard migration by using backup and restore:

  1. Set up connectivity to SQL Server on Azure Virtual Machines based on your requirements. For more information, see Connect to a SQL Server virtual machine on Azure (Resource Manager).
  2. Pause or stop any applications that are using databases intended for migration.
  3. Ensure user databases are inactive by using single user mode.
  4. Perform a full database backup to an on-premises location.
  5. Copy your on-premises backup files to your VM by using a remote desktop, Azure Data Explorer, or the AzCopy command-line utility. (Greater than 2-TB backups are recommended.)
  6. Restore full database backups to the SQL Server on Azure Virtual Machines.

Detach and attach from a URL

Detach your database and log files and transfer them to Azure Blob storage. Then attach the database from the URL on your Azure VM. Use this method if you want the physical database files to reside in Blob storage, which might be useful for very large databases. Use the following general steps to migrate a user database using this manual method:

  1. Detach the database files from the on-premises database instance.
  2. Copy the detached database files into Azure Blob storage using the AZCopy command-line utility.
  3. Attach the database files from the Azure URL to the SQL Server instance in the Azure VM.

Convert to a VM, upload to a URL, and deploy as a new VM

Use this method to migrate all system and user databases in an on-premises SQL Server instance to an Azure virtual machine. Use the following general steps to migrate an entire SQL Server instance using this manual method:

  1. Convert physical or virtual machines to Hyper-V VHDs.
  2. Upload VHD files to Azure Storage by using the Add-AzureVHD cmdlet.
  3. Deploy a new virtual machine by using the uploaded VHD.

Note

To migrate an entire application, consider using Azure Site Recovery.

Log shipping

Log shipping replicates transactional log files from on-premises on to an instance of SQL Server on an Azure VM. This option provides minimal downtime during failover and has less configuration overhead than setting up an Always On availability group.

For more information, see Log Shipping Tables and Stored Procedures.

Ship a hard drive

Use the Windows Import/Export Service method to transfer large amounts of file data to Azure Blob storage in situations where uploading over the network is prohibitively expensive or not feasible. With this service, you send one or more hard drives containing that data to an Azure data center where your data will be uploaded to your storage account.

Migrate objects outside user databases

More SQL Server objects might be required for the seamless operation of your user databases post migration.

The following table provides a list of components and recommended migration methods that can be completed before or after migration of your user databases.

Feature Component Migration methods
Databases Model Script with SQL Server Management Studio.
The tempdb database Plan to move tempdb onto Azure VM temporary disk (SSD)) for best performance. Be sure to pick a VM size that has a sufficient local SSD to accommodate your tempdb.
User databases with FileStream Use the Backup and restore methods for migration. Data Migration Assistant doesn't support databases with FileStream.
Security SQL Server and Windows logins Use Data Migration Assistant to migrate user logins.
SQL Server roles Script with SQL Server Management Studio.
Cryptographic providers Recommend converting to use Azure Key Vault. This procedure uses the SQL VM resource provider.
Server objects Backup devices Replace with database backup by using Azure Backup, or write backups to Azure Storage (SQL Server 2012 SP1 CU2 +). This procedure uses the SQL VM resource provider.
Linked servers Script with SQL Server Management Studio.
Server triggers Script with SQL Server Management Studio.
Replication Local publications Script with SQL Server Management Studio.
Local subscribers Script with SQL Server Management Studio.
PolyBase PolyBase Script with SQL Server Management Studio.
Management Database mail Script with SQL Server Management Studio.
SQL Server Agent Jobs Script with SQL Server Management Studio.
Alerts Script with SQL Server Management Studio.
Operators Script with SQL Server Management Studio.
Proxies Script with SQL Server Management Studio.
Operating system Files, file shares Make a note of any other files or file shares that are used by your SQL servers and replicate on the Azure Virtual Machines target.

Post-migration

After you successfully complete the migration stage, you need to complete a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.

Remediate applications

After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this task might require changes to the applications in some cases.

Apply any fixes recommended by Data Migration Assistant to user databases. You need to script these fixes to ensure consistency and allow for automation.

Perform tests

The test approach to database migration consists of the following activities:

  1. Develop validation tests: To test the database migration, you need to use SQL queries. Create validation queries to run against both the source and target databases. Your validation queries should cover the scope you've defined.
  2. Set up a test environment: The test environment should contain a copy of the source database and the target database. Be sure to isolate the test environment.
  3. Run validation tests: Run validation tests against the source and the target, and then analyze the results.
  4. Run performance tests: Run performance tests against the source and target, and then analyze and compare the results.

Tip

Use the Database Experimentation Assistant to assist with evaluating the target SQL Server performance.

Optimize

The post-migration phase is crucial for reconciling any data accuracy issues, verifying completeness, and addressing potential performance issues with the workload.

For more information about these issues and the steps to mitigate them, see:

Next steps

To check the availability of services that apply to SQL Server, see the Azure global infrastructure center.

For a matrix of Microsoft and third-party services and tools that are available to assist you with various database and data migration scenarios and specialty tasks, see Services 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:

To assess the application access layer, see Data Access Migration Toolkit (preview).

For information about how to perform A/B testing for the data access layer, see Overview of Database Experimentation Assistant.