Question regarding pricing tiers for Azure Sql database and if the cheapest plan ($4.90/month) offloads resources after a specific time period for non use.
If my Azure Sql Server database doesn’t get queried for a specific period of time, ex. 30 minutes. Will Azure offload the resources for the database, until it gets a new request?
This would be similar to an app service running the basic (free) plan. After ~30 minutes the site resources get offloaded from memory (I think). So, when I go to the site, instead of loading up immediately, it takes about 5-10 seconds, then if I hit the site again, because it’s loaded back into memory, it will load immediately.
Does this same thing happen with Sql Server running in Azure with the cheap $4.90/month plan? It seems like it does! If I don’t hit my app service (website), now upgraded to the S1 plan so no offloading, and come back a day later and hit a page that has to fetch database results to display on the page, it will take approx. 5-10 seconds, but then if I refresh the page again or hit another page that needs to query the DB, the queried data comes back instantly!
2
Answers
You can set your Azure SQL Database as serverless. It is the only Azure SQL Database (PaaS) option that can be auto-paused after a specific time of inactivity has passed, the minimum time of inactivity is 1 hour. You also get billed by the minute instead of by the hour. Here you will find step-by-step tutorial how you create a serverless database.
Make sure you set the auto-pause delay setting.
No it should not do that. You reserve capacity in vCores or DTUs, and that is what you should get. But different management operations (like maintenance, capacity restructuring, moving workloads from faulty hardware) will evict your workload from the underlying host it is running on and spin it up on another hardware. It would be transparent to you if not for the flushed cache. Of course this should not happen every time your application goes idle, but can be the case from time to time.
If you are using DTU purchasing model, I would recommend switching to vCore based model as it’s the recommended one. Then take a look at the CPU Used metric. As long as the line of the metric is continuous you have allocated CPU, when the line becomes dotted it indicates that the db is paused, but the dotted behavior should only apply for serverless.