SQL Server DMA v2.0 – Migration Time

In my last post I showed you how to conduct an assessment using DMA – Database Migration Assessment tool v2.0

This time let’s do an actual migration from SQL 2008R2 to SQL 2014.


I created a new migration project as shown above.

Next section is all about the permissions for the source and target SQL Servers. For the Source you will need CONTROL SERVER permission and the target you will need to be SYSADMIN.


Now we can add databases and setup the share to where the backup operation will take place. The share must be accessible for both the source and target server as the backup file will be the object used for migration.

You then need to enter the details to where you want the data files and log files to be placed on the target SQL Server.


Select the logins to move.


The tool tells you to validate the login(s) after migration – we don’t want them orphaned.

The stages that the tool goes through are as follows (well this is what I observed anyways):

  • Verifies metadata for the migration.
  • Takes the backup.


Nice name for a backup. (This is the share mentioned earlier)


  • Restores the database to the destination (it actually does something before this but it was so quick I missed it – maybe it validates the backup file)


  • Move the logins associated with the database.


A report is generated which is in CSV format.


By the way the backup file created via the tool is temporary, after a migration it is deleted. Also the compatibility level DOES NOT change, you need to do this yourself.


SQL Server DMA – Data Migration Assistant V2.0

One of the benefits of twitter is that you get to hear about new tools quickly, one of the tools being the DMA – Data Migration Assistant (Version 2).

It “enables you to upgrade to a modern data platform by detecting compatibility issues that can impact database functionality on your new version of SQL Server. It recommends performance and reliability improvements for your target environment. It allows you to not only move your schema and data, but also uncontained objects from your source server to your target server”. It can be found at this link: https://www.microsoft.com/en-us/download/details.aspx?id=53595.

Supported versions for this tool are as follows.

Source: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, and SQL Server 2016

Target: SQL Server 2012, SQL Server 2014, and SQL Server 2016

So let’s see it in action. I will be running the tool where I want to move from SQL Server 2008R2 to SQL Server 2014 where I will be doing an assessment only (Not the actual migration which this tool is capable of). I will be showing the migration capability in other post.

Note: Documentation states NOT to run this tool directly on the SQL Server host machine.



The install takes less than a minute.


You will be presented with the start screen as shown below.


You then click on the + button to create a new project. For this project I will be doing an assessment.


Then hit CREATE.

We have some further settings to complete – more around the source and target SQL Server.


The next section creates connections to the servers of interest – I will be using Windows authentication. The permissions needed for this tool are: CONNECT SQL, VIEW SERVER STATE and VIEW ANY DEFINITION.

If you have successfully connected to the server you will then need to select the database name that you would like to carry out an assessment on.

Now we are ready for the assessment to start.


Let’s look at the outcome.

Ok, so it provided information on behaviour changes that they recommend and information regarding if we are using deprecated features.


Handy indeed and it absolutely detects compatibility issues (and “breaking changes”), however I would’ve liked to see some more areas explored.

I purposely set some poor settings for the database to see if the tool would pick them up, things that I would personally check during a migration, such as Page verify = Torn_page_detection. None of them were highlighted during the assessment, maybe it is not the goal of the tool to do this but I would personally like these extra snippets of information from my environments.