Data Migration Assistant: How to run from command line


With DMA v2.1 and above when you install DMA, it will also install dmacmd.exe in %ProgramFiles%\Microsoft Data Migration Assistant\. Use dmacmd.exe to assess your databases in an unattended mode. This is especially useful when assessing several databases or huge databases. Output the result to JSON or CSV file.

System_CAPS_ICON_note.jpg Note
Dmacmd.exe supports running assessments only. Migrations are not supported at this time.

Command line arguments

DmaCmd.exe /AssessmentName="string"
/AssessmentDatabases="connectionString1" \["connectionString2"\]
\[/AssessmentTargetPlatform="TargetPlatform"\]
/AssessmentEvaluateRecommendations|/AssessmentEvaluateCompatibilityIssues
\[/AssessmentOverwriteResult\]
/AssessmentResultJson="file"|/AssessmentResultCsv="file"
Argument Description Required (Y/N)
/help or /? How to use dmacmd.exe help text

 

N
/AssessmentName Name of the assessment project

 

Y
/AssessmentDatabases Space-delimited list of connection strings. Database name (Initial Catalog) is case-sensitive.

 

Y
/AssessmentTargetPlatform Target platform for the assessment, supported values: SqlServer2012, SqlServer2014, SqlServer2016, and AzureSqlDatabaseV12. Default is SqlServer2016

 

N
/AssessmentEvaluateFeatureParity Run feature parity rules

 

N
/AssessmentEvaluateCompatibilityIssues Run compatibility rules Y 
(Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateRecommendations is required.)

 

/AssessmentEvaluateRecommendations Run feature recommendations Y 
(Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateRecommendationsis required)

 

/AssessmentOverwriteResult Overwrite the result file

 

N
/AssessmentResultJson Full path to the JSON result file Y 
(Either AssessmentResultJson or AssessmentResultCsv is required)

 

/AssessmentResultCsv Full path to the CSV result file Y 
(Either AssessmentResultJson or AssessmentResultCsv is required)

 

Examples of assessments using the CLI

Dmacmd.exe

Dmacmd.exe /? or DmaCmd.exe /help

Single-database assessment using Windows authentication and running compatibility rules

DmaCmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;***Integrated Security=true*"**
***/AssessmentEvaluateCompatibilityIssues*** /AssessmentOverwriteResult
/AssessmentResultJson="C:\\temp\\Results\\AssessmentReport.json"

Single-database assessment using SQL Server authentication and running feature recommendation

DmaCmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;***User Id=myUsername;Password=myPassword;***"
***/AssessmentEvaluateRecommendations*** /AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\Results\\AssessmentReport.csv"

Single-database assessment for target platform SQL Server 2012, save results to .json and .csv file

DmaCmd.exe /AssessmentName="TestAssessment"
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
***/AssessmentTargetPlatform="SqlServer2012"***
/AssessmentEvaluateRecommendations /AssessmentOverwriteResult
***/AssessmentResultJson***="C:\\temp\\Results\\AssessmentReport.json"
***/AssessmentResultCsv***="C:\\temp\\Results\\AssessmentReport.csv"

Single-database assessment for target platform SQL Azure Database, save results to .json and .csv file

DmaCmd.exe /AssessmentName="TestAssessment" 
/AssessmentDatabases="Server=SQLServerInstanceName;Initial
Catalog=DatabaseName;Integrated Security=true"
/AssessmentTargetPlatform="AzureSqlDatabaseV12"
/AssessmentEvaluateCompatibilityIssues /AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult 
/AssessmentResultCsv="C:\\temp\\AssessmentReport.csv" 
/AssessmentResultJson="C:\\temp\\AssessmentReport.json"

Multiple-database assessment

DmaCmd.exe /AssessmentName="TestAssessment"
***/AssessmentDatabases="Server=SQLServerInstanceName1;Initial
Catalog=DatabaseName1;Integrated Security=true"
"Server=SQLServerInstanceName1;Initial Catalog=DatabaseName2;Integrated
Security=true" "Server=SQLServerInstanceName2;Initial
Catalog=DatabaseName3;Integrated Security=true"***
/AssessmentTargetPlatform="SqlServer2016"
/AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\Results\\AssessmentReport.csv"
/AssessmentResultJson="C:\\Results\\test2016.json"

Azure SQL Database SKU recommendations using the CLI

