How to Run the DMA Tool in bulk

Problem

In one of my previous post I was discussing how to discover your SQL Server estate. Now that you know all the targets you need to assess them through the DMA Tool.

You don’t want to do that one by one.

You want to run script that does the job for you, go to grab a coffee, play pool table, and when you come back you want the job to be over.

Let’s do this.

Solution

We are going to use a script provided by Microsoft called DMACMD.

First of all you need to watch this video and see what are the implications of running the assessment:

  • Run the DMACMD tool for around 10 instances at the time. The script has a heavy resources consumption both on the machine where you are running the script and the target machines.
  • You need to run the DMACMD tool twice, once targeting Managed Instances and once targeting Azure SQL Database.
  • Resource consumption is not based on the database size but on the database complexity (objects and schema).

I’ve seen this script running for 1-2 hours multiple times. Staring at the terminal will not make it run faster. Run it before lunch and when you will be back hopefully it will be over as well as your pad thai.

Troubleshooting

This is a script provided by Microsoft.

This means that they allocated enough resources to it to be delivered and the documentation is lame.

Here a list of problems you will certainly face if you run it against a SQL Server estate.

The network path was not found

You run the DMACMD and you incur in the error:

                Inner exception type: System.ComponentModel.Win32Exception
                    Message:
                        The network path was not found
                    HResult : 0x80004005
                        FacilityCode : 0 (000)
                        ErrorCode : 16389 (4005)
                    Error : 53'
DmaCmd.exe Information: 0 : Workflow host unhandled exception reported, ErrorId '2856896974377884355' with exception type 'ActionExecutionException'
DmaCmd.exe Information: 0 : The workflow host has stopped with exit code 1 in assembly 'WorkflowHostProgram' and version '5.4.5184.4'

This is because DMACMD cannot connect to the target instance.

Scroll up and you will see the line Unable to get user databases from server 'The-Name-of-your-Server'. comment that line in the .xml file that you are using to run the DMACMD or eventually find out why it cannot connect to it. You misspelled the name? Not enough privileges?

The DMACMD never ends running 

It’s unfortunately normal to see the DMACMD tool to run for 1 or 2 hours.

But if you open the Task Manager and you find that the RAM keeps increasing and after several hours the process is still running the memory will be saturated and you will eventually end up with the error:

Oops, something went wrong.  Please report this bug with the details below.
Report on GitHub: https://github.com/lzybkr/PSReadLine/issues/new
-----------------------------------------------------------------------
Last 1 Keys:
 UpArrow

Exception:
System.ArgumentOutOfRangeException: The value must be greater than or equal to zero and less than the console's buffer size in that dimension.
Parameter name: top
Actual value was -1.
   at System.Console.SetCursorPosition(Int32 left, Int32 top)
   at Microsoft.PowerShell.PSConsoleReadLine.ReallyRender(RenderData renderData, String defaultColor)
   at Microsoft.PowerShell.PSConsoleReadLine.ForceRender()
   at Microsoft.PowerShell.PSConsoleReadLine.HistoryRecall(Int32 direction)
   at Microsoft.PowerShell.PSConsoleReadLine.PreviousHistory(Nullable`1 key, Object arg)
   at Microsoft.PowerShell.PSConsoleReadLine.ProcessOneKey(ConsoleKeyInfo key, Dictionary`2 dispatchTable, Boolean ignoreIfNoAction, Object arg)
   at Microsoft.PowerShell.PSConsoleReadLine.InputLoop()
   at Microsoft.PowerShell.PSConsoleReadLine.ReadLine(Runspace runspace, EngineIntrinsics engineIntrinsics)

I have reported the error but I haven’t found a solution.

Running the DMA Tool from the GUI hasn’t helped.

Red dot in the DMA Tool report 

I’ve seen this happening when the target server doesn’t has enough resources:

DMACMD error

Click on each dot, if you are lucky you will find that the database has been investigated anyway.

Running the DMA Tool again might work. If it doesn’t check if you have enough privileges on the target database.

Conclusions 

It’s crap and it’s the only way to make the job done.

Frankly I see that Microsoft is struggling to provide solutions for a wide range of customers, especially when the cloud changes so fast.

This tool will eventually be deprecated and I expect Azure Migrate to be a valid candidate for replacement.

In the meanwhile we have no other option.

Related

Leave a Reply

You have to agree to the comment policy.