SqlConnection Pooling question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Vance Kessler (vkessler_at_peachtree.com)
Date: 03/15/04 

Date: 15 Mar 2004 09:43:36 -0800

I have been reading about ADO.NET's connection pooling and have a
question.

Our situation:
In our system we have a LOT of different user databases. This is a
state less web application running on about 10 web servers accessing a
single SQL 2000 server instance. So, each web request could go to a
different web server. My first assumption is that the connection pool
is local to each web server. I have not found anything that would
contradict that and it makes sense. So, each time a user accesses
their database they could be creating connections on each of the 10
web servers for their particular database. As you can see this could
add up VERY quickly.

1. Would it be advisable to open the connection on a fixed database
(e.g., master) and then issue a changeDatabase() call to the
connection to switch to the actual database desired? This would allow
all the users on a web server to really share connections from the
pool since the connection strings would then match.

2. If I always issue the changeDatabase() call could I set
ConnectionReset to false?

3. What are the disadvantages of doing this? I know it will be a
little slower since I will have to issue the changeDatabase call, but
will be benefits of having truly shared connections be worth it?

4. Another option I just thought of would be to connect to the same
database all the time but instead of changing databases I could fully
qualify access to the tables in all queries by prefixing the database
name to the table names. Will this work with DataSets? Can
SqlCommandBuilder handle a fully qualified table name?

Thanks,
Vance

Relevant Pages

  • Re: ADO Connection Timeout
    ... so what happens when a connection failure forces one station to revert ... to a local database? ... Further, you *will* have contention issues, Jet does not support record ... to the central server, but you are willing to live with periods where it ...
    (microsoft.public.data.ado)
  • Re: Transfer Excel file from a Web Server to Local Server
    ... Have your web administrator create an System DSN name in the ODBC control ... panel on the web server then try it using the name they create. ... I was trying to create a new database connection through Form Properties. ...
    (microsoft.public.frontpage.programming)
  • Kerberos timout with IIS6, ASP.Net and SQLServer
    ... We have a traditional ASP.Net 1.1 web site accessing a SQL2000 database ... using delegation and a trusted connection. ... fully authenticated through Kerberos and Integrated ... The connection with the web server works fine, ...
    (microsoft.public.inetserver.iis.security)
  • Re: ADO Connection Timeout
    ... When the first test is run, the results are stored in the central database. ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)
  • Re: ADO Connection Timeout
    ... to the central server, but you are willing to live with periods where it ... i.e. a local database or even a text file. ... to function until the connection can be restored to the server. ...
    (microsoft.public.data.ado)