Migration guide: SQL Server to SQL Server on Azure Virtual Machines
Applies to:
SQL Server on Azure Virtual Machines
In this guide, you learn how to 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.
Complete pre-migration steps before continuing.
Migrate
After you complete the steps for the pre-migration stage, 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 with minimal downtime
- backup and restore
- detach and attach from a URL
- convert to a VM, upload to a URL, and deploy as a new VM
- log shipping
- ship a hard drive
- migrate objects outside user databases
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 Tutorial: Migrate SQL Server to SQL Server on Azure Virtual Machines with DMS:
- Download and install Azure Data Studio and the Azure SQL migration extension.
- Launch the Migrate to Azure SQL wizard in the extension in Azure Data Studio.
- Select databases for assessment and view migration readiness or issues (if any). Additionally, collect performance data and get right-sized Azure recommendation.
- Select your Azure account and your target SQL Server on Azure Machine from your subscription.
- 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.
- 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.
- 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.
- 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.
- Complete the cutover.
- Stop all incoming transactions to the source database.
- Make application configuration changes to point to the target database in SQL Server on Azure Virtual Machine.
- Take any tail log backups for the source database in the backup location specified.
- Ensure all database backups have the status Restored in the monitoring details page.
- Select Complete cutover in the monitoring details page.
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.
- 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.
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:
- Detach the database files from the on-premises database instance.
- Copy the detached database files into Azure Blob storage using the AzCopy command-line utility.
- 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:
- Convert physical or virtual machines to Hyper-V VHDs.
- Upload VHD files to Azure Storage by using the Add-AzureVHD cmdlet.
- 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 IaaS Agent extension. | |
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 IaaS Agent extension. |
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:
- 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.
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:
- Post-migration validation and optimization guide
- Checklist: Best practices for SQL Server on Azure VMs
- Azure cost optimization center
Related content
- Azure global infrastructure center
- Services and tools available for data migration scenarios
- What is Azure SQL?
- What is SQL Server on Windows Azure Virtual Machines?
- Azure Total Cost of Ownership (TCO) Calculator
- Cloud Adoption Framework for Azure
- Best practices for costing and sizing workloads for migration to Azure
- Change the license model for a SQL virtual machine in Azure
- Extend support for SQL Server with Azure
- Data Access Migration Toolkit (preview)
- Overview of Database Experimentation Assistant