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]).
Migrating to SQL Server on Azure Virtual Machines requires the following resources:
- Data Migration Assistant.
- An Azure Migrate project.
- A prepared target SQL Server on Azure Virtual Machines instance that's the same or greater version than the SQL Server source.
- Connectivity between Azure and on-premises.
- Choosing an appropriate migration strategy.
Before you begin your migration, you need to discover the topology of your SQL environment and assess the feasibility of your intended migration.
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.
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.
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.
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.
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
VIEW SERVER STATE, and
VIEW ANY DEFINITIONpermissions.
- 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:
- By using captured extended events or SQL Server Profiler traces of your user databases. You can also use the 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.
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.
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.
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:
- 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).
- Pause or stop any applications that are using databases intended for migration.
- Ensure user databases are inactive by using single user mode.
- Perform a full database backup to an on-premises location.
- 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.)
- 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:
- 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).
- Ensure on-premises user databases to be migrated are in full or bulk-logged recovery model.
- 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.
- 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.)
- Restore full database backups on SQL Server on Azure Virtual Machines.
- 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.
- Cut over to the target server.
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.
|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.|
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.
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.
The test approach to database migration consists of the following activities:
- 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.
- 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.
- Run validation tests: Run validation tests against the source and the target, and then analyze the results.
- Run performance tests: Run performance tests against the source and target, and then analyze and compare the results.
Use the Database Experimentation Assistant to assist with evaluating the target SQL Server performance.
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:
- Post-migration validation and optimization guide
- Tuning performance in Azure SQL virtual machines
- Azure cost optimization center
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.