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

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 using backup and restore and log shipping that uses Data Migration Assistant for assessment.

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

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.

After you've discovered all the data sources, use Data Migration Assistant to assess on-premises SQL Server instances migrating to an instance of SQL Server on Azure Virtual Machines to understand the gaps between the source and target instances.

Note

If you're not upgrading the version of SQL Server, skip this step and move to the Migrate section.

Assess user databases

Data Migration Assistant assists your migration to a modern data platform by detecting compatibility issues that can affect database functionality in your new version of SQL Server. Data Migration Assistant recommends performance and reliability improvements for your target environment and also allows you to move your schema, data, and login objects from your source server to your target server.

To learn more, see Assessment.

Important

Based on the type of assessment, the permissions required on the source SQL Server can be different:

  • For the feature parity advisor, the credentials provided to connect to the source SQL Server database must be a member of the sysadmin server role.
  • For the compatibility issues advisor, the credentials provided must have at least CONNECT SQL, VIEW SERVER STATE, and VIEW ANY DEFINITION permissions.
  • Data Migration Assistant will highlight the permissions required for the chosen advisor before running the assessment.

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 a Data Migration Assistant assessment, you can automate the process by using the command-line interface. Using the interface, you can prepare assessment commands in advance for each SQL Server instance in the scope for migration.

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

Refactor databases with Data Migration Assistant

Based on the Data Migration Assistant assessment results, you might have a series of recommendations to ensure your user databases perform and function correctly after migration. 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 migration.

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.

You need to script all Data Migration Assistant fixes and apply them to the target SQL Server database during the post-migration phase.

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 steps for performing either a migration by using backup and restore or a minimal downtime migration by using backup and restore along with log shipping.

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.

Log shipping (minimize downtime)

To perform a minimal downtime migration by using backup and restore and log shipping:

  1. Set up connectivity to the 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. Ensure on-premises user databases to be migrated are in full or bulk-logged recovery model.
  3. Perform a full database backup to an on-premises location, and modify any existing full database backups jobs to use the COPY_ONLY keyword to preserve the log chain.
  4. 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 1-TB backups are recommended.)
  5. Restore full database backups on SQL Server on Azure Virtual Machines.
  6. Set up log shipping between the on-premises database and SQL Server on Azure Virtual Machines. Be sure not to reinitialize the databases because this task was already completed in the previous steps.
  7. Cut over to the target server.
    1. Pause or stop applications by using databases to be migrated.
    2. Ensure user databases are inactive by using single user mode.
    3. When you're ready, perform a log shipping controlled failover of on-premises databases to SQL Server on Azure Virtual Machines.

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.
TempDB 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've successfully completed 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