Best practices for Azure SQL Serverless and Fabric Databases

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.

Properly configure your ORM

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.