Data Migration Assistant for SQL Server 2016 Upgrade

The Data Migration Assistant (DMA) has replaced the SQL Server Upgrade Wizard. The big difference is the Data Maigration Assistant also offers an option to Migrate the databases.

It's a key step in documenting how to update SQL Server. The details from the DMA give database owners detailed information about any changes required

The purpose of this post is to focus on Assessment only not on the Migration Aspect. The Assessment type of usage is very similar to the Upgrade Wizard.

There is also a command line option for the Data Migration Assistant , but I'll discuss the command line option in another post. If you want to automate the procedure of generating Compatibility reports than command line is the way to go - particularly if you have a large SQL Server inventory

 The Data Migration Assistant tool only needs to be installed once – and then you can chose the target server.The general advice is inot install on the directly on the OS hosting SQL Server. I normally select a low usage server used mainly for DBA reporting.

The other advice is not to execute the DMA during peak time. It's a resource intensive process and can have a negative impact on the SQL Server. The other advice offered by Microsoft is not run the "Compatibility issues" and the "New feature recommendations"  in the same test . Run them as separate tests

I won't include every screen , except these 3 , which highlight the basics of choosing Assessment versus Migration.

DMA Options - Choose Assessment (click to enlarge image)

DMA Options - Choose Compatibility Issues (Click to enlarge image)

DMA Options - Choose Server Name and Authentication method (Click to enlarge image)

Read More on Upgrade issues

SQL Server support levels

Perfmon SQL Server Deprecated Features Object

SQL Server Performance Checklist - SQL Server DBA