Connecting to many different instances

[ATofler-Quest]

Hello,
I would like to consult about a special case of connecting multiple different databases.
We are building a system using Azure Function.
Each minute our system connects to many different SQL Azure instances.

So I don't see a reason to use connection pooling because every time we connect to the SQL azure it will be from different processes of azure function.
So we set pooling attribute to be false.
Then we got the following error:
"Timeout attempting to open the connection. The time period elapsed prior to attempting to open the connection has been exceeded. This may have occurred because of too many simultaneous non-pooled connection attempts."

If we don't use connection pool why we weren't able to open a connection?
What approaches do you suggest in this case?

BTW, we use Microsoft.Data.SqlClient(1.1.0) with .NET core 2.2

Thank

[cheenamalhotra]

Hi @ATofler-Quest

Could you provide us your connection string & also let us know which platform (Windows/Unix) your Azure function corresponds to?

Also could you give v1.1.1 a try and confirm if you face similar issue?

[ATofler-Quest]

Hi @cheenamalhotra ,

Our azure function platform is windows.

We have 2 types of connection:
database user:

Server=tcp:{SERVER_NAME_PARAMETER},1433;Initial Catalog={DATABASE_NAME_PARAMETER};Persist Security Info=False;User ID={USER_ID_PARAMETER};Password={USER_PASSWORD_PARAMETER};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=5;Pooling=false;

azure active directory user:

Server=tcp:{SERVER_NAME_PARAMETER},1433;Initial Catalog={DATABASE_NAME_PARAMETER};Persist Security Info=False;User ID={USER_ID_PARAMETER};Password={USER_PASSWORD_PARAMETER};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=5;Authentication=\"Active Directory Password\";Pooling=false;

We will try soon v1.1.1.

[cheenamalhotra]

Your connection timeout 5 seems pretty low to me when working with multiple connections. Azure connections get generally slow when working in multiple parallel threads. We fixed similar issues in #466 in 2.0.0-preview2 but that was related to pooling, I would still request you to give that a try and let us know if you face issue again.

If yes, please wrap us a repro app and link it here that we can try to reproduce and investigate.

[cheenamalhotra]

Hi @ATofler-Quest

Do you have any update on this issue?

[ATofler-Quest]

Hi @cheenamalhotra,

I tried to use v1.1.2, it had the same issue.
we tried to use different "connection timeout" values, it didn't help.

Currently, we do 3 retries with a jittered back-off policy.
About 3% of the first requests fail.
In the following chart, you can see the amounts of retries. (fails - didn't succeed after 3 retries)

[cheenamalhotra]

Hi @ATofler-Quest

We've identified 1 issue with Transient Fault Handling, and fix is in PR #637 - would you like to try and let us know if that makes a difference in your case?

[cheenamalhotra]

Hi @ATofler-Quest

Pinging again, please let me know if you could try the fix from PR #637 or provide us a repro by any chance?

[cheenamalhotra]

Closing due to inactivity.