Web Analytics Made Easy - Statcounter
HomeAzureAzure Pricing Models and Service Tiers

Azure Pricing Models and Service Tiers

April 24, 2018

When we start migrating our database from SQL Server to Azure Database, we want to know what are the pricing models are offered by Azure and how do we know the correct Pricing model/Service Tier suitable for our database. This article will going to discuss about the pricing models offered by Azure and helps in choosing the correct Service Tier as per our database workload need.

There are 2 Pricing models offered by Azure database. Since DTUs based pricing model is currently in use and the V-Core Based Pricing model is under preview, so in this article we are going to discuss about how DTUs based pricing model work in Azure in & going to cover the following topics:-

Exception handling in SQL Server
Trending
Exception handling in SQL Server

 1) DTUs based Pricing model
2) Service Tiers under DTUs based pricing model & its effect on Application Performance
2) What is DTU ?
3) calculating required DTUs using DTU Calculator

DTU Based Pricing model:-

What is DTU?

DTU or Database Transaction Unit can be defined as the blended measure of resources like memory, CPU, Data IO, Log IO guaranteed to be available to an Azure database at the specific performance level. It simply defined how powerful your Azure database is and used as the unit of comparison. Higher the DTUs higher the performance.
Example: –

For example, if 5 DTUs takes 100 seconds to executes 15 queries, then 100 DTUs will going to take 5 seconds time in solving these 15 queries.

Different Service Tiers in DTUs base pricing model:-

In DTUs base pricing model, Azure offers preconfigured bundle of computer resources & storage size for different level of application performance. Customers which like preconfigured bundle of resources & monthly payments, prefer DTUs base pricing model. In this pricing model, Azure database offers below mentioned 3 types of Service Tiers:-

Service Tiers in Azure - Basis, Standard & Premium
Service Tiers in Azure
We can differentiate these 3 service tiers based on the below mention characteristics:-
  • Size                       –  Size limit for an Azure Database
  • Performance     –  No. of DTUs available
  • Recovery             –  No. Of Days for performing Point In Time Recovery
  • Concurrency      –  No of Login attempts/Worker threads / Concurrent Sessions
  • Extra Features  –  No of Features available only in high-end services tiers like In-Memory OLTP & ColumnStore Indexes

ServiceTiers Characteristics - Summary

BasicStandardPremium
Maximum Storage Size2 GB1 TB4 TB
Maximum DTUs530004000
Point-In-Time Recovery7 days35 days35 days
Login Attempts3060 - 200200 - 6400
Worker Threads3060 - 200200 - 6400
Sessions300600 - 24002400 - 32000
In Memory OLTPN/AN/ASupported
Columnstore indexingN/AS3 and AboveSupported

Standard Service Tier Levels

With in Standard Service Tier, depending on the number of DTUs, we  have  various level starting from S0 to S12. It means higher the level, higher the DTUs and higher the performance

Standard Service Tier Levels

LevelsS0S1S2S3S4S6S7S9S12
DTUs10205010020040080016003000
In Addition, in case of Standard Service Tier, if our database size goes beyond 250 GB then we need to pay money for the extra storage space we occupied as shown in the below image
Standard Service Tier Size Limit
Standard Service Tier Size Limit

Premium Service Tier Levels

Also in Premium Service Tier, depending on the number of DTUs, we  have  various level starting from P1 to P15 which are mentioned below

Premium Service Tier Levels

LevelsP1P2P4P6P11P15
DTUs125250500100017504000
Size in GB50050050050040004000
Azure gives us the flexibility to easily move from lower service tier to higher service Tiers and from higher service tier to lower service tier at any point of time. Duration of the change in service tier will depend upon the database size. Change in the charges will only comes into effect once the change in the service tier got completed.
In Addition, Azure billed single database on the hourly basis based on the highest Service tier used in that hour.
Please Note– If you upgrade database to over 1 TB storage option, you will not be able to downgrade this database to 1 TB or below or to a performance tier below a P11. You will not be able to add this database to an elastic pool. Using restore, copy, or geo-replication will require over 1 TB storage option with a P11 or P15 performance tier.

 DTU Calculator

After we got understanding about the DTUs and Service tiers, next question comes how do we know the number of  DTUs needed when we migrate our SQL database to the Azure because it will finally going to help in choosing the correct Service tire. To help us, we have a DTU Calculator at http://dtucalculator.azurewebsites.net . This DTU Calculator has been developed by Justin Henriksen, Azure Solution Architect at Microsoft.

How DTU Calculator work: –

