Azure Pricing Demystified: Azure SQL

This is the inaugural entry of a series of explainers for Azure pricing, which
is one of the most confusing and daunting aspects of considering a move to the
cloud.

Azure SQL

This is your basic managed SQL Server in the cloud. You don’t have to deal with
backups, performance tuning, maintenance, replication, or scaling. These things
are all handled by Microsoft for a fee that is higher than running SQL Server
in a VM yourself.

Be aware that Azure SQL does not have complete feature parity with full SQL
Server and while that gap is getting smaller all the time, some complex
applications may have trouble porting.

How do I choose a tier that I need?

There are a number of levers you can pull in selecting a pricing tier to meet
your needs:

Throughput

Database size

Number of databases

Backup retention time

By considering these details you can make an informed decision on what tier you need
ranging from an economical $5 per month Basic database, to the absolutely insane
$16k per month Premium P15.

Throughput

The main driver in picking a tier is the required throughput. Pricing tiers are
broken down according to a made up metric called a DTU, or Database Transaction
Unit.

You can think of a DTU as “one query per second” where the query is of the sort
of size and shape of a typical web application query.

“Aha!” you might think. “I’ll just batch my updates into one enormous statement
that does 1000 inserts in one query!”

Nope! Remember its equivalent to a “typical” web application query, and actually
includes a blend of IO, RAM, and CPU used during processing. So while that giant
query might be a single transaction, it uses the IO of a 1000 queries and would
bump against the max throughput limits. Depending on the tier, that query would
take multiple seconds to complete.

So in general, you’ll want to consider how many queries per second your system
needs to support to meet user requirements. But if your applications queries are
heavier than a “typical” web application, you’ll be needing more scale.

Database size

Next up consider database sizes. This is the physical size of the database on
disk.

I’d expect the vast majority of real world applications will get by just fine in
the Standard level, which gives you up to 250GB. If you need more, you’ll need to
be on Premium.

Number of Databases

OK, so my system has 12 databases, and they have different throughput needs at
different times. Any one of them might spike up to 100 DTUs, but rarely at the
same time. Is there a way to share DTU across a bunch of databases?

Great question! And yes! It sounds like you’d be interested in a an elastic
database pool.

In the elastic pool offering, you choose a tier by considering the maximum DTUs a
single database will require, as well as the maximum DTUs the entire pool may
need. Throughput and disk usage are then shared across the pool, and individual
databases are allowed up to a maximum throughput.

The number of databases in the pool are also a factor, but are so large they
only make sense if you are running a SAAS where each of your customers has their
own physical database. Even the Basic plan in the elastic tier gives you 100
databases per pool, which is more than enough for most common scenarios.

Whoa, this is weird: The Standard 50 tier has 50 DTUs shared across the pool,
but allows 100 DTUs per database. Whats up with that? How can a single database
use more than the total allowed in the pool?

Another great question. Thanks for paying attention!

My understanding is that the shared DTUs in the pool is an average of
utilization across each database, but that the max DTU per database is a hard
limit at the database level. So for example, a single database in your pool
could spike up to 100 DTUs for a few moments, but the average usage across the
all the databases for that billing hour is still less than 50.

Backup Retention Time

Another thing to consider is the length of time you want backups to persist.

Each level offers Point in Time Recovery down to the millisecond, but how far
back you can recover depends on if you’re in Basic, Standard, or Premium.

Basic: 7 days

Standard: 14 days

Premium: 35 days

Choosing a service tier

Here are some questions you can ask yourself that can guide you into picking
the right tier:

Do I have more than 4-5 databases?

An elastic database pool may be a good choice for you.

How big are my databases?

For the single database offering, size on disk immediately buckets you into
Basic, Standard, or Premium.

Less than 2GB? You can use Basic. Less than 250GB? You can use Standard. Less
than 500GB? You can use Premium, up to P6. Less than 1TB? You’ll need at least a
P11 and $7k/mo.

Do I care about point in time recovery more than a week old?

Likewise, this can drive your decision into the Basic, Standard, or Premium
tiers

How much throughput do I need?

Once you’re bucketed into Basic, Standard, or Premium, you should consider how
active your database will be. At the Basic tier, 5 DTUs is fine for demo
applications and prototypes, but will struggle with anything more than a
development load.

Chatty applications like content management systems are going to want closer to
an S3. Batch job processing can also require additional horsepower to complete
in a reasonable time.

Once you get to about the middle of the Premium tier (and running bills in the
multiple thousands for a single database), I start to wonder if you’re not
better off running SQL Server in a VM. You’ll have to manage operating system
updates, software updates, backups, and tuning yourself, but you’re obviously
deeply invested in SQL Server technology and it would be prudent to have staff
skilled in those areas.

I’m a cheapskate, what else can I do?

So for example, I run multiple demo and hobby projects that all use the same
database, but each works under a schema unique to that application. Instead of
just using dbo, I have podcasts, gastracker, efh and other acronyms.

Another trick you could try is automated scaling.

If your database is mostly used for occasional reporting but has a huge spike
in throughput overnight when batch jobs run against it, you could use Azure
command line tools to automatically scale it up and down on a schedule. So
during the day it hangs out at a little S0, but at 11PM you have it scale up to
S3, and then back down when the jobs are finishes.

You only pay S3 rates for the hours it was scaled at S3. For some workloads this
makes a lot of sense.

Conclusion

I hope this helps you understand how Azure prices database tiers so you can make
a good decision. I know its pretty confusing and its annoying to have to figure
out all these concepts just to pick a tier.

I really like Azure SQL for most workloads, and would prefer it over running SQL
Server in a VM whenever possible. It’s nice to focus on your business and your
application instead of ancillary topics like operating system patches.