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.
- Compatibility Assessment (GUI Tool) – https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15
- SKU Assessment (PowerShell Tool) – https://docs.microsoft.com/en-us/sql/dma/dma-sku-recommend-sql-db?view=sql-server-ver15
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.
- Generating Load – The tool I used SQLQueryStress (https://github.com/ErikEJ/SqlQueryStress). Do not run this on the SQL server itself.
- Test Database – Sample databases are normally too simple, so for this I used the 10 GB Stack Overflow Database as of 2010 (https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/).
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
- 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.