Edit

Share via

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

  • Article

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 (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:

  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.
  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 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:

  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:

Related content