Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Connecting to many different instances #513

Closed
ATofler-Quest opened this issue Apr 7, 2020 · 8 comments
Closed

Connecting to many different instances #513

ATofler-Quest opened this issue Apr 7, 2020 · 8 comments

Comments

@ATofler-Quest
Copy link

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
Copy link
Member

cheenamalhotra commented Apr 7, 2020

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?

@cheenamalhotra cheenamalhotra added this to Needs triage in SqlClient Triage Board via automation Apr 7, 2020
@cheenamalhotra cheenamalhotra moved this from Needs triage to Waiting for customer in SqlClient Triage Board Apr 7, 2020
@ATofler-Quest
Copy link
Author

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
Copy link
Member

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
Copy link
Member

Hi @ATofler-Quest

Do you have any update on this issue?

@ATofler-Quest
Copy link
Author

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)
image-2020-05-14-10-28-07-789

@cheenamalhotra
Copy link
Member

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
Copy link
Member

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
Copy link
Member

Closing due to inactivity.

SqlClient Triage Board automation moved this from Waiting for customer to Closed Oct 21, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
Development

No branches or pull requests

2 participants