Rajendra Gupta
Status of data migration assistant tool

How to identify suitable SKUs for Azure SQL Database, Managed Instance (MI), or SQL Server on Azure VM

October 28, 2022 by

The database migration from on-premises to Azure cloud can be complicated in choosing the best SKU for Azure SQL Database, Managed Instance (MI), or SQL Server on Azure VM. Every database might have different resource requirements, and we need to choose the correct DB sizing based on the current workloads.

As a customer using Microsoft SQL databases, you may wonder what the right purchase model and service tier will be for both your database, transaction load, and available resources.

You can calculate the estimated cost using the Azure pricing calculator. It requires the user input such as service tier, compute tier, hardware type, number of vCores.

Azure SQL Database

Similarly, you choose the instance type, hardware type, and instance configuration for Azure Managed instance.

Azure SQL Managed Instance

If you allocate a high number of resources for Azure databases, it might cause you a high recurring cost. Similarly, if you allocate fewer resources for Azure DBs, you might save cost, but the database performance might hamper. How do we tackle these issues? Let’s find out in this article.

Compare cost for different instance

An overview of Data Migration Assistant (DMA)

The Data Migration Assistant helps you upgrade your technology by detecting compatibility issues impacting your database’s functionality. It recommends improvements regarding performance and reliability for the target environment, and it moves data, schema, objects, and the like from a source server to the target server.

The Database Migration Assistant(DMA) can provide SKU recommendations using the performance data points. These SKU recommendations can help identify the minimum SKU and recommendations related to computing, pricing tier, and max data size.

Note: The SKU recommendation is currently available using the command-line interface (CLI).

Configure the Data Migration Assistant

Download and install the latest version of Microsoft® Data Migration Assistant v5.5.

Data Migration Assistant (DMA)

You can install the DMA on a separate server that can access the database on the source server. It’s common practice to utilize a “jump box.” Setting up this added layer of security can protect you and prevent any potential intrusion in your existing system by bad actors.

The DMA requires a .NET framework 4.8 installed in your system.

Net Framework 4.8

If you do not .Net Framework 4.8 installed, download the .Net framework 4.8 runtimes using the link. Download Net Framework 4.8

Click on the link – Download .Net framework 4.8 runtimes, and it starts the installation as shown below.

.net framework setup

The DMA installation is straightforward. You can follow the wizard and finish the wizard.

Status of data migration assistant tool

complete setup of data migration assistant tool

The default installation path is C:\Program Files\Microsoft Data Migration Assistant.

View SQL assessment

The SqlAssessmentConsole folder has an exe file – SqlAssessment.exe that we will use in this article.

View path of SqlAssessment.exe

Collect baseline for the on-premises SQL Server instance

After installing the data migration assistant tool, we need to collect a baseline for assessment for migrating to Azure SQL Database. The collected data includes hardware configuration, SQL performance data points such as CPU, Memory, Storage, and IO throughput. The collected data can be examined for Azure SQL Database, Managed Instance(MI), or SQL Server on Azure VM.

The following script starts the data collection with the default configuration.

.\SqlAssessment.exe PerfDataCollection –sqlConnectionStrings “Data Source=Server1;Initial Catalog=master;Integrated Security=True;” –outputFolder C:\Output

The script can include the following arguments:

  • sqlConnectionStrings: Specify the target SQL instance connection string. It is a mandatory parameter.
  • perfQueryIntervalInSec(optional): We can specify the interval to query performance data. The default value is 30 seconds.
  • staticQueryIntervalInSec(optional): It is the interval at which to query and persist static configuration data. The default value is 60 seconds.
  • numberOfIterations(optional): It refers to a number of iterations of performance data collection before persisting to the output file. The default value is 20 iterations.
  • outputFolder(optional): It is the output directory. The default value is %LocalAppData%/Microsoft/SqlAssessmentConsole.

It starts the data collection, and as per default configuration, it has 20 data query iterations.

Collect baseline

After the command executes, performance and configuration data are saved in three CSV files.

  1. Common DB level counters
  2. Common instance-level counters
  3. Performance aggregated counters

View output files


We will use these files input for the next phase of the process, which provides SKU recommendations for Azure SQL Database, Managed Instance (MI), or SQL Server on Azure VM.

Generate Azure SKU recommendation

Once we have collected data for sufficient time, you can generate SKU recommendations. You can specify the target environment such as Azure SQL Database, Managed Instance (MI), or SQL Server on Azure VM.

  • Azure SQL Database generates recommendations for a single database tier with the compute level and recommended storage.
  • Azure SQL Managed Instance and SQL Server on Azure VM: The recommendations are for lift and shift scenarios. If the on-prem SQL instance has many databases, it provides the feasibility to exclude a set of databases from SKU recommendations.

To generate the SKU recommendation, we need to specify the parameter – GetSkuRecommendation. The process uses a default baseline strategy for mapping the performance data to the right Azure SKU. This default strategy is based on the percentile value. It also has an elastic strategy that generates a unique price to performance curve based on data collection. It can also analyze the workload pattern with the customer already migrated to the Azure database platform.

