Setting up an Azure SQL Database

From my upcoming MCSA exam i’m currently learning the basics of setting up an Azure SQL database. This has some differences with the on-premise version, for example you have to choose a service tier in which to operate your database.

Choosing your service tier

In contrast with on-premise databases, you don’t choose a SQL version based on how many CPUs you will be using, the amount of RAM you estimate will be consumed or storage factors. Microsoft Azure gives the user the option to choose from four different service tiers. Most of the time you will be initially choosing a low tier and scaling up when the requirement arises.These are the four service tiers:

  • Basic – Choose this for small databases in early stages of development, limited to 2GB in size and low usage expectations for CPU and RAM.
  • Standard – Choose the standard tier when you need moderate performance and can tolerate some possible latency, so not very well suited for high-available OLTP databases. 250GB is the size limit.
  • Premium – Choose premium if you want your database to respond as fast as you can type. This tier offers the lowest latency with high possible throughput. The high number of performance options (IO intensive workloads, parallelism gives you a stable database with up to 4TB in storage included.
  • Premium-RS – This service tier strikes me as an Develop/Test/Acceptance environment of a Premium database. The only difference between this and premium is that Premium-RS does not feature high availability. Another use case is a non-critical analytical application.

Azure databases use ‘Database Transaction Units’ or DTU’s. These units are used when choosing an elastic pool. DTU’s are used to calculate the performance cost of a specific database. The importance of DTU’s looks to be less then it was before so this type of measurement might be replaced in the future.

The standard tier

Max Database size2 GB
Max concurrent workers (requests simultaneous)30 users/ queries
Max concurrent logins30
Max concurrent logins300
Performance TiersS0S1S2S3
MAX DTU’s102050100
Max Database Size250GB250GB250GB1TB
Max Concurrent Workers6090120200
Max Concurrent Logins6090120200
Max Concurrent Sessions60090012002400

Because Azure is still in development and people have different needs, Azure decided that it would be best if everyone had another 5 extra tiers to choose from:

Performance LevelS4S6S7S9S12
Max DTU’s20040080016003000
Max Database Storage1TB1TB1TB1TB1TB
Max Concurrent Workers400800160032006000
Max Concurrent Logins400800160032006000
Max Concurrent Sessions48009600192003000030000

The premium tier

The premium tier offers better (and more expensive) performance tiers that start with a ‘P’ instead of ‘S’. You can find these values below:

Performance LevelP1P2P4P6P11P15
Max DTU’s125250500100017504000
Max Database Size500GB500GB500GB500GB4TB4TB
Max in-memory OLTP Storage1GB2GB4GB8GB14GB32GB
Max Concurrent Workers200400800160024006400
Max Concurrent Sessions300003000030000300003000030000

When you choose a tier, set up a database and start using that database you might notice bad query performance. This slowdown means that your database uses more resources then was allocated and throttles active queries to stay within its chosen service tier. Upgrading your database should be as easy as clicking on a different tier. Note that your database might be offline while the upgrade is pending. The duration of this downtime depends on the size of your database (Azure ‘copies’ your database into a new tier)

Copyright © 2020 Castle Computer — Lyrical WordPress theme by GoDaddy