Application fails to establish Azure SQL Database when load is increased

Question

Hello,

We are using Azure SQL Database for our application but over time we start getting "Server is not found" error.

Our application DB workflow is as follows.

1) Our application is running on the K8s cluster
2) When the application receives requests it starts a process.
3) That process connects to the database, does the processing, and writes the data back to DB and exits.

But we are observing the following error in step 3 for 5-10% of the requests. The total connection to DB max to our queue size which is around 10 at the moment.

Sql State = 08001Native Error = 258Error Message = [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible.

What we have tried.

1. Increased size of DB from Basic to Premium to Business Critical ... but still getting this error.
2. We have tried the retry mechanism for failed connection ... we are still seeing this error.
3. We have checked for possible locks but everything seems OK. Also, running the application locally with an in-house SQL Server then the connections is fine and stable.
4. We are observing around 30+k connections to SQL Database when we run this testing activity.
5. We have checked the Azure SQL Metrics and Recommendation ... CPU usage and other metrics do not peak and stay around 30-50%.

Looking forward to your help in resolving this issue.

Regards,
Owais

Answer 1

30,000 sessions is the limit for Azure SQL Database as explained here.

Can you examine the way you are opening connections to the database? Are all those connections needed? Is there any layer opening many connections from within each transaction and for any reason they stay opened, they are not closed? Do you see there are loops within transactions?

For how much time queries executed by those connections are running? Are they optimized. Have you seen blocking occurring? Use this related documentation to explore this possibility.

Allow me to recommend you this related documentation about troubleshooting connection limits..