.\DmaCmd.exe /Action=SkuRecommendation
/SkuRecommendationInputDataFilePath="C:\TestOut\out.csv"
/SkuRecommendationTsvOutputResultsFilePath="C:\TestOut\prices.tsv"
/SkuRecommendationJsonOutputResultsFilePath="C:\TestOut\prices.json"
/SkuRecommendationOutputResultsFilePath="C:\TestOut\prices.html"
/SkuRecommendationCurrencyCode=USD
/SkuRecommendationOfferName=MS-AZR-0044p
/SkuRecommendationRegionName=UKWest
/SkuRecommendationSubscriptionId=<Your Subscription Id>
/AzureAuthenticationInteractiveAuthentication=true
/AzureAuthenticationClientId=<Your AzureAuthenticationClientId>
/AzureAuthenticationTenantId=<Your AzureAuthenticationTenantId>
.\DmaCmd.exe /Action=SkuRecommendation
/SkuRecommendationInputDataFilePath="C:\TestOut\out.csv"
/SkuRecommendationTsvOutputResultsFilePath="C:\TestOut\prices.tsv"
/SkuRecommendationJsonOutputResultsFilePath="C:\TestOut\prices.json"
/SkuRecommendationOutputResultsFilePath="C:\TestOut\prices.html"
/SkuRecommendationPreventPriceRefresh=true 
Argument Description Required (Y/N)
/Action=SkuRecommendation Execute SKU assessment using DMA command line Y
/SkuRecommendationInputDataFilePath Full path to the performance counter file collected from the computer hosting your databases Y
/SkuRecommendationTsvOutputResultsFilePath Full path to the TSV result file Y 
(Either TSV or JSON or HTML file path is required)
/SkuRecommendationJsonOutputResultsFilePath Full path to the JSON result file Y 
(Either TSV or JSON or HTML file path is required)
/SkuRecommendationHtmlResultsFilePath Full path to the HTML result file Y 
(Either TSV or JSON or HTML file path is required)
/SkuRecommendationPreventPriceRefresh Prevents the price refresh from occurring. Use if running in offline mode. Y 
(Either this argument is selected for static prices or all the arguments below need to be selected for getting latest prices)
/SkuRecommendationCurrencyCode The currency in which to display prices (e.g. "USD") Y 
(If you want to get the latest prices)
/SkuRecommendationOfferName The offer name (e.g. "MS-AZR-0003P"). See the Microsoft Azure Offer Details page for more information. Y 
(If you want to get the latest prices)
/SkuRecommendationRegionName The region name (e.g. "WestUS") Y 
(If you want to get the latest prices)
/SkuRecommendationSubscriptionId The subscription ID. Y 
(If you want to get the latest prices)
/AzureAuthenticationTenantId The authentication tenant. Y 
(If you want to get the latest prices)
/AzureAuthenticationClientId The client ID of the AAD app used for authentication. Y 
(If you want to get the latest prices)
/AzureAuthenticationInteractiveAuthentication Set to true to pop up the window. Y 
(If you want to get the latest prices) 
(Pick one of the 3 authentication options - option 1)
/AzureAuthenticationCertificateStoreLocation Set to the certificate store location (e.g. "CurrentUser"). Y 
(If you want to get the latest prices) 
(Pick one of the 3 authentication options - option 2)
/AzureAuthenticationCertificateThumbprint Set to the cert thumbprint. Y 
(If you want to get the latest prices) 
(Pick one of the 3 authentication options - option 2)
/AzureAuthenticationToken Set to the certificate token. Y 
(If you want to get the latest prices) 
(Pick one of the 3 authentication options - option 3)

Examples for SKU assessments using the CLI

Dmacmd.exe

Dmacmd.exe /? or DmaCmd.exe /help

Azure SQL DB SKU recommendation with price refresh (get latest prices)

.\DmaCmd.exe /Action=SkuRecommendation
/SkuRecommendationInputDataFilePath="C:\TestOut\out.csv"
/SkuRecommendationTsvOutputResultsFilePath="C:\TestOut\prices.tsv"
/SkuRecommendationJsonOutputResultsFilePath="C:\TestOut\prices.json"
/SkuRecommendationOutputResultsFilePath="C:\TestOut\prices.html"
/SkuRecommendationCurrencyCode=USD
/SkuRecommendationOfferName=MS-AZR-0044p
/SkuRecommendationRegionName=UKWest
/SkuRecommendationSubscriptionId=<Your Subscription Id>
/AzureAuthenticationInteractiveAuthentication=true
/AzureAuthenticationClientId=<Your AzureAuthenticationClientId>
/AzureAuthenticationTenantId=<Your AzureAuthenticationTenantId>

Azure SQL DB SKU recommendation without price refresh (use static prices)

.\DmaCmd.exe /Action=SkuRecommendation
/SkuRecommendationInputDataFilePath="C:\TestOut\out.csv"
/SkuRecommendationTsvOutputResultsFilePath="C:\TestOut\prices.tsv"
/SkuRecommendationJsonOutputResultsFilePath="C:\TestOut\prices.json"
/SkuRecommendationOutputResultsFilePath="C:\TestOut\prices.html"
/SkuRecommendationPreventPriceRefresh=true 

