Help and Support
 

powered byLive Search

Recommendations for connecting to databases through Internet Information Services

View products that this article applies to.
Article ID:258939
Last Review:July 7, 2008
Revision:13.0
This article was previously published under Q258939
We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 7.0 running on Microsoft Windows Server 2008. IIS 7.0 significantly increases Web infrastructure security. For more information about IIS security-related topics, visit the following Microsoft Web site:
http://www.microsoft.com/technet/security/prodtech/IIS.mspx (http://www.microsoft.com/technet/security/prodtech/IIS.mspx)
For more information about IIS 7.0, visit the following Microsoft Web site:
http://www.iis.net/default.aspx?tabid=1 (http://www.iis.net/default.aspx?tabid=1)
On This Page

SUMMARY

This article is intended as a "best practices" guide for when you call database components from Web applications in Internet Information Server/Services (IIS), though most recommendations apply to any distributed application that accesses database connections, either locally or remotely.

Back to the top

MORE INFORMATION

When using ADO in ASP, make sure to...

•Always close recordsets and connections.
rs.close
set rs=nothing
conn.close
set conn=nothing
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
176056 (http://support.microsoft.com/kb/176056/) ADO/ASP scalability FAQ
•Open late, close early: Open ADO objects just before they're needed and close them immediately after you're done. This frees resources while other logic is processing.
•Don't create ADO objects in session variables. This effectively bypasses MTX connection and thread pooling. If threads aren't pooled, each object created per user can tie up a thread. Also, if the object isn't specifically closed, a session object variable can live and tie up a thread for the life of a session (20 minutes after the last click).
•Do not pass parameters to the command object in the execute statement.
•If you are not marshalling data through a firewall, instantiate objects with Server.CreateObject. The Server part tells Microsoft Transaction Server to create the object in a Transaction Server package so that resources are pooled. (Note this does not apply to IIS 5.0/COM+.)
•Do not reuse recordset or command variables. Create new ones. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
197449 (http://support.microsoft.com/kb/197449/) PRB: Problems reusing ADO Command object on multiple recordsets

Additional items to consider

•While configuring Open Database Connectivity (ODBC) settings for your data source, use system data source names (DSNs) as much as possible, rather than file DSNs. A system DSN is three times faster than a file DSN.
•Don't put ADO connections in session object. ODBC 3.x does connection pooling automatically for you.
•Use TCP/IP sockets to connect to Microsoft SQL Server if it is running on another computer. Use named pipes if SQL Server is running on the same computer as Active Server Pages (ASP).
•Ensure that a proxy account is used to connect to Oracle because Oracle assigns user context to each thread.
•Ensure that connection objects are created in each ASP page.

PerfMon counters

The Active Server Pages object provides excellent counters to do detailed monitoring of your ASP application. The following points are particularly important:
•Errors-related counters (such as "Errors During Script Runtime") - these should be near zero in value. Any errors-related counters that are significantly larger than zero should be further investigated before proceeding with troubleshooting.
•Requests executing - This is very important because it tells you whether your application is forced to be single-threaded or not. The default worker threads for executing ASP requests in IIS 4.0 is 10 (ProcessorThreadMax in the registry). If the ASP Requests Executing counter returns a value of "1", then your requests are definitely being serialized for some reason. Make sure Web Application debugging is not on because this will serialize your requests. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
216580 (http://support.microsoft.com/kb/216580/) PRB: Blocking/serialization when using InProc component (DLL) from ASP
•Requests queued - If this number keeps increasing continuously, your ASP pages have blocked all threads for some reason and no threads are being released to service additional requests from the queue.
•Sessions total - This is the total number of sessions since the Web service was started. You may want to stop and restart the Web service before a test run to more accurately monitor the total sessions being created for a specific test script run. Make sure that while your script is running this number keeps gradually increasing until it reaches the desired total.

ADO/MDAC

To obtain the latest version Microsoft Data Access Components (MDAC) as well as some "getting started" information, see the following links:
General information
http://msdn.microsoft.com/data/default.aspx (http://msdn.microsoft.com/data/default.aspx)

Pooling in the Microsoft Data Access Components
http://msdn2.microsoft.com/en-us/library/ms810829.aspx (http://msdn2.microsoft.com/en-us/library/ms810829.aspx)

Improve the performance of Your MDAC application
http://msdn.microsoft.com/data/learning/MDAC/ (http://msdn.microsoft.com/data/learning/MDAC/)

Microsoft ActiveX Data Objects (ADO) Programming
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/MicrosoftOfficeDeveloperForumAccessADOProgramming.asp (http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/MicrosoftOfficeDeveloperForumAccessADOProgramming.asp)

Back to the top

Articles on best practices for developing Data Access-enabled Web applications

Data Access

Setting object scope
http://msdn2.microsoft.com/en-gb/library/ms525036.aspx (http://msdn2.microsoft.com/en-gb/library/ms525036.aspx)
176056 (http://support.microsoft.com/kb/176056/) ADO/ASP scalability FAQ
216950 (http://support.microsoft.com/kb/216950/) How to enable ODBC connection pooling performance counters
233299 (http://support.microsoft.com/kb/233299/) Identity and auto-increment fields in ADO 2.1 and beyond
230101 (http://support.microsoft.com/kb/230101/) FIX: Data queries sporadically return empty recordsets
195047 (http://support.microsoft.com/kb/195047/) How to call a parameterized SQL Server stored procedure from ADO
200300 (http://support.microsoft.com/kb/200300/) How to synchronize writes and reads with the Jet OLE DB Provider and ADO

General ASP

243543 (http://support.microsoft.com/kb/243543/) PRB: Do not store objects in session or application
243815 (http://support.microsoft.com/kb/243815/) PRB: Storing STA COM component in session locks to single thread
243548 (http://support.microsoft.com/kb/243548/) Design guidelines for Visual Basic components under ASP
243547 (http://support.microsoft.com/kb/243547/) PRB: ASP does not provide progress notifications to client browsers
243828 (http://support.microsoft.com/kb/243828/) BUG: Session_OnEnd changes security context of InProcess components
158229 (http://support.microsoft.com/kb/158229/) Security ramifications for IIS applications
150777 (http://support.microsoft.com/kb/150777/) Descriptions and workings of OLE threading models
156223 (http://support.microsoft.com/kb/156223/) How to launch OLE servers from ISAPI extensions

Back to the top

APPLIES TO
•Microsoft Active Server Pages 4.0
•Microsoft Internet Information Services 6.0
•Microsoft Internet Information Services 5.0
•Microsoft Internet Information Server 4.0
•Microsoft Internet Information Server 3.0
•Microsoft ActiveX Data Objects 1.5
•Microsoft ActiveX Data Objects 2.0
•Microsoft ActiveX Data Objects 2.01
•Microsoft ActiveX Data Objects 2.1
•Microsoft ActiveX Data Objects 2.1 Service Pack 1
•Microsoft ActiveX Data Objects 2.1 Service Pack 2
•Microsoft ActiveX Data Objects 2.5
•Microsoft Internet Information Services 7.0

Back to the top

Keywords: 
kbhowto KB258939

Back to the top

Article Translations

 

Other Support Options

  • Need More Help?
    Contact a Support professional by Email, Online or Phone.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.