Knowledge Base

Recommendations for connecting to databases through Internet Information Services

Article ID: 258939

Article Last Modified on 12/3/2007

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

This article was previously published under Q258939

We strongly recommend that all users upgrade to Microsoft Internet Information Services (IIS) version 6.0 running on Microsoft Windows Server 2003. IIS 6.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

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.

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 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 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 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

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

Improve the performance of Your MDAC application
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

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

176056 ADO/ASP scalability FAQ

216950 How to enable ODBC connection pooling performance counters

233299 Identity and auto-increment fields in ADO 2.1 and beyond

230101 FIX: Data queries sporadically return empty recordsets

195047 How to call a parameterized SQL Server stored procedure from ADO

200300 How to synchronize writes and reads with the Jet OLE DB Provider and ADO

General ASP

243543 PRB: Do not store objects in session or application

243815 PRB: Storing STA COM component in session locks to single thread

243548 Design guidelines for Visual Basic components under ASP

243547 PRB: ASP does not provide progress notifications to client browsers

243828 BUG: Session_OnEnd changes security context of InProcess components

158229 Security ramifications for IIS applications

150777 Descriptions and workings of OLE threading models

156223 How to launch OLE servers from ISAPI extensions

Keywords: kbhowto KB258939

Microsoft KB Archive/258939

Contents