When does the .NET Data Provider create a new pool? Your application might not always be able to use an existing connection. If any of the following items change, the .NET Data Provider creates a new pool:
- The ConnectionString property—This property must be the same each time ADO.NET uses the Open method to open the Connection object. Even if the only change is that the arguments are in a different order, the string is different. If you add, change, or remove arguments—even if they simply reflect the default behavior—the property is different, and you get a new pool.
- The User ID (UID) or password—If you use a specific UID for each connection, each UID gets its own pool.
- The process identifier (PID)—Each process, program, component, Web service, or Web application gets its own pool. I discuss this aspect further in a moment.
- The transaction scope or enlistment—If you're using transactions, each transaction scope gets its own pool.
Where are connection pools created? The provider always creates connection pools on the system where the code creating the connection runs: on the client. In a client/server situation (e.g., a Windows Forms application opens a connection to a remote server), the provider establishes the connection pool on the client (local system). In Web service, ASP, ASP.NET, or COM+ architectures, the code that opens the connection runs on a remote server that's hosting Microsoft IIS or Microsoft Transaction Server (MTS). In these cases, the "client" is the code running on the remote code server. Connection pools aren't created on the database server unless it happens to be hosting IIS or MTS.
How does the security setting affect pooling? As I mentioned, if you use a specific UID for each connection, each UID gets its own pool. But if you specify Integrated Security=SSPI in the ConnectionString, you get one pool for your process's User Account. This means that you can't specify a specific role or UID and share the pool that the .NET Data Provider created for another role or user.
Why does the PID affect pooling? Like every database interface since the dawn of time, ADO.NET can't share connections across processes, so each process must have its own connection pool. A client/server application gets its own PID each time you run it. If you have five instances of an application, each gets its own PID, regardless of whether you run the application on one system or five systems. If you run a middle-tier component—such as a Web service, a COM+ component, or a Web application—that component also has its own PID; but in this case, multiple instances are running the same process. This means that a Web service can serve a few clients or a few thousand clients—all of which share the same connection pool. When you're developing your Web service, each time you compile and create a new assembly, the new executable gets a new PID—and a new connection pool. So you could potentially have two nearly identical copies of an executable on your system, but each gets its own connection pool.
What happens when the pool is full? If the pool fills up with connections (the default limit is 100 connections), the .NET Data Provider waits for another instance of the application to close an existing connection. If the .NET Data Provider can't get a connection in time (i.e., within the limit that the Connection Timeout setting specifies), it throws a System.InvalidOperationException with the message Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. You don't want to show this message to your users because it's not their fault and, generally, users can't debug your code (which caused the problem in the first place). When I get this message, I send a message to the Web master offering to fix the problem. Note that ADO.NET uses System.InvalidOperation- Exception for a variety of situations, so you need to parse the Message string to find out what's really wrong.
Prev. page
1
[2]
3
4
next page 
