Lösungen
Märkte
Referenzen
Services
Unternehmen
How to calculate the Azure SQL DTU demand of your on prem database

How to calculate the Azure SQL DTU demand of your on prem database

11. August 2019

How to calculate the Azure SQL DTU demand of your on prem database

One of the options to scale Azure SQL Databases is by DTU, database transaction unit (to get an overview and comparison of the purchasing models, check https://docs.microsoft.com/en-us/azure/sql-database/sql-database-purchase-models). But how do you know, how many of those DTUs you need for your on-prem workload? A very helpful tool for that is Azure SQL DTU Calculator, oftentimes wrongly attributed to Microsoft, while in fact it was created and is maintained by Justin Henriksen. By grabbing a couple of performance metrics and uploading the results, you can let it calculate the necessary DTU level for every data point and also present you with a recommendation and a couple of nice graphs. While that is already very good, you might want to do this repeatedly after capturing longer periods of SQL load or on multiple machines and you might want to do your own analysis on the base data. To achieve that, I have created a couple of scripts with the help of my colleagues Christoph Litters and Pascal Poletto.

The TL;DR

You can find the code here in the Axians code sharing platform. It works in four steps:

  1. Assuming that you have multiple .csv files generated by the scripts shared on the Azure SQL DTU Calculator homepage, the first script accumulates them into one big .csv
  2. With the second script you convert the .csv file to the .json format expected by the API provided by the calculator and also split it into chunks of 50.000 lines as the API has problems with large files
  3. The third script calls the API
  4. The last script interprets the results and gives you a short result overview: the recommended Azure SQL service tiers, the count how often they were recommended, the current price per hour in USD (read via a call to the same backend as the Azure pricing calculator1, the included and the max DB size in GB

The result, imported into Excel looks like this, with the percentage and cost per month easily calculated in Excel

With that you can do your own calculations depending on your needs

The details

The scripts actually are pretty straight forward, so only a couple of things to note:

  1. thanks to Pascal Poletto for sharing his scripts for that!
  2. especially Christoph Litters, as always thanks a lot for your help!