connection pooling across multiple applications | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

connection pooling across multiple applications

Hi there, I have an SQL application that requires several external processing applications. These applications all run on separate machines than the SQL server, and they all launch in bursts. We are running into a problem with TCP connections being eaten up on the SQL server because connections are being created and dropped too quickly. We’ve adjusted the TCP connection timeouts in the OS on the SQL server and all that, but I want a better solution. I’m wondering if there is any way for multiple applications that use the same connection string to benefit from ODBC connection pooling or OLE DB resource pooling, or is pooling restricted to multiple connections made from within the same application? Don’t bother suggesting we revise our application and the way it works, this is something we’ve fought internally over for months. Also, finding the answer to this connection pooling question would be quite beneficial for future projects as well. Thanks in advance!
I am not familiar with any way to use connection pooling with multiple applications. Generally speaking, connection pooling requires that the exact same connection (user, security, etc) be the same for connection pooling to work. —————————–
Brad M. McGehee, SQL Server MVP
Thanks for your response Brad. Let me explain further. Each instance of this application is doing the same thing… processing records in a queue table. The connection string is exactly the same for each instance of the application, the issue seems to be that each instance is a separate application. For example, we have 3 machines… 1 SQL server and 2 processing servers. Each processing server will launch a maximum of 3 instances of the processing application. In some cases, when one instance completes and exits, the next one will start immediately. The problem is that the next instance seems to create a new connection, even though its using exactly the same connection string (to the letter). I’d love for these new instances to use a connection from the pool. Thanks
As Brad mentioned I don’t think connection pooling works with different applications OR diffent instance of the same application with same string even they are running on the same server…. When you spin another instance of the same application it spins new connections to the db and I don’t think there is any way to getrid of this… MohammedU.
Moderator
SQL-Server-Performance.com
Hmm… ok thanks. What about using an ODBC DSN? Would connecting to a defined DSN allow for connection pooling between separate applications on a single server? Thanks
]]>