Our branch dashboards read from a reporting database that is loaded overnight from our trading system. Last year we moved it to Azure SQL Database on the serverless option, which charges for computing power only while the database is working. It is busy for a few hours each morning, when branch managers check yesterday's sales and today's deliveries, and idle for much of the night. Serverless can also pause a database completely when nobody has used it for a set time, so that you pay only for storage. We set it to pause after an hour, and the bill fell by more than half.
Three weeks later, branch managers began telling us the dashboards were showing the day before yesterday. Not every morning, but most.
The cause was the first connection of the day. The overnight load finished at about one o'clock, and an hour later the database paused. At half past five the dashboard tool connected to refresh its figures. A paused database wakes when someone connects, but that takes a minute or so, and meanwhile the connection is refused with an error saying the database is not currently available. Microsoft documents this and tells you to retry. Our refresh did not retry. It logged a failure and kept showing the previous figures as if nothing had happened. On mornings when an early manager had already woken the database, the refresh worked, which is why it looked random.
Nothing was broken. We had chosen a database that sleeps, and nothing that used it had been built to wake it. The overnight load had only ever worked because it started while the database was still awake from the day.
We made three changes. Everything that connects to the database now retries for up to two minutes before giving up. A small job connects at five o'clock, so the database is awake before anything important needs it. And every dashboard shows the time of its last successful refresh at the top, so a manager can see when the numbers are old. We kept the automatic pause, and most of the saving with it.
A cheaper way of running something often changes how it behaves as well as what it costs. When a pricing page says a service pauses when idle, read it as a sentence about availability, and test what the first visitor of the day will see.
– Serguey Shinder
Top comments (0)