Personally, I never hit any limitation in the number of connections, so I
never searched this topic. The exact relationship between ADP and
connections: how long they are connected, are they closed regularly, are
they reopened automatically, how well is the connection pool used, etc.;
have never been clearly explicited by any MS documentation.
But excerpt for Application Role, I don't remember anyone having problem in
this regard. The only one that I can think of is for some bugs (or *design*
problems) in the connection pools when many different applications use it
with different kinds of parameter connections; for exemple when you are
connecting to a public SQL-Server hosted by a public web hosting service.
The usual solution is usually to isolate yourself from the other connections
by adding some strange parameter in the connection property or simply by
deactivating the connection pool in your case.
Specific to your problem, it is my opinion that inactive connection will be
closed and recycled by the SQL-Server connection pool and that you will have
a problem only if the number of active connections - ie connections wanting
to do some usefull work or having an opened transaction at the same time -
reach the maximum number of connection available on the SQL-Server. I
don't know what is this maximum (100?) but all you will have to do is to
augment this number if necessary.
Also, *regular* applications may often use more connections than you think.
Also, I don't see why you thinking that using more connections will result
in a less efficient work. The real problem is usually the work done by the
SQL-Server, not the number of open connections.
