Export (0) Print
Expand All

Migrating to SQL Server in an Azure Virtual Machine

Updated: March 12, 2014

This topic provides information on how to migrate an existing on-premise SQL Server database built on Windows Server platform to SQL Server in Azure Virtual Machines (VMs). The topic also includes a decision workflow of when to choose to run your SQL Server application in a Virtual Machine on the cloud or to migrate your existing SQL Server database to Azure SQL Database (SQL Database).

SQL Server in a Virtual Machine enables you to reduce the total cost of ownership of a deployment, management and maintenance of enterprise breadth applications by easily migrating these applications to Azure. Migrating existing SQL Server applications to Azure Virtual Machines requires minimal or no code changes. With SQL Server in a Virtual Machine, administrators and developers can still use the same development and administration tools that are available on-premises.

Authors: Selcin Turkarslan
Reviewers: Evgeny Krivosheev, Paolo Salvatori, Lindsey Allen, Steve Howard

Sections in this article

When to use SQL Server in an Azure Virtual Machine

Using SQL Server in an Azure Virtual Machine can enable many of the on-premises scenarios in the cloud:

  • Rapid application development and testing: Develop a database application that requires some validation in a production like environment. Instead of purchasing a new hardware to do validation or testing of your new application, use SQL Server in a Virtual Machine. Simply, create a virtual machine by using the platform image that contains SQL Server or upload your own image to Azure. Then, connect to your new machine, move your data and setup your application, test and perform fixes.

  • Virtualization platform total cost of ownership: Move the existing on-premises virtualization platform to Azure. Instead of purchasing a new hardware to run the increasing number of enterprise breadth applications in your own virtualization platform on-premises, move them to Azure by leveraging the SQL Server infrastructure in an Azure Virtual Machine.

  • Rapid on-demand scale: Get additional computer, storage, and network resources to handle seasonal application usage peaks. Instead of purchasing an additional hardware just needed for a specific time period, use the SQL Server infrastructure in Azure Virtual Machines.

  • Data availability and mobility: Hosting SQL Server databases in Azure Virtual Machines makes them available to both on-premises and cloud applications.

Choosing between SQL Server in Azure Virtual Machine vs. Azure SQL Database

The following decision work flow explains when to choose SQL Server in a Virtual Machine and Azure SQL Database:

  • For new database applications, use either Azure SQL Database (SQL Database) or SQL Server in a Virtual Machine in Azure:

    • If SQL Database supports all the required features, provision a new SQL Database instance in Azure. Develop your new database application by using Azure SDK and plugins for Visual Studio, Java, PHP, or Node.js. Deploy your application to Azure and create your tables in SQL Database.

    • If SQL Database does not support all the required features and you do not want to invest in re-design changes in your application database, provision a new Virtual Machine with the SQL Server platform image supplied at the Management Portal. Create a database deployment package by using SQL Server Data Tools. Deploy this database package to SQL Server in a Virtual Machine. You can manage, upgrade, and monitor your database by using the traditional administrative tools, such as SQL Server Management Studio.

  • For existing database applications, identify which databases you want to migrate to SQL Server in Azure Virtual Machines first. Then, follow one of these two options:

    • Convert physical or virtual machines to Hyper-V VHDs by using System Center 2012 Virtual Machine Manager a physical-to-virtual machine (P2V) or a virtual-to-virtual (V2V) wizard. Upload VHD files to Azure Storage by using the Add-AzureVHD cmdlet. You can then deploy a new virtual machine by using the uploaded VHD. You can manage, upgrade, and monitor your database by using the traditional administrative tools, such as SQL Server Management Studio.

    • Provision a new Virtual Machine with the SQL Server platform image as well as an application-tier cloud compute resources at the Azure Management Portal. Create a database deployment package by using SQL Server Data Tools and SQL Server Management Studio. Migrate the existing application-tier to Azure Project by using Azure SDK and plugins for Visual Studio, Java, PHP, or Node.js. Deploy an application-tier to Azure and access your data on the cloud.

Migrating database schema and data to SQL Server in an Azure Virtual Machine

