Max pool and time out for SQL connection string?

sharepoint0520
sharepoint0520 used Ask the Experts™
on
Experts,
 We have created one application by using sharepoint  and SQL server. We have 2 billion data in SQL server and we are using SQL connection string to pull data to sharepoint application page. Now near 20000 thousand users going to use this application. I would like to know some assumptions for connection string setting for Max pool and Time out.

1.what is the max connection pool
2.What is the  risk of not using any timeout or Max pool size
3. What is recommended limit for connection pool for 1000 concurrent users

We expect 1000 concurrent  users to access this application. Please advice.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> 1.what is the max connection pool

Connection pooling is a concept at the application layer. In your case, its from Sharepoint level configuration..
From SQL Server perspective, it can accept any no. of connections depending upon the CPU and RAM available in the Server. If the hardware is good, it can support more connections.

>> 2.What is the  risk of not using any timeout or Max pool size

Let's say If there is no timeout value configured, then if Sharepoint requests large data(in worst case scenarios) it will run as long as it could to complete it. This can potentially cause Server level resource issues and impact others. If we have timeout values configured, then longer or problematic queries will be killed by Sharepoint Connection pool and the resources will be freed on SQL Server level.

If there are no Max Pool configured, then Sharepoint can send any no. of connections to DB Server based upon the no. of concurrent user requests. If DB Server hardware is good, then configuring or not configuring Max Pool doesn't matter. but if your DB Server is having limited hardware resources, then set the Max pool so that you don't overload the DB Server with lot of requests.

>> 3. What is recommended limit for connection pool for 1000 concurrent users

For 1000 concurrent users, I would start to 2500 to 3000 and will monitor closely and increase it as required.

Author

Commented:
Thank you Raja. I just wanted to clarify that we created custom application with .net  framework and used below connection. And we used client side code by javascript and rest API to read data from application page. So data we are displaying from SQL server though service account.

<add connectionString="Server=server1;Database=db11;Integrated Security=true" name="test" />

Do you think should we set up Max Pool and Timeout for this connection string?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Okay, got it..
Connection timeout value is 15 seconds by default if you don't specify any thing..
I really wonder whether you really would have 1000 concurrent users using your application.. if so, then kindly specify both Min and Max Pool size to 1000 and 2500 to start with..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Walter CurtisSharePoint AED
Distinguished Expert 2018

Commented:
Just a question - if you built a .net application, why are you using SharePoint also?

Author

Commented:
Business uses sharepoint and for this web application we used LADP authentication. They also use some sharepoint functionality along with this. And we uses only client side coding so that is why first we created the application page to pull SQL data to sesssion and from application page we reading data with rest api.

Author

Commented:
Raja,
 We have 4 core cpu and 32 GB ram. Do you think do we need to set Timeout and Max pool properties?
Walter CurtisSharePoint AED
Distinguished Expert 2018

Commented:
Thanks for the info. Sounds like you have a nice setup.

Good luck...
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Thanks for the Server info..

>> Do you think do we need to set Timeout and Max pool properties?

As mentioned above, Timeout value is configured by default to 15 seconds and hence no need to set it unless we want to change it to some other value.
And set both the Min and Max Pool size to 1000 and 2500 to start with.

Author

Commented:
Thanks a lot Raja. One more thing to clarify. As i mention we created the application page project in visual studio for our sharepoint farm to read data from SQL. (https://docs.microsoft.com/en-us/visualstudio/sharepoint/walkthrough-creating-a-sharepoint-application-page?view=vs-2017)

And we have used the basic connection string to pull data. You have suggested that we should set Min and Max Pool size to 1000 and 2500 to start with for 1000 concurrent users.  What is the  risk of not using  Max pool size? By Default it takes 100 Max value?

Please advice

Thanks again for all information.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> What is the  risk of not using  Max pool size? By Default it takes 100 Max value?

Yes, by default Min and max pool size would be set as 0 and 100..
If not configured above 100, then your application can establish a max of 100 connections at any point in time and any new db connection request when these 100 sessions are in use will get an error message saying "Max Connection pool size reached".
https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.connectionstring?view=netframework-4.7.2

Author

Commented:
Thank you Raja
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial