SALES: 1-800-867-1380

How to copy SQL Server data and setup files in a data disk from on-premises to Azure

Updated: March 28, 2014

SQL Server in Azure Virtual Machines provides you an easy migration path when you want to move your applications and databases to the cloud as-is. You can create virtual machine by using the platform provided SQL Server images or you can bring your own licensed SQL Server to the cloud.

This topic explains how to copy your SQL Server data files or setup bits to a virtual machine in Azure by using a local fixed size virtual hard disk (VHD) and the Add-AzureVhd cmdlet. 

This topic assumes that:

The following sections demonstrate how to copy SQL Server data files or setup bits to a virtual machine in Azure by using a data disk and the Add-AzureVHD cmdlet:

Create a virtual hard drive (VHD) file on your server

  1. With Windows Server 2008 R2, click Start, point to All Programs – Administrative Tools, then click Computer Management. With Windows Server 2012, press the Windows logo key to open the Start screen. Type Computer Management on the Start screen. Then, double-click Computer Management.

  2. In the Computer Management, click Storage and then right-click on the Disk Management. Click Create VHD.

  3. In the Create and Attach Virtual Hard Disk dialog window, Browse to the folder that you want to place your VHD file. Type MyTestData as a file name. Click Save. Then, choose Fixed Size as a virtual hard disk format. Set the Virtual Hard Disk size to the size that you need. Click OK.

  4. Select the new disk in the Disk Management window. Right-click on it and choose Initialize Disk. In the Initialize Disk dialog window, choose your new disk and also choose MBR (Master Boot Record).

  5. Right-click on the unallocated disk. Click New Simple Volume. When using the New Simple Volume Wizard, keep all default values. Set the drive letter (such as, G:\). Click Next. Click to perform quick format. Set the Volume Label, such as MyTestData. Click Next. Click Finish.

  6. After the wizard runs, you can see the new volume MyTestData in the Windows Explorer.

Copy SQL Server data files or setup bits to the VHD file

  1. Copy your SQL Server data files or setup bits to the new volume (such as, G:\). The copy process might take a few minutes or hours depending on the size of the files.

  2. With Windows Server 2008 R2, click Start, point to All Programs – Administrative Tools, then click Computer Management. With Windows Server 2012, on the Start screen, type Administrative Tools, and in the Search bar, click Settings. In the Settings results, click Administrative Tools. Then, double-click Computer Management.

  3. In the Computer Management, right-click the new volume (such as, G:\). Click Detach VHD so that you can upload it to Azure. In the Detach Virtual Hard Disk dialog window, do not check the Delete the virtual hard disk file after removing the disk option. Click OK.

Create and install a management certificate

  1. To upload a virtual hard drive contained in a VHD file to Azure, first create and install a management certificate. Then, obtain the thumbprint of the certificate and the subscription ID and set the connection to connect to your subscription in the Azure Platform Management Portal.

    You can use the Certificate Creation Tool (makecert.exe) to create an X.509 certificate that is used to authenticate operations with a Azure subscription. To create a certificate, open Visual Studio command prompt as an administrator. Then, type the following command:

    makecert -sky exchange -r -n "CN=<CertificateName>" -pe -a sha1 -len 2048 -ss My "<CertificateName>.cer"
    
    

    Where <CertificateName> is the name that you want to use for the certificate. It must have a .cer extension. For more information, see Create and Upload a Management Certificate for Azure.

    After you create the certificate, add it to your subscription in Azure.

  2. Sign in to the Azure Platform Management Portal.

  3. Click Settings, and then click Management Certificates.

  4. Click the Upload a Management Certificate arrow.

  5. In the Upload a management certificate dialog window, browse to the certificate file. In the Subscription field, select the Azure subscription to which you want to add the management certificate. Click OK.

  6. In the center pane, click your certificate, and then record the Thumbprint and Subscription Identifier from the pane by copying and pasting it to a location where you can retrieve it later.

Upload the VHD file to Azure using Add-AzureVHD

  1. Before uploading a VHD files to Azure, you need to make sure that you have a storage account. Create a storage account (such as, named as sqldeploymentstorage) if you don’t have one already. For information, see Creating and Uploading a Virtual Hard Disk that Contains the Windows Server Operating System.

  2. You need to prepare the connection to Azure. To do this, Run the following command in the Azure PowerShell window:

    Get-AzurePublishSettingsFile
    
    

    This command opens a browser window and automatically downloads a .publishsettings file that contains information and a certificate for your Azure subscription. Save the .publishsettings file.

  3. Run the following command in the Azure PowerShell window:

    Import-AzurePublishSettingsFile –PublishSettingsFile ‘<FullPathToPublishSettingsFile>’
    
    
  4. Set the current Azure subscription as follows:

    Select-AzureSubscription -SubscriptionName "YourSubscriptionName "
    
    
  5. Set the -CurrentStorageAccount property of your subscription to specify the storage account to use for VHD storage:

    Set-AzureSubscription -SubscriptionName '<SubscriptionName>' -CurrentStorageAccount ‘<YourStorageAccount>'
    
    
  6. Then, you need to upload the .vhd file to Azure by using the Add-AzureVHD cmdlet in the Azure PowerShell window:

    Add-AzureVhd -Destination <BlobStorageURL>/<YourVHDFolder>/<VHDName> -LocalFilePath <PathToVHDFile> 
    
    
    

    Note that, BlobStorageURL is the URL for the storage account that you created earlier, YourVHDFolder is the container within blob storage where you want to store your virtual hard disks. VHDName is the label that appears in the Management Portal to identify the virtual hard disk. PathToVHDFile is the full path and name of the .vhd file in your local computer.

    The upload process might take a few minutes.

Access SQL Server data files or setup bits in Azure virtual machine

  1. After the Add-AzureVHD operation completes, log on to the Azure Platform Management Portal.

  2. Click Disks at the Virtual Machines panel. In the Create a Disk from VHD dialog window, type Name as SQLServerSetupFiles. Then, browse to the VHD URL. Choose the storage account “sqldeploymentstorage” and the container “vhdcontainer”, and then SQLSetupFiles.vhd. Do not check the “The VHD contains an operating system”.

  3. Click the virtual machine that you want to attach the data disk to. For more information on how to create a SQL Server virtual machine in Azure using the platform-supplied image, see Provisioning a SQL Server Virtual Machine on Azure. You may also create Create a Virtual Machine Running Windows Server using the platform-supplied Windows Server image.

  4. Click Attach at the bottom panel. Choose Attach disk. In the Attach a disk to the virtual machine dialog window, choose the disk that you want to attach to. Choose the disk that you recently uploaded, such as SQLServerSetupFiles.vhd. Click the check mark to complete the process.

  5. Once the disk is attached to the virtual machine, connect to the virtual machine by using the remote desktop connection button, CONNECT.

  6. When you log onto the virtual machine, you should be able to see the new volume (such as, MyTestData) in the Windows Explorer. If the new volume includes SQL Server setup bits, you can run setup.exe to install SQL Server to your virtual machine in Azure. If it contains your database files, you can attach or restore them in your virtual machine in Azure.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft