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.

dma

dma1

The install takes less than a minute.

dma3

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

dma4

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

dma5

Then hit CREATE.

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

dma6

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.

dma8

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.

dma10

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.

2 thoughts on “SQL Server DMA – Data Migration Assistant V2.0

  1. Pingback: SQL Server DMA – Data Migration Assistant V2.0 - SQL Server Blog - SQL Server - Toad World

  2. Pingback: Using SQL Server DMA – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s