When building a SaaS application used by several clients, a common pattern consists to create a database per client.
Using several databases instead of one offers some advantages:
- Data is partitioned physically
- Each database can be sized and scaled independently
However, using many databases offers new issues too :
- Load management
The scale strategy will be different for each database because each client is going to use the SaaS app in a different way and peak loads will happen at different times. To face to performance issues each database must be monitored independently to detect scaling needs. This monitoring process is complicated when a lot of databases are used, besides some database can need more resources when nobody can monitor them, for example at night.
- Data and schema updates
To update data stored in databases or to apply a new schema structure, customs scripts have to run on each database, cumulate those operations can take a long time and are possible source of error.
In this article, we will analyze and resolve the first problematic using a service “built in” Azure named Azure Elastic Db Pool. The second can be resolved using Azure Elastic Job and will be explained in a following article. To make this one simple and understandable, will we use a fictive SaaS application named “SaaSapp”. This application is used by 3 clients, each owning a database: « customer1-db », « customer2-db », « customer3-db ».
Azure SQL Database, basis
Pricing tiers and performance levels :
When creating an Azure SQL Database, a pricing tier must be chosen. This pricing tier defines the maximal size (GO) of the database and the compute resources that the database can use (CPU, Memory, I/O throughout, I/O latency). The unit to represent consumable resources is the DTU : Data transaction Unit.
Actually 4 pricing tiers are available (“Basic”, “Standard”, “Premium”, and “Premium RS”). Each of them propose several performance levels (retails are available here). In memory features (OLTP) are only available with “Premium” and “Premium RS” pricing tier.
When an Azure SQL database performances are insufficient to provide a good quality of service, it’s possible to scale it in two way: vertically or horizontally.
A vertical scaling consist to increase or reduce the compute resources usable by the database: with Azure SQL Database, it can be done increasing or reducing the DTU configured.
Vertical Scaling = Increase / Reduce DTU
An horizontal scaling consists to add databases to distribute the load on several databases instead of one. Contrary to vertical scaling, this type of scaling is only possible with a custom data partitioning. Tools like Azure Elastic Scale can be used to manage easily horizontal scaling.
Horizontal scaling = Add new databases
The following schema shows a SaaS application using 3 independent databases (one per customer) :
Red rectangles represent the compute resources (DTU) usable by each databases. If databases use a pricing tier “Standard” with a performance level of 1 (S1), they can consume each 20 DTU. This pricing tier costs about 30 $ / month, the total for 3 databases using an S1 pricing tier will be 30 x 3 = 90 $ / month.
With this architecture, it’s necessary to manage the performance level of each database independently. Usually, two main strategy are used :
- Oversizing : Permits to insure an optimal quality of service but the cost will be higher
- Undersizing : Economic solution, but dangerous because the infrastructure used can be not sufficient to answer to business needs and it can cause a degradation of the user experience
Besides, a question stays unanswered: How to manage peak of loads in an efficient way? The elastic database pool will help us.
Elastic Database Pool : resources sharing
An elastic database pool provide compute resources shareable between several databases. The units used to represent those shared resources is eDTU (elastic Data Transaction Unit).
An elastic pool works with classic pricing tiers (“Basic”, “Standard” and “Premium”) and performance levels associated, each pricing tier provide:
- A number of eDTU depending of the performance level
- A maximum and a minimum of eDTU usable by databases *
- Storage usable by the pool (GO)
A « Standard » pool configured with 50 eDTU permits to each database to use between 0 and 50 eDTU and The storage limit is 50 GO.
* Each database is configured with a minimum and a maximum of eDTU usable?
In the pool each database is configured with a range of usable DTU. The minimum can be 0 and the maximum must be enough high to absorb peak loads (the azure documentation advise to define a maximum equals to 1.5 x average load).
[Warning]: The maximum of eDTU usable by a database must not be necessarily the maximum of eDTU of the pool, it must fit with peak loads. Moreover it’s not advised to configure all database’s pool with the maximum of eDTU usable by the pool to avoid that one database use all eDTU available.
The following shema shows a SaaSapp using 3 databases gathered in an elastic database pool:
Real example with a “Standard” Pool:
A standard pool configured with a Standard Pricing Tier costs about 113 $ / month. If it’s configured with a range of 0 and 50 eDTU usable for each database, it can contains 100 databases.
No matter the pricing tier used, it’s important to consider peak loads periods for each database to insure that databases are able to use their maximum of eDTU with having negative impacts on other databases performances. Indeed, databases have to share the eDTU of the pool and they can’t use in the same time the maximum of eDTU provided by the pool !
Setup and configuration
To create azure resources, we will use the following Azure Cli command lines. They can be executed directly in the Azure Cloud Shell :
- Create the resource group « data-rg » :
az group create -l westeurope -n data-rg
- Create the database server « saasappsrv » :
az sql server create -g data-rg -n saasappsrv -l westeurope -u adminsvr -p TranisePwd11
3. Create the elastic db pool « saasappdbpool » :
az sql elastic-pool create -g data-rg -n saasappdbpool -s saasappsrv –-edition Standard --db-min-dtu 0 --db-max-dtu 50
When creating an elastic database pool, we need to specify:
- A SQL Server which will host the databases
- An Edition to define the pool pricing tier
- Range of minimum and maximum DTU usable by each database of the pool
In this example, we create a « Standard » pool with 50 eDTU. Each database will be able to between to use 0 and 50 eDTU.
4. Create 3 databases specifying to pool created previously :
az sql db create -g data-rg -n customer1-db -s saasappsrv &amp;amp;amp;amp;amp;nbsp;--elastic-pool saasappdbpool az sql db create -g data-rg -n customer2-db -s saasappsrv --elastic-pool saasappdbpool az sql db create -g data-rg -n customer3-db -s saasappsrv &amp;amp;amp;amp;amp;nbsp;--elastic-pool saasappdbpool
Once theses commands executed with success, the resource group « data-rg » is accessible in the Azure Portal:
In the pool dashboard, we can find 2 monitoring charts, besides it’s possible to manage the pool using the left menu “Configure pool”:
The configuration dashboard helps for the following tasks:
- Add or remove database from the pool
- Scale the pool (increase or reduce eDTU and storage capacity)
Financial and technical interests
The elastic pool has a real financial interest when you plan to use several database with high pricing tiers. For example, our test pool is a standard one and costs around 113 $ per month and without extra charge it can contains 100 databases.
Without using an elastic database pool, here are the following prices for databases with a « Standard » pricing tier:
- An Azure SQL Database Standard S1 costs around 30 $ / month, it permits to use 20 DTU
- An Azure SQL Database Standard S2 costs around 75 $ / month, it permits to use 50 DTU
4 x 30 (Azure SQL Standard S1) = 120 > Elastic pool Standard 50 eDtu = 113
2 x 75 (SQL Standard S2) = 150 > Elastic pool Standard 50 = 130
In a technical aspect, the elastic pool has an interest when it contains at least 2 databases because it makes scaling management easier and by the way it helps to face to peak loads.
Besides it provides performance level that classic pricing tiers for single databases does not propose: for example a database in a pool can be configured to use 300 DTU, that’s not actually possible with classic pricing tiers.
Finally, it provides tools to create the infrastructure necessary to use Azure Elastic Jobs. We will cover this subject in the next article.
Happy coding 🙂