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.

Advertisements

Another way to move SQL DB to Azure

About 8 months ago I wrote an article (http://www.sqlservercentral.com/articles/SQL+Server/139998/ ) where I showed you how to migrate your “earth” SQL Database to Azure using a BACPAC import. If you look at the following diagram I will now show you the middle route of migration using the deployment wizard.

First you need to make sure that your local (earthed-based) database is V12 compatible and if not address those issues first else the migration will fail.

ape

Next connect to your local SQL Server database using a recent release of SQL Server Management Studio (the one that you want to migrate) and under tasks you will want to select “Deploy Database to Microsoft Azure SQL Database”.

ape2

Next you will need to make sure that you enter valid deployment settings for your target connection.

dasdasfsfsfasfsfwe

So once you have selected the connect button you will need to connect to your Azure SQL Server using your server admin account.

ape4

Back to the main screen you will then have the ability to change basic database settings such as edition and size.

ape6

Once you are happy with the details on the summary screen just click finish.

ape7

Naturally the time this process takes will depend on the amount of data you are moving and on what sort of connection you have to the Azure DC.

I like to confirm the migration went well, so I connected via SSMS to check for the existence of my database.

ape0

So now I have covered another way to move to Azure.