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 size||2 GB|
|Max concurrent workers (requests simultaneous)||30 users/ queries|
|Max concurrent logins||30|
|Max concurrent logins||300|
|Max Database Size||250GB||250GB||250GB||1TB|
|Max Concurrent Workers||60||90||120||200|
|Max Concurrent Logins||60||90||120||200|
|Max Concurrent Sessions||600||900||1200||2400|
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:
|Max Database Storage||1TB||1TB||1TB||1TB||1TB|
|Max Concurrent Workers||400||800||1600||3200||6000|
|Max Concurrent Logins||400||800||1600||3200||6000|
|Max Concurrent Sessions||4800||9600||19200||30000||30000|
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:
|Max Database Size||500GB||500GB||500GB||500GB||4TB||4TB|
|Max in-memory OLTP Storage||1GB||2GB||4GB||8GB||14GB||32GB|
|Max Concurrent Workers||200||400||800||1600||2400||6400|
|Max Concurrent Sessions||30000||30000||30000||30000||30000||30000|
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)