Use Data Migration Assistant (DMA) to migrate on-premises SQL Server database to Azure

By:   |   Updated: 2018-06-22   |   Comments (2)   |   Related: More > Azure

Problem

We have an on-premises SQL Server database and we have used the Data Migration Assistant (DMA) to check for compatibility issues before migration to Azure SQL database. Is it possible to use this tool to perform the actual migration of the database to SQL Server on an Azure virtual machine?

Solution

In part 1, of the tip, you learned about using the Data Migration Assistant (DMA) tool to check for any compatibility issues before the migration. You can refer to part 1, to follow the steps to download and install the tool.

In this tip, we will perform a demo of the actual migration of the SQL Server database from on-premises SQL Server to an Azure virtual machine.

Migrate from SQL Server Database to Azure Virtual Machine

For this demo, I downloaded the sample WideWorldImporters database and restored on my on-premises SQL instance. The plan is to migrate this WideWorldImporters database to a SQL Server on an Azure virtual machine. Start the Data Migration Assistant tool and begin the migration. You can refer to the screenshot below.

Start Migration Project

Provide a project name, select the “Target server type” as shown and click on “Create”. Once done, you will see the screen below where you need to enter the details for the source and target servers.

Connect to Source and Target Servers

On the screen below you will need to enter information about your source and target servers. 

In the “Target server details”, we have provided details of a SQL Server on Azure VM. You can refer this tip to create your own SQL Server on the Azure platform.

DMA-Source-Target-Server-Details

Once you click on “Next”, you will be able to see the list of databases on the source server as shown below. Select the database on the source server that you plan to migrate.

You will need to have a shared folder setup that both your on-premises and Azure VM can access.  This is used to migrate the database. Enter details of the shared folder on the source server. The source SQL Server service account needs to have read\write access to this folder. During the migration process, the source database will get backed up to this location. As the database needs to be restored on to a SQL Server on an Azure VM, ensure to create a shared directory that will have access from the Azure VM. In this demo, a file share on Azure is provided for the restore operation. The SQL service account on the Azure VM must have read privileges to this Azure file share. Also, the user account used for running the Data Migration Assistant tool must have sufficient privileges to perform the migration.

DMA_Select_Source_database

On the lower side of this window is the “Restore options” sections as shown.  The drive path locations get loaded by default, but can be changed as needed. Before you click on “Next”, if you don’t already have an Azure file storage to use, refer below steps to create one.

DMA_Select_Target_Restore_options

Steps to Create Azure File Share (if needed)

If you don’t already have an Azure file share, it can be created in a few minutes.  Go to the Azure portal https://portal.azure.com and click on the “Storage accounts” section as shown.

Azure file share - go to Storage Accounts

Once you click on “Storage accounts”, you will see this option to create a storage account. Refer to this screenshot to follow the steps.

Create Storage account

Once you click on “Create”, the storage account gets created in a few minutes. Click on the newly created storage account as shown.

Newly created storage account

Once you click on the storage account, you will see this screen where you have the option to create a new file share.

New File share

Click on the “Files” section to create a new file share.

Click on File share

When you click on the + symbol, it will give you the option to enter the file share name and allocate a size for the share.

File share quota

For this demo, 10 GB is sufficient as we are only migrating one sample database. Click on “Ok” and the file share gets created. When you navigate to the newly created file share, you will get the option to connect to the file share as shown. In order to connect to this file share from the source server and target server, copy the command section as shown and run them using PowerShell and this will create the necessary access.

Click on fileshare connect

Run the PowerShell commands on both source and target servers to connect to the file share.

On the source and target servers, run the PowerShell command as shown below. Just click on the copy icon and run it in PowerShell. The below screenshot shows a successful run of the command.

PowerShell Commands

Once the command runs successfully on the source and target server locations, you will see that the file share gets automatically created.

File Share created

You will see that the drive letter allocation is Y as we had used that while running the PowerShell commands. This drive letter (Y) is visible at the Windows layer, but wouldn’t be visible on the SQL Server side which is evident when we run the below command on SQL Server on the Azure VM.

Drive not visible to SQL Server

In order to make Y drive accessible from SQL Server, we need to run the same PowerShell command from within SQL Server using xp_cmdshell. Just copy it like before and embed it within xp_cmdshell and run the command as shown. This step is required for the restore operation to complete successfully on the target server otherwise the Data Migration Assistant tool will have issues.

Now you can see that the Y drive is visible to SQL Server.

Run command shell in SQL Server

Continue Data Migration Assistant Steps

After the Azure file share is successfully created, you can review the “Restore Options” section in the Data Migration Assistant wizard and click on “Next”. This will take you to the “Select logins” window. In this demo, I didn’t select any logins and left them as shown. Here, you have the option to review and select logins for migration.

DMA Select Logins

After reviewing, click on the “Start Migration” option to begin the migration process. You can view the progress of the migration as shown in this window.

View Migration progress

Once the migration process completes, you will see the status as shown. Migration is successful and the entire process took 4 minutes to complete.

Migration completed

Check Migrated SQL Server Database on Azure Virtual Machine

You can connect to the SQL Server Azure instance and confirm the database was restored successfully.

Database migrated successfully
Next Steps
  • With this tip, you were able to use the Data Migration Assistant (DMA) tool to migrate an on-premises SQL Server database to SQL Server on an Azure Virtual Machine
  • Try this tip using your own sample on-premises database
  • Refer this tip to check for any compatibility issues while migrating to Azure
  • To get familiar with Azure, refer to these Azure tips


Last Updated: 2018-06-22


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Sunday, June 24, 2018 - 5:52:22 PM - Mohammed Back To Top

Anne, it is not really possible to automate this using this tool.


Friday, June 22, 2018 - 3:58:39 PM - Anne Back To Top

Is there a way to automate this process?

for example I want to schedule and restore the database nightly from on premise to Azure?

Or any other options?

Thanks



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools