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.