Let’s generate the recommendations using the following examples.

Example 1: Generate Azure SKU recommendation for Azure SQL Database

.\SqlAssessment.exe GetSkuRecommendation –outputFolder C:\Temp\Output –targetPlatform AzureSqlDatabase

As shown below, the SKU recommendation is for individual database migration to Azure SQL Database. For example, it has separate recommendations for [AdventureWorks2019] and [ImportDB].

My demo environment recommends General Purpose compute with 2 vCores and 1 GB storage. You also get the recommendation reason for why the data migration tool generated these SKU recommendations.

Recommendation reason: According to the performance data collected, we estimate that your SQL Server database has a requirement for 0.00 vCores of CPU. For greater flexibility, based on your scaling factor of 100.00%, we are making a recommendation based on 0.00 vCores. Based on all the other factors, including memory, storage, and IO, this is the smallest compute sizing that will satisfy all of your needs. This SQL Server database requires 0.00 GB of memory. This SQL Server database requires 0.26 GB of storage for data files. We recommend provisioning 1 GB of storage, which is the closest valid amount that can be provisioned that meets your requirement. This SQL Server database requires 0.00 MB/second of combined read/write IO throughput. This is a relatively idle database, so IO latency is not considered.

Assuming the database uses the Full Recovery Model, this SQL Server Database requires 0 IOPS for data files. This is the most cost-efficient offering among all the performance-eligible SKUs.

Generate Azure SKU recommendation Azure SQL Database

Example 2: Generate Azure SKU recommendation for Azure SQL Managed Instance

.\SqlAssessment.exe GetSkuRecommendation –outputFolder C:\Temp\Output –targetPlatform AzureSqlManagedInstance–elasticStrategy true

If you need to migrate on Azure Managed Instance, the generated SKU recommendation is as below.

  • Compute: General Purpose – 4 cores
  • Storage: 32 GB
  • Recommendation reason: Based on the performance counters for your CPU, memory, storage, IOPS, and IO latency that were collected between 12/7/2021 7:56:12 AM and 12/7/2021 9:26:14 AM, the optimal AzureSqlManagedInstance SKU that we recommend you migrate to is: SQLMI_GP_Gen5_4. This SKU satisfies 100% of your current utilization needs with a 6.78x buffer on your current CPU usage.

migrate on Azure Managed Instance

Example 3: Generate Azure SKU recommendation for SQL Server on Azure VM

SQL Server on Azure VM

SKU recommendation: SQL Server on Azure VM:

.\SqlAssessment.exe GetSkuRecommendation –outputFolder C:\Temp\Output –targetPlatform AzureSqlVirtualMachine

  • Compute: standardEASv4Family – E2as_v4 – 2 Available vCPU
  • Data storage: Tier: Premium, Size: P30, Caching: ReadOnly
  • Log storage: Tier: Premium, Size: P40, Caching: None
  • TempDB storage:Use local SSD

Recommendation reasons: This Virtual Machine series uses the latest generation of hardware. According to the performance data collected, we estimate that your SQL Server instance has a requirement for 0.59 vCores of CPU. For greater flexibility, based on your scaling factor of 100.00%, we are making a recommendation based on 0.59 vCores. Based on all the other factors, including memory, storage, and IO, this is the smallest compute sizing that will satisfy all of your needs.

This SQL Server instance requires 0.09 GB of memory, which is within this SKU’s limit of 16.00 GB. This Virtual Machine size’s Memory-to-vCore ratio of 8.0 is greater than or equal to the recommended value of 8.0.

This Virtual Machine size supports Premium SSD-managed disks. Assuming the database uses the Full Recovery Model, this SQL Server instance requires 5,651 IOPS for data and log files. This SQL Server instance’s IOPS requirement is within the Virtual Machine Size’s combined (uncached and cached) IOPS limit of 7200. This SQL Server instance’s IO throughput requirement of 3.27 MBps is within the Virtual Machine’s uncached limit of 48.00 MBps.

This SQL Server instance’s Data storage requirement of 0.40 GB will fit on 1 P30 disk(s), which can hold 1024 GB each. We recommend provisioning dedicated Log disk(s). This SQL Server instance’s Log storage requirement of 0.34 GB will fit on 1 P40 disk(s), which can hold 2048 GB each. This Virtual Machine size supports temporary storage of up to 32.00 GB, which is large enough to hold your 0.02 GB TempDB, so no separate disk is needed. This is the most cost-efficient offering among all the performance-eligible SKUs.

Example 4: Generating SKU recommendations for a specific database

We might not want to move all on-premises databases on an instance to the Azure cloud. Therefore, we can include a specific database using the parameter (–databaseAllowList).

SqlAssessment.exe GetSkuRecommendation –outputFolder C:\Temp\Output –targetPlatform AzureSqlVirtualMachine –databaseAllowList AdventureWorks2019

SKU recommendations for a specific database

Conclusion

This article explored the Data Migration Assistant tool to identify appropriate SKUs for migrating databases to Azure SQL Database, Managed Instance, or SQL Server on Azure VM.

Rajendra Gupta
168 Views