Run Data Migration Assistant from the command line
With version 2.1 and above, when you install Data Migration Assistant, it will also install dmacmd.exe in %ProgramFiles%\Microsoft Data Migration Assistant\. Use dmacmd.exe to assess your databases in an unattended mode, and output the result to JSON or CSV file. This method is especially useful when assessing several databases or huge databases.
Note
Dmacmd.exe supports running assessments only. Migrations are not supported at this time.
Assessments using the Command Line Interface (CLI)
DmaCmd.exe /AssessmentName="string"
/AssessmentDatabases="connectionString1" \["connectionString2"\]
\[/AssessmentSourcePlatform="SourcePlatform"]
\[/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 |
/AssessmentSourcePlatform |
Source platform for the assessment: Supported values for Assessment: SqlOnPrem, RdsSqlServer (default) Supported values for Target Readiness Assessment: SqlOnPrem, RdsSqlServer (default), Cassandra (preview) |
N |
/AssessmentTargetPlatform |
Target platform for the assessment: Supported values for Assessment: AzureSqlDatabase, ManagedSqlServer, SqlServer2012, SqlServer2014, SqlServer2016, SqlServerLinux2017 and SqlServerWindows2017 (default) Supported values for Target Readiness Assessment: ManagedSqlServer (default), CosmosDB (preview) |
N |
/AssessmentEvaluateFeatureParity |
Run feature parity rules. If source platform is RdsSqlServer, feature parity evaluation is not supported for target platform AzureSqlDatabase | N |
/AssessmentEvaluateCompatibilityIssues |
Run compatibility rules | Y (Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateRecommendations is required.) |
/AssessmentEvaluateRecommendations |
Run feature recommendations | Y (Either AssessmentEvaluateCompatibilityIssues or AssessmentEvaluateRecommendations is 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) |
/Action |
Use SkuRecommendation to get SKU recommendations, use AssessTargetReadiness to perform target readiness assessment. | N |
/SourceConnections |
Space delimited list of connection strings. Database name (Initial Catalog) is optional. If no database name is provided, then all databases on the source are assessed. | Y (Required if Action is 'AssessTargetReadiness') |
/TargetReadinessConfiguration |
Full path to the XML file describing values for the name, source connections and result file. | Y (Either TargetReadinessConfiguration or SourceConnections is required) |
/FeatureDiscoveryReportJson |
Path to the feature discovery JSON report. If this file is generated, then it can be used to run target readiness assessment again without connecting to source. | N |
/ImportFeatureDiscoveryReportJson |
Path to the feature discovery JSON report created earlier. Instead of source connections, this file will be used. | N |
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"
Single-database Target Readiness assessment using Windows authentication
DmaCmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Initial Catalog=DatabaseName;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"
Single-database Target Readiness assessment using SQL Server authentication
DmaCmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Initial Catalog=DatabaseName;User Id=myUsername;Password=myPassword;" /AssessmentEvaluateRecommendations
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"
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"
/AssessmentSourcePlatform="SqlOnPrem"
/AssessmentTargetPlatform="AzureSqlDatabase"
/AssessmentEvaluateCompatibilityIssues /AssessmentEvaluateFeatureParity
/AssessmentOverwriteResult
/AssessmentResultCsv="C:\\temp\\AssessmentReport.csv"
/AssessmentResultJson="C:\\temp\\AssessmentReport.json"
Multiple-database Target Readiness assessment
DmaCmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/AssessmentSourcePlatform=SourcePlatform
/AssessmentTargetPlatform=TargetPlatform
/SourceConnections="Server=SQLServerInstanceName1;Initial Catalog=DatabaseName1;Integrated Security=true" "Server=SQLServerInstanceName1;Initial Catalog=DatabaseName2;Integrated Security=true" "Server=SQLServerInstanceName2;Initial Catalog=DatabaseName3;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\Results\test2016.json"
(/AssessmentSourcePlatform and /AssessmentTargetPlatform are optional.)
Target Readiness assessment for all databases on a server using Windows authentication
DmaCmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/SourceConnections="Server=SQLServerInstanceName;Integrated Security=true"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"
Target Readiness assessment by importing feature discovery report created earlier
DmaCmd.exe /Action=AssessTargetReadiness
/AssessmentName="TestAssessment"
/ImportFeatureDiscoveryReportJson="c:\temp\feature_report.json"
/AssessmentOverwriteResult
/AssessmentResultJson="C:\temp\Results\AssessmentReport.json"
Target Readiness assessment by providing configuration file
DmaCmd.exe /Action=AssessTargetReadiness
/TargetReadinessConfiguration=.\Config.xml
Configuration file contents when using source connections:
<?xml version="1.0" encoding="utf-8" ?>
<TargetReadinessConfiguration xmlns="https://microsoft.com/schemas/SqlServer/Advisor/TargetReadinessConfiguration">
<AssessmentName>name</AssessmentName>
<SourcePlatform>Source Platform</SourcePlatform> <!-- Optional. The default is SqlOnPrem -->
<TargetPlatform>TargetPlatform</TargetPlatform> <!-- Optional. The default is ManagedSqlServer -->
<SourceConnections>
<SourceConnection>connection string 1</SourceConnection>
<SourceConnection>connection string 2</SourceConnection>
<!-- ... -->
<SourceConnection>connection string n</SourceConnection>
</SourceConnections>
<AssessmentResultJson>path\to\file.json</AssessmentResultJson>
<FeatureDiscoveryReportJson>path\to\featurediscoveryreport.json</FeatureDiscoveryReportJson>
<OverwriteResult>true</OverwriteResult> <!-- or false -->
</TargetReadinessConfiguration>
Configuration file contents when importing feature discovery report:
<TargetReadinessConfiguration xmlns="https://microsoft.com/schemas/SqlServer/Advisor/TargetReadinessConfiguration">
<AssessmentName>name</AssessmentName>
<ImportFeatureDiscoveryReportJson>path\to\featurediscoveryfile.json</ImportFeatureDiscoveryReportJson>
<AssessmentResultJson>path\to\resultfile.json</AssessmentResultJson>
<OverwriteResult>true</OverwriteResult><!-- or false -->
</TargetReadinessConfiguration>
Azure SQL Database/managed instance SKU recommendations using the CLI
These commands support recommendations for both Azure SQL Database single database and managed instance deployment options.
.\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 (Requires either TSV or JSON or HTML file path) |
/SkuRecommendationJsonOutputResultsFilePath |
Full path to the JSON result file | Y (Requires either TSV or JSON or HTML file path) |
/SkuRecommendationHtmlResultsFilePath |
Full path to the HTML result file | Y (Requires either TSV or JSON or HTML file path) |
/SkuRecommendationPreventPriceRefresh |
Prevents the price refresh from occurring. Use if running in offline mode (e.g., true). | Y (Select either this argument for static prices or all arguments below need to be selected to get the latest prices) |
/SkuRecommendationCurrencyCode |
The currency in which to display prices (e.g. "USD") | Y (For the latest prices) |
/SkuRecommendationOfferName |
The offer name (e.g. "MS-AZR-0003P"). For more information, see the Microsoft Azure Offer Details page. | Y (For the latest prices) |
/SkuRecommendationRegionName |
The region name (e.g. "WestUS") | Y (For the latest prices) |
/SkuRecommendationSubscriptionId |
The subscription ID. | Y (For the latest prices) |
/SkuRecommendationDatabasesToRecommend |
Space-separated list of databases to recommend for (e.g. “Database1” “Database2” “Database3”). Names are case-sensitive and must be surrounded by double-quotes. If omitted, recommendations are provided for all databases. | N |
/AzureAuthenticationTenantId |
The authentication tenant. | Y (For the latest prices) |
/AzureAuthenticationClientId |
The client ID of the AAD app used for authentication. | Y (For the latest prices) |
/AzureAuthenticationInteractiveAuthentication |
Set to true to pop up the window. | Y (For the latest prices) (Pick one of the 3 authentication options - option 1) |
/AzureAuthenticationCertificateStoreLocation |
Set to the certificate store location (e.g. "CurrentUser"). | Y (For the latest prices) (Pick one of the 3 authentication options - option 2) |
/AzureAuthenticationCertificateThumbprint |
Set to the cert thumbprint. | Y (For the latest prices) (Pick one of the 3 authentication options - option 2) |
/AzureAuthenticationToken |
Set to the certificate token. | Y (For the latest prices) (Pick one of the 3 authentication options - option 3) |
Examples of SKU assessments using the CLI
Dmacmd.exe
Dmacmd.exe /? or DmaCmd.exe /help
Azure SQL DB/MI SKU recommendation with price refresh (get latest prices) - Interactive authentication
.\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>
/AzureAuthenticationClientId=<Your AzureAuthenticationClientId>
/AzureAuthenticationTenantId=<Your AzureAuthenticationTenantId>
/AzureAuthenticationInteractiveAuthentication=true
Azure SQL DB/MI SKU recommendation with price refresh (get latest prices) - Certificate authentication
.\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>
/AzureAuthenticationClientId=<Your AzureAuthenticationClientId>
/AzureAuthenticationTenantId=<Your AzureAuthenticationTenantId>
/AzureAuthenticationCertificateStoreLocation=<Your Certificate Store Location>
/AzureAuthenticationCertificateThumbprint=<Your Certificate Thumbprint>
Azure SQL DB SKU/MI recommendation with price refresh (get latest prices) - Token authentication and specify databases to recommend
.\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
/SkuRecommendationDatabasesToRecommend=“TPCDS1G,EDW_3G,TPCDS10G”
/SkuRecommendationSubscriptionId=<Your Subscription Id>
/AzureAuthenticationClientId=<Your AzureAuthenticationClientId>
/AzureAuthenticationTenantId=<Your AzureAuthenticationTenantId>
/AzureAuthenticationToken=<Your Authentication Token>
Azure SQL DB/MI 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
- Data Migration Assistant download.
- The article Identify the right Azure SQL Database SKU for your on-premises database.