Azure Database Migration Assistant (DMA)

The DMA actually consists of 2 components. Details below are more around the PowerShell tool as the GUI assessment is very simple and only takes a few minutes to run.

1. Generating DB Load

This won’t be required in production as the database will have organic load, but in a lab, I had to simulate load.

In SQL Query Stress, I used the following query to generate load for the duration of the data collection (more info below). This basically generates a random number between 25 and 10 and uses that number to perform some updates on the database.

NOTE: When I used ‘read only’ queries, the SKU assessment tool did not provide different output to just running without load.

DECLARE @MyCounter INT;
SET @MyCounter = FLOOR(RAND()*(25-10+1))+10;
SELECT * from dbo.Comments where Score = @MyCounter
UPDATE dbo.Comments SET Text = 'GB_Updated + MyCounter' WHERE Score = @MyCounter

2.Collecting Metrics

  • Before proceeding, make sure to install the DMA tool on a workstation where you will be collecting metrics. Don’t do this on the SQL server itself.
  • While the tool above is running, open PowerShell in ‘C:\Program Files\Microsoft Data Migration Assistant’ and execute the command below.
  • Adjust the connection string as needed, but note that the catalog has to point to the ‘master’ database.
  • The minimum collection interval is 120 seconds.
  • The tool analyses all DBs.
.\SkuRecommendationDataCollectionScript.ps1 -ComputerName citrix1 -OutputFilePath C:\temp\counters.csv -CollectionTimeInSeconds 120 -DbConnectionString "Server=citrix1;Initial Catalog=master;Integrated Security=SSPI;"

When completed, the CSV file will look as below.

3. Analysing SKU

  • This done using a PS script included with the DMA tool.
  • You can specify the database to analyse.
  • You need to specify the 3 different output locations as per below, but they can all point to the same folder.
  • The folder has to already exist.
.\DmaCmd.exe /Action=SkuRecommendation /SkuRecommendationInputDataFilePath="C:\temp\counters.csv" /SkuRecommendationTsvOutputResultsFilePath="C:\temp\2\prices.tsv" /SkuRecommendationJsonOutputResultsFilePath="C:\temp\2\prices.json" /SkuRecommendationOutputResultsFilePath="C:\temp\2\prices.html" /SkuRecommendationPreventPriceRefresh=true /SkuRecommendationCurrencyCode=NZD /SkuRecommendationRegionName=australiaeast /SkuRecommendationDatabasesToRecommend="StackOverflow2010"

The folder output looks as below.

SQL DB HTML files below, with the 250 DTU being recommended for the counters which were collected while the stress tool was running.

No Load

Generated Load