While migrating your database and data to SQL Server in an Azure Virtual Machine, follow these steps in the order specified:

  1. Prepare your database schema and data file on-premises using DAC, backup, or detach. For more information, see How to prepare the schema and data on–premises and upload them to an instance of SQL Server in a Virtual Machine below.

  2. Optionally, compress and encrypt your files before transmitting them to Azure.

  3. Transmit your database schema, data and log files to Azure. If you use the Add-AzureVHD cmdlet, first place your files in a virtual hard drive (VHD) and then upload the VHD to Azure. For more information, see How to move your database schema and data file to Azure Virtual Machine below.

  4. Load your database schema and data file to SQL Server in a Virtual Machine. For more information, see How to prepare the schema and data on–premises and upload them to an instance of SQL Server in a Virtual Machine below.

  5. Recreate any metadata that the migration tools could not create on the SQL Server on Azure Virtual Machine.

Starting with SQL Server 2014 release, you can also use the Deploy a SQL Server Database to an Azure VM Wizard in SQL Server Management Studio to deploy a database from an instance of the Database Engine to SQL Server in an Azure Virtual Machine. For more information, see Deploy a SQL Server Database to an Azure Virtual Machine.

How to prepare the schema and data on–premises and upload them to an instance of SQL Server in a Virtual Machine

This section describes how to prepare your database schema and data files on-premises. There are several options that you can choose depending on your needs:

Option 1: Data-tier Applications .BACPAC or .DACPAC files

You can use a Data-tier Application (DAC) to prepare your database schema and data file to be transmitted from on-premises to Azure:

  • A .DACPAC file: A .dacpac file includes the definitions of all SQL Server objects - such as tables, views, and instance objects – associated with a user’s database. A DACPAC is focused on capturing and deploying database schema, including upgrading an existing database. For more information on to extract a data-tier application (DAC) package from an existing SQL Server database, see Extract a DAC From a Database.

  • A .BACPAC file: A .bacpac file includes the database schema as well as the data stored in the database. A BACPAC is focused on capturing schema and data. It is the logical equivalent of a database backup and cannot be used to upgrade existing databases. For more information on how to create a .bacpac file, see Export a Data-tier Application.

You can export the schema and the data of a database to a BACPAC file. Then, you can import the schema and the data into a new database in the host server. Both of these capabilities are supported by the database management tools: Server Management Studio and the DACFx API. For more information, see Import a BACPAC File to Create a New User Database and the Microsoft.SqlServer.Dac Namespace in the MSDN library.

Notes:

  • DAC operation does not encrypt the BACPAC or DACPAC files automatically. You should ensure that the communication between on-premises and Azure is secure. You can also encrypt the bacpac or dacfile file separately to get additional protection when the file is at rest in Azure Blob Storage or on-premises disk storage.

  • DAC does not support full-text catalogs.

  • To improve security, SQL Server Authentication logins are stored in a DAC package without a password. When the package is deployed or upgraded, the login is created as a disabled login with a generated password. To enable the logins, log in using a login that has ALTER ANY LOGIN permission and use ALTER LOGIN to enable the login and assign a new password that can be communicated to the user. This is not needed for Windows Authentication logins because their passwords are not managed by SQL Server.

Option 2. Backup and Restore

To move a database to another instance of SQL Server or to another server, you can use backup and restore operations. If both SQL Server on-premises and SQL Server in a Virtual Machine have the same version, you can copy a database backup file to the virtual machine and then restore the database. For more information, see Back Up and Restore of SQL Server Databases.

Notes:

  • Backup and restore is faster than DAC.

  • You can create a compressed backup of your data. For more information, see Backup Compression.

  • You can use the existing and familiar tools such as the SQL Server Management Studio Backup Wizard as well as third-party tools.

  • When you move the database to another server instance, you must re-create all the metadata of the dependent entities and objects in master and msdb on the destination server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance.

Option 3. Detach and Attach

To move a database to another instance of SQL Server or to another server, you can use detach and attach operations. Copy the data (.mdf, .ndf), and log (.ldf) files to a local folder of the virtual machine, and then attach the database. For more information, see Move a Database Using Detach and Attach.

Notes:

  • Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. It requires the source database to go offline. It is best for upgrading databases or moving very large databases.

  • You cannot detach a database if any of the following are true:

    • The database is replicated and published.

    • A database snapshot exists on the database.

    • The database is being mirrored in a database mirroring session.

    • The database is suspect.

    • The database is a system database.

  • It’s recommend that you take a new full backup and restart differential backups prior to detach.

  • When performing database detach and attach operations, you can use the Data Compression feature to help compress the data inside a database, and to help reduce the size of the database. In addition, you can use separate compression and decompression tools on the detached files.

  • When you attach a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins and jobs, on the other server instance. For more information, see Manage Metadata When Making a Database Available on Another Server Instance.

