For several years now, weâve had access to the serverless version of Azure SQL, a PaaS database that automatically stops when not in use and wakes up as soon as a request is made. The advantage? You only pay for storage when the database is off. This is particularly
useful for databases that arenât used frequently, like an app to manage time off or shared development databases.
Despite its benefits, serverless databases are not widely adopted. With the introduction ofSQL
Database in Microsoft Fabric, a serverless SaaS database, itâs an opportune time to revisit this topic and share some best practices.
Moving from Vertical Sizing to Horizontal Scaling
In traditional cloud databases, costs are primarily driven by the size of the server, measured in vCores/memory or DTUs. You need to provision the right resources to handle all requests, even if some donât need full capacity or the database isnât used during
off-hours. With serverless databases, the vCore count is flexibleâyou set a maximum but may be billed for less. The main cost driver is now the duration your database is running.
While this may seem like a minor change, it has significant implications for implementation and production. For instance, leveraging SQL query cache becomes less relevant, while monitoring request numbers over time becomes more important.
Serverless-specific best practices are tied to one thing: auto-pause
The biggest advantage of serverless databases is their ability to pause. With a minimum auto-pause delay of 15 minutes in Azure SQL and Fabric, itâs crucial to determine if this feature suits your use case. Ideally, serverless databases are most beneficial
for short or medium periods of use with long pauses. Take a few minutes to calculate and compare costs to ensure serverless is more economical than a PaaS server.
Few best practices to consider
Cache your data outside the DB
Most databasse are read-heavy. That means that for one INSERT or UPDATE statement, you may have hundreds of SELECT statements hitting your DB. By caching read-heavy data outside the database, you can reduce the number of times the database needs to wake
up, saving costs and improving performance. You could look at this articlehttps://learn.microsoft.com/en-us/azure/architecture/patterns/cache-asideto
learn more about this pattern.
Timeout and connection pooling are crucial settings in your Object-Relational Mapping (ORM) tool. Some default settings may be too aggressive if you need to wait for database wakeup/autostart. Hereâs an example of how to configure Entity Framework Core for
optimal performance with a serverless database:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
connectionString,
sqlServerOptionsAction: sqlOptions =>
{
sqlOptions.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null);
sqlOptions.CommandTimeout(TimeSpan.FromSeconds(60));
});
}
Measure Database wake up
Monitoring time it takes for your database to wake up from a paused state can help you optimize performance. Use tools like Azure Monitor to track wake-up times and adjust your auto-pause settings accordingly. Itâll also ensure that ânobodyâ is waking up your
database. Letâs take a simple example: You containerize your app. In development, everything is fine. But in production, you database is always up. The culpirt: Docker
healthchecks and Kubernetes liveness probes. Youâve done your job by implementing a thorough probe that even
checks if your database is healthy, and it comes and bite yor.
Be transparent with your users
You have made an economical-conscious choice, that happens to be a sustainable choice as well. And sometimes only, some of your users may have to wait for a few seconds? Let them know!
Inform your users about potential delays due to database wake-up times. Transparency can help manage expectations and improve user satisfaction.
Depending on your Framework, this could be quite easy to implement. Here is a very
naive implementation in React. In an ideal situation, your checks may be a bit more advanced. You may want to do this only for the first-time request (and save it in a cookie?) or have a âserver pingâ method: If you can reach the server, that
certainly means that youâre in wakeup phase and youâre safe to display the message.
Delay writes
It might not be applicable to all applications, but you could delay your writes if there is no active connection. You could send the message in a Service Bus that will get processed by a worker. Yes, thatâs not an experience you may want, but if youâre building
a Survey app, you could wait something like one hour to get your results.
There is a lifetime Azure SQL Serverless free tier! With 100,000vCore-seconds per month and auto-pause at 15 minutes, you could wakeup your database and execute queries for 5 minutes every 20 minutes (consuming 600 vCore-seconds at 0.5 vCore each time), you could
wakeup your DB 166 times a month / Every 4.5 hours. And you can schedule the execution of an Azure Container App Job
every x hours quite easily.
One more best practice for SQL Database in Fabric
One more notable feature of SQL Database in Fabric is its real-time analytics store replication, which can be queried from an SQL Endpoint. If you accept a small latency of less than a minute, you can use the SQL Analytics endpoint for read operations and the
SQL Database endpoint for write operations. This setup allows for efficient separation of read and write workloads, enhancing performance and scalability. Please note that, in the case of Fabric, youâre still paying for the number of CUs attached to the capacity,
whenether youâre using SQL Database or the SQL Endpoint. The main interest here is to spare some CUs to do something else on your stack.
