SqlConnection Pooling question

  • Thread starter Thread starter Vance Kessler
  • Start date Start date
V

Vance Kessler

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
 
Vance Kessler said:
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.
Click to expand...

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
 
Thanks for David's quick response!

Hi Vance,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that to avoid too many open connections to a
single SQL server, you would like to use ChangeDatabase method on a single
connection to switch between several databases on the same server. If there
is any misunderstanding, please feel free to let me know.

1. It is not recommended to open a connection on a fixed database and then
issue a ChangeDatabase call. Because when you are using connection pooling
against SQL Server 2000, and you close the connection, it is returned to
the connection pool. The next time the connection is retrieved from the
pool used, the reset connection request piggybacks on the first round trip
to the server and executes before the user performs any operations. From
the time you return a connection to the connection pool after calling the
ChangeDatabase method, until the time the connection is retrieved, the
connection remains active on the database. This will prevent you from
dropping the database because the connection is still active.

2. I'd like to know what is ConnectionReset? Is it a property? Sorry that I
couldn't find it.

3. The disadvantages are just as David mentioned in his post. It might
cause security issues and the users mightaccessing the wrong data. Besides,
the performance not always increases by doing this. If you don't like
connection pooling, you can just add "pooling=false" in the connection
string to disable it.

4. As far as I know, fully qualified table name cannot be handled by
SqlCommandBuilder. So we cannot connect to the same database all the time
and use fully qualified table name. It might also cause security issues.

So, my suggestion is as David said: until you see hard evidence that this
causes a performance problem for you, do nothing. Just use connection
pooling that ADO.NET has provided. it will manage connections to the SQL
server and increases performance.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Thanks for all the advice. I DO like connection pooling I just want
it to be as efficient as possible and not be a burden to the SQL
server. So, I will leave the pooling as is for now.

However, as for the security issue. We use Windows authentication on
this site and cannot create a Windows NT domain user for each
database. Therefore they all have to share a NT user. So the
security issue really does not apply to the pools (anymore than it
does to the system as a whole).

Thanks,
Vance
 
Hi Vance,

It was nice to know that you have had the problem resolved. As we know,
connection pooling really increases performance. Thanks again for sharing
your experience with all the people here. If you have any questions, please
feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top