One of the most useful features of MTS is database connection pooling. MTS can hold a connection open for a specified period of time even after a component is finished using the connection. When another component in MTS needs a database connection, MTS already has the connection open and waiting.
Connection pooling will only be effective if many of the database requests occur under the same database login. Note that the database requests can originate from different users as long as they use the same database login. The reason for this restriction is that the database login is a characteristic of a database connection.
If MTS components are written so that they hold open database connections only for a brief time, a limited pool of database connections can serve many client applications. This saves time and server resources and allows a system to scale more gracefully.
Changing the Driver Timeout
After SQL Server 2000 is installed, you can use ODBC to configure connection pooling. Figure 43.16 shows the Connection Pooling tab of the ODBC Administrator. The important parameter here is the Pool Timeout (in seconds). This parameter tells ODBC how long a connection should be held open in the connection pool after a client application is finished with it.
Figure 43.16. ODBC: Set connection pooling attributes.
To change the timeout for SQL Server, follow these steps: