When migrating on-premises SQL Server database to Azure SQL environment, identifying the right compute capacity of the target environment to match the resource consumption of the source database server is vital. In on-premises database compute resource size is based on CPU, RAM and Disk I/O. These resources are sized individually in order to fit the application requirement. However, when it comes to cloud resource in Azure, database resource is sized by Database Transaction Unit (DTU) and Virtual Core (vCore).
One of the primary issues you need to overcome when considering Azure migration is identifying the right database compute size for your on-premise database. So how do you convert CPU/RAM and I/O size into DTU and vCore?
Microsoft provides SKU Recommender utility as part of the Database Migration Assistant tool to address the traditional compute resource utilization to a compatible Azure resource. After installing DMA tool, look for SkuRecommendationDataCollectionScript.ps1 in the installation folder and running this powershell script requires Powershell version 5.1 and above as well as Azure Powershell module.
The SKU Recommender utility is based on resource utilization data collected using the below four windows performance counters over a period of time on a source database server. Once the data is collected, the utility also analysis the data and provides appropriate Azure SQL SKU (Compute and service level)
- Processor – % Processor Time
- Logical Disk – Disk Reads/sec
- Logical Disk – Disk Writes/sec
- Database – Log Bytes Flushed/sec
The SKU recommender script has four main parameters
- ComputerName – Source database server name
- OutputFilePath – Performance counter output folder
- CollectionTimeInSeconds – The duration of the data collection time in seconds
- DbConnectionString – Database connection string
Once the performance data is collected, it has to be analyzed for the appropriate SKU recommendation. This has one input file and three output files showing the recommended SKU in tsv, json and html file format. In addition to this there are multiple optional parameters you can use to make the recommendation closer to your need, this includes database, currency, region, subscription and authentication.
The html file output display a recommended SKU graphically including compute, service tier and associate cost. This recommendation is an estimate and need to be adjusted to fit the customer database requirement.
SKU recommender can be used to size Azure SQL database and Azure SQL Managed Instances.