Re: SqlConnection Pooling question

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: David Browne (meat_at_hotmail.com)
Date: 03/15/04 

Date: Mon, 15 Mar 2004 12:02:20 -0600

"Vance Kessler" <vkessler@peachtree.com> wrote in message
news:aeaf260b.0403150943.7fefea37@posting.google.com...
> 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.

No. You should connect each user with a set of credentials which allow them
access to only their database. Each web server will have a couple of
connections in the pool for each database. Until you see hard evidence that
this causes a performance problem for you, do nothing. Don't compromise
security and risk users accessing the wrong data by using credentials which
allow access to multiple databases.

David

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: Where is the database connection pool locating?
    ... authentication. ... Pool Fragmentation Due to Integrated Security ... Connections are pooled according to the connection string plus the user ... Although this improves the performance of subsequent database ...
    (microsoft.public.dotnet.framework.aspnet)
  • 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)