When going to the cloud, saving costs while maximizing performances is always a goal to reach but sometimes also not so easy to achieve.
I’ve talked a lot in the past about how to move your databases to the cloud, how to use Azure SQL Database for Dynamics 365 Business Central, how to optimize performances and also (if cost is not a problem and you want the maximum performances for your database in general and for Dynamics 365 Business Central on a IaaS architecture) how Azure SQL Hyperscale could have a lots of performance advantages over Azure SQL (but you need to handle scaling).
Today I want to talk a bit about a quite new service tier for Azure SQL databases: the serverless tier.
Azure SQL Database serverless is a compute tier for single databases that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.
Azure SQL Database serverless is not for all scenarios, but if you have a database that is not always heavily used and if you have periods of complete inactivity, this is a very interesting solution that can you guarantee performances and that can help you on saving a lot of costs.
A tipical scenario is the classic database usage that I think you have in a Dynamics 365 Business Central installation: the database is heavily used from Monday to Tuesday, maybe 8 hours a day, then it’s not used in the night and it’s not used during the weekends. In this situation, you have 5 days * 8 hours/day of usage. And during the working days, I think that you have some “up and down” of database usage. This is where Azure SQL Serverless could help you: it can automatically scale your DTUs up and down accordingly to the database usage and it could pause the database when it’s not in use. You pay only for what you’re effectively using.
Creating an Azure SQL Serverless database is extremely simple and it can be done via the Azure Portal or directly by using scripts (Powershell or Azure CLI). To create an Azure SQL Serverless database via the Azure Portal, you need to select the General Purpose model and then select the Serverless option:
What you’ll pay for this instance will depend on how much memory and how many processors your database uses, as well as how much storage the database requires. You can set the minimum and maximum number of cores (from 0.5 to 16) and the available memory (linked to vCores, from 3GB minimum to 48 GB). Regarding storage, cost is about $0.12 a month for every GB, while computing cost is about $0.27 per vCore per hour. This means that if you have a 50GB database that uses 2 cores for 8 hours a day, for a month it costs about 6$ for the storage + (0,27*2)*8 * 20 working days = 92$.
Storage is cheap, as usual. You’ll pay about $0.12 a month for every GB. Compute is just under $0.27 per vCore per hour. If your database uses 1 processor for an entire day, that’s under $7 for the day. However, if the database is inactive you can pause the database and pay nothing for the compute power.
As an example, here I’m creating an Azure SQL Serverless database that can span from 0.5 to 6 vCores with a maximum data storage capacity of 20 GB. For this database, I’ve selected to enable the auto-pause feature:
As said before, the database can be paused manually, programmatically or automatically by enabling the auto-pause feature (as you can see in the previous picture). When auto-pause is enabled, Azure will pause the database if it’s not detecting activities on the database for the number of hours specified. The minimum number is 1 hour.
The above picture shows also the estimated price. As you can see, you will pay about 2.52 euro/month for the storage and about 0.000122 euro for compute cost (vCore per second). As a comparison, if you select a Provisioned instance (where compute resources are pre-allocated and you’re billed per hour based on the vCores configured) with 4vCores and 20 Gb of maximum data size, the cost is about 385 euro per month.
I’ve done a set of stress tests using SQLQueryStress tool on this database (by simulating some tipical ERP queries) and performances and scalability is good. The first test simulates 50 users performing some select/insert queries (with lots of joins) for 100 iterations each:
The second part of the test simulates 200 users performing the same set of queries (1000 iterations each):
As you can see from the above diagrams, in Serverless databases you have also the App CPU billed and APP CPU percentage metrics. The APP CPU percentage metric is in my opinion quite interesting to check because it gives you the % of the vCore allocation (100% means that you’re database vCore allocation is under-estimated) and you can tune up your database scaling accordingly. If you see (as in my case) that 6 vCores are too much (you’ll never reach this limit) you can change the vCore allocation for your Serverless database.
From the above image, you can also see that App CPU billed metric shows the amount of compute billed (measured in units of vCore per seconds). In this case, to execute this test I have a 2,69K vcore per seconds usage. The compute unit price (as you can see in the second image of this post) is 0,000122 euro so the estimated billing is about 2,69*1000*0,000122 = 0,33 euro.
Obviously, this is not a featured complete test, but I think that it can give you an idea that the Serverless option can handle scalability and performances in a great way while maximizing cost savings. The database engine is able to handle scaling dinamically (up and down) accordingly to the database usage and you always pay for how much the engine is working.
Azure SQL Serverless is a great choice on lots of scenarios, but there are some aspects that you should consider and that could affect your choice:
- When the database is paused, the resume is not immediate but you’ve to wait some seconds (on my personal experience, at least about 40 seconds). During this resume phase the database is not accessible.
- When your database is not used, the cache is cleaned (so you can have some small performance degradations after a resume).
- The compute unit price is higher for a serverless database than for a provisioned compute database (Azure SQL serverless is optimized for workloads with intermittent usage patterns). If CPU or memory usage is high for a long period of time, then the provisioned compute tier may be less expensive.
Obviously, if you disable the AutoPause feature your database will always be online and you pay a bit also if your database is not in use. Instead, when the database is paused, only the storage is charged. Remember also that the AutoPause feature may not happen if:
- The are active sessions in your database
- The CPU usage for user’s workloads is > 0
- Long term backup is is progress
- Active geo-replication is enabled
Is it a cloud SQL database solution for everyone? No. Is it the recommended solution for Dynamics 365 Business Central with Azure SQL as backend? No. But if you have a particular usage patterns where you have peaks of loads (like working hours) and other moments where you’re database is not used (like at night) this database type can help you on saving money. When instead you have a more regular, predictable usage and higher average compute utilization over time, a provisioned instance can be better for you. Before deciding the best database type for your application, you should evaluate your usage patterns a lot. Remember that you can switch the database from provisioned to serverless also after creation.
As a comparison, here a brief summary of the main differences between a provisioned and a serveress instance:
Stefano Demiliani 