This is a part of a Series “Stairway to being an Azure SQL DBA“, in which I am planning to cover everything related to working as an Azure SQL DBA.
When creating an Azure SQL Database, which as you might already know is a PaaS service, (if you don’t know what it is I’ll suggest checking my previous blog SQL IaaS vs PaaS) and that too the one in which you just create a database on some SQL Server about which you don’t have any idea 🙂 weird for a DBA, I know…
When you are creating such database you also need to provide the resources that you wish to allocate to this database. The costing, performance and maximum size of the database depends upon it.
There are two models available when creating an Azure SQL Database database:
DTU is a blend of CPU, Memory, Reads and Writes and a database having 5 DTUs will perform 5 times better than another database having just 1 DTU.
Even in DTU Azure provides the flexibility of choosing different service tiers which are Basic, Standard and Premium. Based on the Tier you have chosen you will not just have the option of having a bigger database with more DTU but it also comes with better underlying storage and more backup retention period.
|Target workload||Development and production||Development and production||Development and production|
|Maximum backup retention||7 days||35 days||35 days|
|CPU||Low||Low, Medium, High||Medium, High|
|IO throughput (approximate)||1-5 IOPS per DTU||1-5 IOPS per DTU||25 IOPS per DTU|
|IO latency (approximate)||5 ms (read), 10 ms (write)||5 ms (read), 10 ms (write)||2 ms (read/write)|
|Columnstore indexing||N/A||S3 and above||Supported|
|Maximum storage size||2 GB||1 TB||4 TB|
There some more resource limitations tied along with different Service Tiers related to size, concurrent connection, requests and more. To know about that check the ms-docs for more detail.
If you need to find DTU equivalent for your database you can use Azure SQL Database DTU Calculator.
For the maximum Tempdb size possible with each service tier check here.
It is a more robust and feels similar to the on-prem environment. Here you get the option to choose the Cores (vCore here as it is “virtual” core) and also the size of the database.
Based upon the cores that you have chosen changes the maximum size of the database that you can have, eg if having 2 vCores you can have a database of maximum size of 1 TB only but if you increase the vCores to 10 you can have a database of maximum 1.5 TB size.
So the thing to consider here is that assuming you have a reporting database which is not that much used but is of size 1.5 TB than you don’t have any other option but to opt for 10 vCores. The costing of the databases is per vCore and Storage selected. So in such case you don’t have any other option but to pay more for the vCores that you are never going to use fully.
To help out in such scenarios Azure also has the Serverless option which provides the auto-scaling and is billed per second and this database can also be switched off.
So in our scenario of 1.5 TB reporting database we can choose the Serverless model with auto-scale option with max 6 vCore and min 0.75 vCores.
In vCores also we have different Service Tiers which are General Purpose, HyperScale and Business Critical.
Every scale has it own limitations and when creating an Azure SQL Database make sure to have a proper understanding of the requirement and have the model chosen appropriately. You can also change the service tier anytime (except when it’s in HyperScale) with minimum downtime.
To know about the resource limitations per purchasing model check this ms-doc page.
My take onto DTU vs vCore when to choose one over the other.
Having knowing everything doesn’t make it any easy when to choose DTU or vCore, so just providing the way I choose one over other.
- If I am creating a new database for doing a POC, I choose DTU the reason being you can have the cheapest Azure SQL Database in this which 5$/month for a 2 GB database.
- If I am creating a new database which is required for a application about which the requirements are not clear, I again create one in DTU model (generally Standard).
- If I am creating a new database which will be use heavily in terms of IO and have very low latency requirement I choose Premium or Business Critical.
- If I am migrating a database from On-Prem and it’s production database with decent load on on-prem I choose vCore model.
- If I am migrating/creating a new database where I know the size is going to be more then 4 TB in near future (or already is) I choose vCore Hyperscale offering.
- If I need a database which is either used by developers for some fixed duration per day I choose vCore Serverless model with Auto Pause enabled.
- If I need a database which has very uneven usage, I choose vCore Serverless without Auto Pause enabled.
The important thing is you can always change the tier from one to another (except once moved to Hyperscale)