Set up trace at server - collect performance trace - upload trace file in DTU calculator - DTU calculator Analyzes trace to give estimation
DTU Calculator Work Flow

 As you have seen in the above work flow, to know the most accurate resource utilization of our database, we first need to capture below mentioned utilization metrics on our SQL Server. For capturing the correct performance metrics, we can use one of the utilities, Command Line EXE or PowerShell Script. These utilities can be downloaded from the DTU Calculator website.  We need to capture representative workload for getting the best result which may require to run this utilities to at least some hours to few days.

  • Processor – % Processor Time
  • Logical Disk – Disk Reads/sec
  • Logical Disk – Disk Writes/sec
  • Database – Log Bytes Flushed/sec

when we download the Command Line Utility and unzip it, we got below mentioned 2 files: –

 

Command Line Utility files
Command Line Utility files

Now if you open the config fie SqlDtuPerfmon.exe, you may need to take care of the below highlighted sections and change its value accordingly to your needs.

Config fie - SqlDtuPerfmon.exe. Changes may required for highlighted section
Config fie – SqlDtuPerfmon.exe
1) In the first highlighted section with key as “SqlCategory”, if you don’t change the value and execute the Utility as it is, it will work if your SQL server instance is the default one. If your SQL Server instance is the named instance then you need to change its Value. Also this utility work at the SQL instance level not at the database level.  For specific SQL database, you need to use the other DMVs for Capturing this data.
For example, suppose my SQL instance name is DEV2K16 then, the value as shown below

<add key=”SqlCategory” value=”MSSQL$DEV2K16:Databases”/>

2) In the second highlighted section, Value 3600 represents the number of seconds this utility will going to run for capturing the representative workload. By default it is 1 hour or 3600 Seconds. You can increase the value as per your requirement. But make sure that it ran for enough duration to capture the correct representative workload.

3) In the third highlighted section, value represents the location in the server where the CSV file containing the trace output going to save. Make sure the user has the required permission for writing the file in this location or you can change it to some other location.

Instructions on executing the utility file has been mentioned in the DTUcalculator website. Please see below screenshot containing these instruction, taken from the website http://dtucalculator.azurewebsites.net. You

Instruction for executing Utility files
Instruction for executing Utility files

When you execute the utility, you can see the output data in the command shell as shown below: –

Output data of executing Utility Files
Output data of executing Utility Files
Once you the Utility execution got completed, you need to upload the CSV file into the DTU Calculator website and click on the Calculate button.
After the analyzing the performance traces, the DTU Calculator gives the result as shown in the below mentioned screenshots
Service Tier/Performance Level
Service Tier/Performance Level
In the above screenshot, DTU calculator gives the general recommendation about the Service Tier. It is an interactive chart where you can move the mouse on its various section and see how much percentage a selected Service tier can cover. Here, when I move my cursor to S2 section, it shows that it will going to cover 96.97% of my workload.
DTUs Over Time
DTUs Over Time
If we scroll down, we see the above chart, where mostly DTUs are under 50 but there are some DTUs which are touching 125 mark. Here I run the utility for few minutes only for demo purpose. Due to this the Time values has the range of 0-1100 instead of 0-3600.
Below the chart, there is a link View More details. If we click on this link, it shows Service tier recommendation for different resources like CPU, Iops & Log as shown in below screenshots. All these charts are interactive where we can mouse over to different service tier sections and see how much utilization of resource this tier is going to cover 

"<yoastmark

"<yoastmark

"<yoastmark

Service Tier/Performance Level for Cpu, Iops, & Log
Service Tier/Performance Level for Cpu, Iops, & Log

As you seen in the above screenshot, you can move your mouse over on the chart to find out which Service Tier will cover most of the requirements. Here if we  mouse over to Standard-S2, we see that this Service Tier/Performance Level cover most of the requirement as  98.75 % of CPU, 98.04 % of Iops, and 100.00 % of Log utilization.

Conclusion: –

In previous article on Azure series, we talk about Azure in general and brief introduction of Service Tiers. In this article, we learns that are the 2 types of Price models Azure offers to its customers.

  1. DTUs Based Price model
  2. vCore Based Price model (Under Preview)

In this article, we discuss the DTUs Based Price model in details along with different Service tiers which comes under this price model. This article also gives information about the values levels of the Standard Service Tier & Premium Service Tier. Also this article gives the information about the resources Azure offers in these service tiers and their impact on the application performance.

After that we discuss what is DTU and how we can get the initial estimate of required DTUs to start with using the DTU Calculator. In addition to this we also got the information about how this DTU calculator work and how it gives the estimation of service tier we can use to start with.

In the next articles under the Azure series, we are going to explore more topics of Azure like Database migration from SQL Server to Azure, Security features etc. We can see all these Azure articles under the Azure page. Please don’t forget to gives your valuable comments for the article or you can send me the direct queries to my Email id askvivekjohari@gmail.com

 

 

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

Tags:, , , ,

Related Posts

About The Author

vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

32 Comments
  1. Avinash

    Great article and nicely explained

    Reply
  2. imbeliever

    Very good article. Easy to understand. I like the way you have explained things in such a lucid language. Keep it up.

    Reply

Leave a Reply

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading