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.
Similarly, you choose the instance type, hardware type, and instance configuration for Azure 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.
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.
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.
If you do not .Net Framework 4.8 installed, download the .Net framework 4.8 runtimes using the link.
Click on the link – Download .Net framework 4.8 runtimes, and it starts the installation as shown below.
The DMA installation is straightforward. You can follow the wizard and finish the wizard.
The default installation path is C:\Program Files\Microsoft Data Migration Assistant.
The SqlAssessmentConsole folder has an exe file – SqlAssessment.exe that we will use in this article.
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.
After the command executes, performance and configuration data are saved in three CSV files.
- Common DB level counters
- Common instance-level counters
- Performance aggregated counters
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.
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.
Example 3: Generate Azure SKU recommendation for 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
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023