Max pool and time out for SQL connection string?

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.
sharepoint0520Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharepoint0520Author 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 GuideCommented:
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..
Free and Easy Network Configuration

Network Configuration Generator is designed to make it easy to configure network devices, including Virtual LANs and other advanced features without opening the Command-Line Interface (CLI)! Help boost your network performance, run advanced network scripts, and bypass the CLI.

Walter CurtisSharePoint AEDCommented:
Just a question - if you built a .net application, why are you using SharePoint also?
sharepoint0520Author 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.
sharepoint0520Author 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 AEDCommented:
Thanks for the info. Sounds like you have a nice setup.

Good luck...
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
sharepoint0520Author 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 GuideCommented:
>> 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
sharepoint0520Author Commented:
Thank you Raja
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.