Azure @ Enterprise - Serverless scale problem - Functions in App Service Environment connecting to Azure SQL

This post is about working with Azure Functions in App Service Environment hereafter ASE. Not sure it is qualified to call a classic problem but we all get into trouble when we use the serverless scale model. That is mainly due to the inability to scale by the dependencies of the Serverless compute tier.

To be more clear the Azure Functions will scale out but underlying dependencies such as Azure SQL will not scale with Functions and eventually lead to system failure.

Problem

Let us take a scenario. There is Azure Function that is invoked by the Azure storage queue. There will be one instance of Function running all the time. When the queue has more than 100 messages in the queued state, the Function will scale out to 4 instances to complete processing. Then scale down to 1. 

Each Azure Function instance can dequeue the maximum of 32 messages in a batch.32 is a hard limit.

When the scale-out happens the Azure SQL fails as it cannot accept more connections.

Observations

The SQL server sp_who2 command shows there are a lot of connections in the sleeping state from the Function instances.

The Azure SQL is in General Purpose Gen 5, 4 vCores tier. It can accept only 400 connections.

Root cause

.Net has a default connection pool size of 100. There are other applications also connecting to the same database apart from the Function. 4 Function instances wanted to establish 4x100=400 connections which are not possible with the Azure SQL tier as the other applications also making a similar number of connections. Hence it says cannot accept more connections.

Solution(s)

There can be multiple solutions

• Scale up the Azure SQL tier to General Purpose Gen 5, 8 vCores that can accept 800 concurrent connections.
• Throttle the .Net connection pool to 32 or 64. This number is based on the maximum number of messages dequeued by one Function instance. So max connections from all 4 pools will be maxed 128 or 256. There are some left for the other applications as well.
• The Serverless Azure SQL tier is an option. But please understand that the Serverless compute has a warmup delay after idle usage periods hence best suited for intermittent and unpredictable workloads that can afford this delay.

Happy Serverless