After some googling, there seems to not have a straight forward answer to the question. MSDN Forum mentions it depends on CPU, Memory usage, database size etc. Could someone help explain or perhaps suggest an example to show why there are so many factors limiting the number of concurrent connection. Also how could one ever find out the maximum connection of an application?
So are so many factors because there are so many different types of loads. When it get loaded the only defense mechanism is to deny connections.
I you are looking for max connection you are looking for the wrong thing.
Design your application to be scalable - acquire the connection as late as possible and release the connection as soon as possible. A well designed application could have 100 active users and never have more than 10 active connections.
Notably, SQL Server by default has:
...maximum (32,767) user connections...
Any other limitation of connections will be
- Dictated by the application connecting to the database or
- Resource contention that causes SQL Server to be unable to handle adding more connections.
With proper connection pooling the hard upper limit should not be anywhere near the connections most applications require.