See also

Comments (8)

  1. Ramzay_I says:

    It appears as DMACMD.exe has some issues processing assessments, when compared with DMA.exe (UI version).
    Specifically, occasionally DMACMD would throw the following exception on a database, which is assessed without issues by DMA.exe.

    DmaCmd.exe Information: 0 : ‘Exception type: Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Workflow.Contracts.ActionExecutionException
    Message:
    An exception occurred while running the action ‘CollectMetadata’.
    HResult : 0x84C40001
    FacilityCode : 1220 (4c4)
    ErrorCode : 1 (0001)
    Data:
    Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Contracts.ErrorFailureCategory = UnexpectedFailure
    ActionName = CollectMetadata
    Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Contracts.ActionName = CollectMetadata
    Stack:
    at Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Workflow.Contracts.ComposableActivity.Execute(NativeActivityContext context)
    at System.Activities.NativeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
    at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
    Inner exception type: System.AggregateException
    Message:
    One or more errors occurred.
    HResult : 0x80131500
    FacilityCode : 19 (013)
    ErrorCode : 5376 (1500)
    Data:
    Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Contracts.ActionName = CollectMetadata
    Stack:
    at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
    at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
    at Microsoft.SqlServer.Advisor.Actions.CollectMetadataAction.Execute()
    at Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Workflow.Contracts.ComposableActivity.Execute(NativeActivityContext context)
    Inner exception type: Microsoft.SqlServer.Advisor.Core.CollectorDatabaseMissingException
    Message:
    Unable to collect metadata for database ‘PaperCut’ from server ‘DCSQLV03\IN03’.
    HResult : 0x853D0001
    FacilityCode : 1341 (53d)
    ErrorCode : 1 (0001)
    Data:
    Microsoft.SqlServer.Fundamentals.OperationsInfrastructure.Contracts.ErrorFailureCategory = SettingValidationFailure
    ServerName = MyServer
    DatabaseName = MyDatabase
    Stack:
    at Microsoft.SqlServer.Advisor.Core.Collectors.SqlMetadataCollector.d__e.MoveNext()
    — End of stack trace from previous location where exception was thrown —
    at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
    at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
    at Microsoft.SqlServer.Advisor.Actions.CollectMetadataAction.d__0.MoveNext()

    1. Harini Gupta says:

      Is this still an issue?

  2. Ramzay_I says:

    Are there any plans to have DMACMD support actual migrations? This would be very helpful!

    1. Harini Gupta says:

      Thanks for the query, yes, we do have backlogs. Coming up in the future…

  3. How does /AssessmentEvaluateFeatureParity differ from /AssessmentEvaluateRecommendations ?

    1. Hello Hiram Fleitas,
      AssessmentEvaluateFeatureParity: This option will discover unsupported and partially supported features that your applications using from your source SQL Servers migrating to a selected target environment for example Azure SQL DB. Also provide guidance around these areas and that may need a bit re-engineering effort to adopt to an alternative approach suggested on Azure. The focus is mainly surface level features in use outside of the databases, like SQL Agent jobs, SSIS packages etc…
      /AssessmentEvaluateRecommendations: It will discover the new target features that your application databases can start leveraging after migrated to target databases, example, new security features “Always encrypted”, “Dynamic masking” etc… Currently this option is only available for migrating to SQL Server but not enabled for Azure SQL Database targets yet.
      Please let us know if you have any further questions.
      Thanks
      Raj Pochiraju

  4. Som Tripathi says:

    DMA comes bundled with command line tool DmaCmd.exe. This is the best tool for assessment and analysis of any migration project and helps a lot in automating.

    I have few comments about dmacmd.exe that can be enhanced to make it perfect:
    1. /AssessmentDatabases parameter should have option to include for all user databases on the same server.

    2. When dmacmd.exe is executed from a different path, it throws errors for certain xml files not found. This can be corrected by changing path to the location using:
    cd “C:\Program Files\Microsoft Data Migration Assistant”
    But this logic can be added to dmacmd.exe itself or a relevant error message can be generated to user.

    3. The error messages or warnings are too long. If only relevant lines are added in output log, it will be helpful to find and correct the issue.

    4. At the end of execution, it tries to check update by connecting Internet. Checking update is not an issue, but the error message can be skipped. Or a parameter can be added to DmaCmd.exe to run offline. Most of production networks have no Internet connection due to obvious reasons.

    1. hello Som Tripathi,
      Thank you so much for all the feedback that you provided. Providing an option for all databases is definitely lot of other customers are also asking, we will definitely prioritize the feedback to enable the option. Can you please provide a speciifc example for long error messages that you pointed out? You can send email to DMAfeedback@microsoft.com

      Thanks again for all the feedback
      Raj Pochiraju

Skip to main content