The information in this article applies to:
SUMMARY
This article addresses two common ActiveX Data Objects (ADO) questions
regarding the proper approach to handling connections in an Active Server
Pages (ASP) page:
MORE INFORMATIONQuestion 1Why would you open and close ADO connections within a page instead of maintaining the connection for session variables? In traditional client/server applications if you maintain the connection everything seems to be okay.Answer 1When you use ASP pages it is a different environment then when you were using traditional client/server applications. Also, many large client/server applications did not perform connection pooling, especially if they were built in modular structure. The problems with determining a connection per client are similar between the traditional client/server application 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 of a "connection" between the Web browsing client and the server. The session ID is a cookie passed back and forth from browser to server (which if the browser does not support cookies or has that option turned off, session state does not work. The only way a session becomes invalid is if you do not return to the server within the timeout 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 Web server could easily have 100,000 clients maintaining active sessions (probability increases as session timeout goes up) and while an intranet server may not have such a load, you would not want to do something in your architecture that would prohibit that possibility. If you cannot establish a connection per client and you plan on scaling past a few thousand concurrent active sessions. You can take steps to try and avoid that type of scaling. You will quickly realize that you 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 wasting server and network resources. You also have some threading issues that can occur if multiple concurrent threads end up hitting on the same connection (though the session ID might save you here, but it is conceivable that a browser could submit two concurrent requests using the same session ID and you could get into situation with transactions or with SQL Server's inability to process more than one command at a time on a given connection). If you "open" the connection, use it and "close" it again, the connection can safely be handed to another thread processing a different command. If the server load gets light, the connection pool is trimmed back automatically and others using that server get better performance. If the server load gets heavy, the pool can grow as needed. However, you'll probably notice that you can support lots of clients with just a few connections. If you have written a traditional client/server application 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. Question 2How does connection pooling work for users with specific connection requirements? 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 their identity.Can connection pooling be used in the latter case? Answer 2Connection 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 something different). 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 open connection, connection pooling would only buy you the ability to reuse a connection that was opened on the first site hit by that user. The pool would keep it alive for a reasonable amount of time and if the user kept interacting with the site, the connection would remain live. If the user stopped interacting with the site, the connection would eventually fall out of the pool and be closed. This still offers some benefits since you code as if you're doing drop/reconnect and 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 the ability to go to lunch and still have the session state there, but not tying 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), and you 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 of people will fall into one or two roles), and you can also enforce certain security 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 could pick a connection out of the pool and change the security context for it, but I don't think that's possible today. REFERENCESFor the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site: http://support.microsoft.com/support/vinterdev/ Additional query words:
Keywords : kbnokeyword kbADO kbDatabase kbVisID kbGrpASP kbGrpMDAC kbDSupport kbADO210sp2 |
|
Last Reviewed: November 16, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |