One of my clients asked to calculate the DTU’s required for an existing database before migrating to Azure SQL Database. Pricing Azure SQL Database is different from traditional SQL Server licensing. Azure SQL Database pricing depends on the database service tier options ,such as Database Transaction units and the maximum size of the database.
Azure SQL Database offers Basic, Standard, Premium, and Premium RS service tiers for both single databases and elastic pools. The cost of Azure SQL Database directly relates to which tier and performance level you are using. To determine service tier and performance level and database throughput units (DTUs) with the help of Azure database DTU calculator.
Requirements | Basic | Standard | Premium | Premium RS |
Target workload | Development and production | Development and production | Development and production | Workload that can tolerate data loss up to 5-minutes due to service failures |
Uptime SLA | 0.9999 | 0.9999 | 0.9999 | N/A while in preview |
Backup retention | 7 days | 35 days | 35 days | 35 days |
CPU | Low | Low, Medium, High | Medium, High | Medium |
IO throughput | Low | Medium | Order of magnitude higher than Standard | Same as Premium |
IO latency | Higher than Premium | Higher than Premium | Lower than Basic and Standard | Same as Premium |
Columnstore indexing and in-memory OLTP | N/A | N/A | Supported | Supported |
For more information on check this link: https://azure.microsoft.com/en-us/pricing/details/sql-database/
This calculator will help you determine the number of DTUs being used for your existing on-prem SQL Server database(s) as well as a recommendation of the minimum performance level and service tier that you need before you migrate to Azure SQL Database. It does this by using performance monitor counters.
How to Use the DTU Calculator
A ‘DTU’ is a ‘Database Throughput Unit’. It’s a calculation of the CPU and IO resource requirements for a given workload on a given server. The exact criteria used by the DTU Calculator to formulate a suggested Performance Level isn’t explained, but it’s calculated using the Performance Counters below:
- Processor – % Processor Time
- Logical Disk – Disk Reads/sec
- Logical Disk – Disk Writes/sec
- Database – Log Bytes Flushed/sec
I have SQL server 2014 instance with OLTP databases running on a virtual machine with 16 cores and 48 GB RAM. We have configured above parameters using Performance Monitor or using a PowerShell script (both attached in the Resources section). Here is how to did this.
Step 1
Create a new data collector set. Click the Windows Start menu and search for "performance". Select performance counters and save the data collector set. In the below screenshot, we have configured the perfmon counters using the perfmon tool.
Step 2
Run the data collector set. in the below screenshot, we can start the report to collect the data.
Step 3
Once the script executes, copy the .csv file generated by the script and upload the csv file to find the recommended DTU’s and size of Azure SQL Database for your workload using the DTU calculator. In the below screenshot, the CSV file is uploaded and number of cores for server is set to 16.
Step 4
Click Calculate to view recommended Service Tier/Performance Level and DTUs. In the below screenshot, it recommended that we migrate our SQL Server workload to a Standard S2 tier.
In the below screenshot, the database utilization has been converted to Azure SQL Database DTU’s for the duration of the workload.
The results will help you to choose the service tier for your workload. The Standard tier is more for your common workloads while Premium is designed for high transactional volume where I/O performance is much more important to you.
Ganapathi varma Chekuri
Lead SQL DBA, MCP
Linkedin
