Using Database Connection Pooling

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:

1. Open ODBC from the Control Panel. Click the Connection Pooling tab.

2. Double-click SQL Server in the ODBC Drivers box.

3. Select Pool Connections to this driver. The connection pool timeout defaults to 60. For this test, change it to 120. Click OK.

4. Note that the Pool Timeout for SQL Server has changed to 120 in the ODBC Drivers list. Click Apply.

5. Stop and restart the MS SQL Server service.

NOTE

Always remember to restart SQL Server after changing the connection pooling timeout setting in ODBC. This action will enable the changes to take effect.