There are two core models when creating Azure SQL database as PaaS service; DTU and vCore.
DTU is a blend of CPU, Memory, Reads and Writes. A database with 5 DTU will perform better than a database with 1 DTU.
vCore is more robust and feels similar to the on-prem environments. Here you get the option of choosing the Cores.
Databases have varying requirements depending on the workload. Microsoft offers three different instance options;
Single Database deployment
The hosting option create a single database deployment, with dedicated management via an SQL Database server. Being Single, each database is fully isolated and portable across Azure platform.
Single database can also be moved in and out of “elastic pool”, allowing for better resource distribution with multiple database instances.
This instance uses DTU purchasing model for billing. A DTU is the convergence of vCores, RAM and IOPS into a standardized measure for benchmarking and billing database instances. They can be used to figure out the cost by using DTU calculator.
Single instances are best suited to businesses running applications that require a resource guarantee at the database level.
Elastic pool
An elastic pool offers a convenient, cost-effective option for maintaining multiple databases. With multiple databases, there is some unpredictability with how much computational power is needed. For this reason, pooled resources can offer better performance, and value for money.
There are four service tiers but I just looked at two; Serverless and DTU.
Serverless
The serverless compute tier for single database is an autoscaling and auto pause delay service. The cost is summation of compute and storage.
Auto pausing is trigger, if number of sessions = 0 or CPU = 0 for use workload running in the user pool. Auto resuming is triggered when a user login. Auto pausing delay could be 1 hour, max is 7 days.
This is only supported in the General-Purpose tier on Gen5 hardware in vCore purchasing model.
This tier is price-performance optimized with intermittent, unpredictable usage pattern that can afford some delay in compute warm-up after idle usage periods.
DTU (Database Transaction Units)
The most common one is a Single database option. 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.. When selecting DTU, A normal conversation between Developer and IT Pro is;
Developer = I’d like a database server
IT Pro = ok, how much CPU do you need?
Developer = average…whatever the normal is
IT Pro = uh…ok, how many IOPS do you need?
Developer = what’s IOPS?
At the end, either too many or too few resources are provided and no one is happy. DTU take the metrics that determine the performance of a database and mush them together in a measure that we can use to abstract and compare performance.
What should I choose for dev/test?
We may be able to run our dev\test database on the Basic tier (5DTU $5/month) or the Standard S0 tier (10DTU $15/month), or maybe it would make more sense to put them all in a 50DTU elastic pool ($112/month). For production we’ll probably start out with a Standard S3 for our main DB and a Standard S0 or S1 for our auditing DB. Then depending on the loads scale them back or possibly put them in an Elastic Pool together.
The Basic tier is incredibly limited. It’s good for occasional/casual use, and it’s a cheap way to “park” your database when you aren’t using it. But if you’re running any real application, the Basic tier isn’t going to work for you.
The Standard Tier is pretty limited, too, but for small applications it’s capable of meeting your needs. If you have a 2-core server running a handful of databases, then those databases individually might fit into the Standard tier. Similarly, if you have a server with only one database, running 1 CPU core at 100% (or 2 cores running at 50%), it is probably just enough horsepower to tip the scale into the Premium-P1 service tier.
What is DTU? This simple way to understand DTU is;
When we build a SQL server box, we go with CPU count, some amount of RAM, storage configuration for enough IOPS for workload. When you jump to Azure, it’s call DTU. DTU is a blend of CPU, Memory, and Storage (Reads and Writes). A database with 5 DTU will perform better than a database with 1 DTU.
IOPS = Input/Output operations per second (IOPS)
Resources
https://dtucalculator.azurewebsites.net/
https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview