This article was previously published under Q176056
This article has been archived. It is offered "as is" and will no longer be updated.
This article addresses two common ActiveX Data Objects (ADO) questionsregarding the proper approach to handling connections in an Active ServerPages (ASP) page:
Why is it a good idea to open and close ADO connections within a pageinstead of maintaining the connections into session variables?
Can you use connection pooling when you have specific connection requirements?
The following explains the key concepts to answer the prior questions.
Description of the Web environment.
Web site scaling and efficient use of server resources.
Description of connection pooling.
Why would you open and close ADO connections within a page instead of maintaining the connection in session variables? In traditional client/server applications if you maintain the connection everything seems to be okay.
When you use ASP pages it is a different environment then when you were using traditional client/server applications. Also, many largeclient/server applications did not perform connection pooling, especially if they were built in modular structure. The problems with determining aconnection per client are similar between the traditional client/serverapplication and the Web server environment, but the connections is exacerbated by the nature of the Web server.
In the Web server, everything is multi-threaded and there is no concept ofa "connection" between the Web browsing client and the server. The sessionID is a cookie passed back and forth from browser to server (which if thebrowser does not support cookies or has that option turned off, session state does not work. The only way a sessionbecomes invalid is if you do not return to the server within thetimeout period, or if you make an explicit call from the script to terminate the session. The result is there not a good way to determine if the client needs the connection.
In a Web server, the entire idea is to scale. A public Webserver could easily have 100,000 clients maintaining active sessions(probability increases as session timeout goes up) and while an intranetserver may not have such a load, you would not want to do something in yourarchitecture that would prohibit that possibility.
If you cannot establish a connection per client and you plan onscaling past a few thousand concurrent active sessions. You can take stepsto try and avoid that type of scaling. You will quickly realize thatyou are a server process, and in scalability. Scalability can be accomplished through resource pooling. This is why Microsoft Transaction Server and Internet Information Server have functionality for resource pooling to support a huge transaction load.
If you do not pool there will be idle connections wastingserver and network resources. You also have some threadingissues that can occur if multiple concurrent threads end up hitting on thesame connection (though the session ID might save you here, but it is conceivable that a browser could submit two concurrent requests using thesame session ID and you could get into situation with transactions orwith SQL Server's inability to process more than one command at a time on agiven connection).
If you "open" the connection, use it and"close" it again, the connection cansafely be handed to another thread processing a different command. If theserver load gets light, the connection pool is trimmed back automaticallyand others using that server get better performance. If the server loadgets heavy, the pool can grow as needed. However, you'll probably noticethat you can support lots of clients with just a few connections.
If you have written a traditional client/serverapplication that was intended to be modular, you quickly realize that connection pooling is important. Since services have to be autonomous, services have to create and use connections. Since you do not want each client desktop creating 5 or 10 connections to the database, you make a shared ConnectionPool component which is what an ODBC 3.0 Driver Manager and later is.
How does connection pooling work for users with specific connectionrequirements? When you pool connections among anonymous users who are operating against the same data objects using similar connection semantics,each user must be authenticated. The user should only have access to a limited number of data objects based on theiridentity.
Can connection pooling be used in the latter case?
Connection pooling does take authentication into consideration, and it will not assign a connection that has a different identity than the one that is being requested (if you submit a connection string with "UID=me;PWD=foo", it won't return a pooled connection that has a UID and PWD of somethingdifferent). Connection pooling also works when using integrated security in that you will not get a connection that was opened under a different security context than you are currently running under.
If you want to use an authenticated connection (integrated security) but want to keep the scalability high, what do you do?
Since there is no way to change the security context of an existing openconnection, connection pooling would only buy you the ability to reuse aconnection that was opened on the first site hit by that user. The poolwould keep it alive for a reasonable amount of time and if the user keptinteracting with the site, the connection would remain live. If the userstopped interacting with the site, the connection would eventually fall out of the pool and be closed. Thisstill offers some benefits since you code as if you're doing drop/reconnectand the pool optimizes the case where the user comes back quickly. However, pooling cannot overcome the problem of not knowing when the user really leaves by aging the connection and killing it if the user doesn't return in the specified timeout period. You could argue that using the Session object would be a way of doing this too, but note that here you could have the connection pool timeout set differently than the session timeout. This would give the user theability to go to lunch and still have the session state there, but nottying up a connection the whole time.
It might be a better approach to classify users into roles like "admin,""user," and "browser." Each role has a SQL Server login (UID and PWD), andyou map the current user to that role based on scheme, for example their name. The users are still authenticated by the Web server, but you do not use integrated security to the database and just have three or four different login identities.This enables you to still have scaling (since the majority ofpeople will fall into one or two roles), and you can also enforce certainsecurity in the application logic when needed (such as, only allow "admin"see employee passwords). Keep in mind that the role and extra security logic is application enforced rather than being in the system, but sometimes you have to do things that the system should do,but doesn't do well at the moment. It would be ideal if the system couldpick a connection out of the pool and change the security context for it,but I don't think that's possible today.
For the latest Knowledge Base articles and other support information onVisual InterDev and Active Server Pages, see the following page on theMicrosoft Technical Support site:
Microsoft Visual InterDev 1.0 Standard Edition, Microsoft Visual InterDev 6.0 Standard Edition, Microsoft Active Server Pages 4.0, Microsoft ActiveX Data Objects 1.0, Microsoft ActiveX Data Objects 1.5, Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1 Service Pack 2, Microsoft ActiveX Data Objects 2.5, Microsoft ActiveX Data Objects 2.6