Option 4. Other SQL Server Techniques

You can use the following additional techniques to copy or move databases between servers:

  • You can use the Copy Database Wizard in SQL Server Management Studio to copy or move databases between servers or to upgrade a SQL Server database to a later version. For more information, see Use the Copy Database Wizard. If you want to use this tool to copy databases from on-premises to Azure, you need to setup Azure Virtual Network for hybrid connectivity.

  • The SQL Server Import and Export Wizard provides a method of copying data between data sources and of constructing basic packages. For more information about the wizard, see SQL Server Import and Export Wizard. The purpose of the SQL Server Import and Export Wizard is to copy data from a source to a destination. The wizard can also create a destination database and destination tables for you. However, if you have to copy multiple databases or tables, or other kinds of database objects, you should use the Copy Database Wizard instead. If you want to use the SQL Server Import and Export wizard to copy databases from on-premises to Azure, you need to setup Azure Virtual Network for hybrid connectivity.

  • You can use the Generate and Publish Scripts Wizard to create scripts for transferring a database between instances of the SQL Server Database Engine. The generated scripts can be run on another instance of the Database Engine. You can also use the wizard to publish the contents of a database directly to a Web service created by using the Database Publishing Services. You can create scripts for an entire database, or limit it to specific objects. For more information, see Generate and Publish Scripts Wizard.

  • You can use the Transfer class of the SQL Server Management Objects (SMO) library. For more information, see Transferring Data. SMO allows the source and target databases to remain online and does not require moving the database files to/from Azure blob storage in a separate step. The disadvantage of SMO is that it would use a client connection to the database on either side which would use TDS and be very inefficient for large data sets.

  • The Transfer Database Task can either copy or move a SQL Server database between two instances of SQL Server. The database can be transferred by using online or offline mode. When you use online mode, the database remains attached and it is transferred by using SQL Management Object (SMO) to copy the database objects. When you use offline mode, the database is detached, the database files are copied or moved, and the database is attached at the destination after the transfer finishes successfully.

How to move your database schema and data file to Azure Virtual Machine

You can copy small files (database backups, BACPAC, or DACPAC files) to the virtual machine using copy/paste while connected using remote desktop.

To transfer large files, select one of the following options:

  • Use the Add-AzureVhd cmdlet to upload VHD files to Azure. A VHD file may include a database. For more information, see Creating and Uploading a Virtual Hard Disk that Contains the Windows Server Operating System.

  • Upload the file to BLOB storage in the same data center as the virtual machine, and then remote desktop to the virtual machine and download the file from BLOB storage. For more information, see Understanding Cloud Storage.

  • Copy the schema and data files to a shared folder in the virtual machine directly.

  • Use a web browser to download a database from the Internet. For example, you can download the AdventureWorks database from codeplex

The following table describes some common transfer methods that you can use when you want to move files to an Azure Virtual Machine. The table also explains the advantages and disadvantages of each method.

 

Transfer Method Advantages Disadvantages

VHD copy to the Azure Blob storage by using add-AzureVhd cmdlet

  • Fast, optimized for Azure

  • Tools can handle unreliable connections

  • Secure transfer

  • Microsoft provides the Add-AzureVhd cmdlet. If a graphical user interface is needed, you can use the third-party tools currently.

  • Before using the Add-AzureVhd cmdlet to upload a VHD to Azure, you must prepare a VHD; and create and upload a management certificate at the portal.

  • You need to attach the uploaded VHD to an Azure virtual machine as a data disk.

File copy to the virtual machine share

  • Simple to use

  • Multiple client tools are available

  • File placed to the virtual machine directly

  • It requires a VPN connection.

  • Only a few file copy tools support restart and resume.

For information on how to setup, configure, and deploy SQL Server in a Virtual Machine, see Tutorial: Provisioning a SQL Server Virtual Machine on Azure. This tutorial describes how to use the Azure Management Portal to select and install a virtual machine from the gallery. In addition, it shows how to connect to the virtual machine using Remote Desktop and also how to connect to SQL Server in the virtual machine using SQL Server Management Studio.

For more comprehensive information, see Getting Ready to Migrate to SQL Server in Azure Virtual Machines and SQL Server Deployment in Azure Virtual Machines.

High Availability and Disaster Recovery when using SQL Server in an Azure Virtual Machine

See Also

Community Additions

ADD
Show:
© 2014 Microsoft