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
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