I have a cloud-based application where there can be up to 5000 "worker nodes" communicating with a web server. All 5000 could be started simultaneously and once online, would call an API method to pick-up a job. This API method in turn calls a SQL Server stored procedure to get the job details and return a response to the individual node. To prevent multiple nodes getting the same job, the stored procedure puts a lock on a particular database table while it executes.
Web Server is Windows 2016 Server running IIS 10. SQL Server is Microsoft SQL Server Standard 2017
My question is how to best manage these connections through web.config or IIS settings. As far as I know, my main levers are
- Max Pool Size in web.config
- Connection time-out for IIS Site
I can increase the pool size to 10,000 of course and set the connection time-out to 2 or 3 minutes but I'm not sure if there's a best practice for managing something like this.