Connection Pool Fragmentation

I was just reading
http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx about
connection pooling with SQL server and came across the follwoing:
**************
Pool Fragmentation
Pool fragmentation is a common problem in many Web applications where the
application can create a large number of pools that are not freed until the
process exits. This leaves a large number of connections open while consuming
memory, resulting in poor performance.
Pool Fragmentation Due to Integrated Security
Connections are pooled according to the connection string plus the user
identity. Therefore, if you use Basic authentication or Windows
Authentication on the Web site and an integrated security login, you get one
pool per user. Although this improves the performance of subsequent database
requests for a single user, that user cannot take advantage of connections
made by other users. It also results in at least one connection per user to
the database server. This is a side effect of a particular Web application
architecture that developers need to weigh against security and auditing
requirements.
**************
I'm not sure that I understand what this really means. Can someone help me
understand?
In our environment, each user is logging into the web based app with their
own unique id, but the app connects to the db as a single login.
Tia
--
MGWhat matters is how the connection to the database is
handled. If the site is authenticating to the database using
impersonation or the app pool identity and just using the
one login, then the connection is reused. The credentials
being used are the same.
If it were setup to use delegation and integrated security,
when user A logs in one connection is used. When user B logs
in, a different connection would be used. The credentials
being used are different as one is for user A and one is for
User B. In this scenario, you need a connection for each
user.
In terms of weighing the performance vs security and
auditing, when the same credentials are used then it's that
log in that is seen in SQL Server as the user who accessed
or modified the data. You don't know the actual user, you
only know that it was the single account used by the app.
In the second scenario, SQL Server will see the access,
modification as done by the actual logged in users.
-Sue
On Fri, 10 Aug 2007 08:57:13 -0700, Hurme
<michael.geles@.thomson.com> wrote:
>I was just reading
>http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx about
>connection pooling with SQL server and came across the follwoing:
>**************
>Pool Fragmentation
>Pool fragmentation is a common problem in many Web applications where the
>application can create a large number of pools that are not freed until the
>process exits. This leaves a large number of connections open while consuming
>memory, resulting in poor performance.
>Pool Fragmentation Due to Integrated Security
>Connections are pooled according to the connection string plus the user
>identity. Therefore, if you use Basic authentication or Windows
>Authentication on the Web site and an integrated security login, you get one
>pool per user. Although this improves the performance of subsequent database
>requests for a single user, that user cannot take advantage of connections
>made by other users. It also results in at least one connection per user to
>the database server. This is a side effect of a particular Web application
>architecture that developers need to weigh against security and auditing
>requirements.
>**************
>I'm not sure that I understand what this really means. Can someone help me
>understand?
>In our environment, each user is logging into the web based app with their
>own unique id, but the app connects to the db as a single login.
>Tia

Connecting via ODBC to SQLServer 2005 June CTP using MARS

Hello newsgroup,
after searching around the web, some reading and testing I'm still having
problems using MARS via ODBC with the SQLServer2005 June CTP.
I'm trying to enable MARS by connection string, after some looking about
"MARS_Connection=yes" should be the setting. That right?
I found different settings for OLEDB and ADO.NET I don't know why three
different strings are needed instead of one for all.
Anyway, it's not working and I'm getting the "connection is already full
with data from another result" message. I'm using the old odbc client for
SQLServer 2000, is that the problem?
I'd really like to use SQLServer in future, but I need MARS because else the
application would need some restructuring and that's too expensive. Currently
I'm working with two other DBMS and they work fine when it come to this
feature.
Thanks for help,
florian
Hi Florian,
Inorder for MARS to work you will need to use the new SQL Native Client; The
ODBC client for SQLServer 2000 does not support MARS.
ADO.NET, OLEDB and ODBC have different connection strings for MARS inorder
to preserve the naming conventions for connectino attributes bettween the
different providers.
Prior versions will get the 'connection busy' errors if you try to use
implicit multiple connections in and ODBC application.
Chris Lee has written an excellent blog about this here: It describes
migrating from MDAC to the SQL Native Client (very easy) and also about
using MARS
http://blogs.msdn.com/dataaccess/arc...gory/9952.aspx
There's a great technet article here as well
http://www.microsoft.com/technet/pro...marssql05.mspx
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
"Florian B." <Florian B.@.discussions.microsoft.com> wrote in message
news:E4A95505-E345-45FF-A9AC-FFAD3E9E6ACF@.microsoft.com...
> Hello newsgroup,
> after searching around the web, some reading and testing I'm still having
> problems using MARS via ODBC with the SQLServer2005 June CTP.
> I'm trying to enable MARS by connection string, after some looking about
> "MARS_Connection=yes" should be the setting. That right?
> I found different settings for OLEDB and ADO.NET I don't know why three
> different strings are needed instead of one for all.
> Anyway, it's not working and I'm getting the "connection is already full
> with data from another result" message. I'm using the old odbc client for
> SQLServer 2000, is that the problem?
> I'd really like to use SQLServer in future, but I need MARS because else
> the
> application would need some restructuring and that's too expensive.
> Currently
> I'm working with two other DBMS and they work fine when it come to this
> feature.
> Thanks for help,
> florian

Connecting via ODBC to SQLServer 2005 June CTP using MARS

Hello newsgroup,
after searching around the web, some reading and testing I'm still having
problems using MARS via ODBC with the SQLServer2005 June CTP.
I'm trying to enable MARS by connection string, after some looking about
"MARS_Connection=yes" should be the setting. That right?
I found different settings for OLEDB and ADO.NET I don't know why three
different strings are needed instead of one for all.
Anyway, it's not working and I'm getting the "connection is already full
with data from another result" message. I'm using the old odbc client for
SQLServer 2000, is that the problem?
I'd really like to use SQLServer in future, but I need MARS because else the
application would need some restructuring and that's too expensive. Currentl
y
I'm working with two other DBMS and they work fine when it come to this
feature.
Thanks for help,
florianHi Florian,
Inorder for MARS to work you will need to use the new SQL Native Client; The
ODBC client for SQLServer 2000 does not support MARS.
ADO.NET, OLEDB and ODBC have different connection strings for MARS inorder
to preserve the naming conventions for connectino attributes bettween the
different providers.
Prior versions will get the 'connection busy' errors if you try to use
implicit multiple connections in and ODBC application.
Chris Lee has written an excellent blog about this here: It describes
migrating from MDAC to the SQL Native Client (very easy) and also about
using MARS
http://blogs.msdn.com/dataaccess/ar...egory/9952.aspx
There's a great technet article here as well
http://www.microsoft.com/technet/pr.../marssql05.mspx
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
"Florian B." <Florian B.@.discussions.microsoft.com> wrote in message
news:E4A95505-E345-45FF-A9AC-FFAD3E9E6ACF@.microsoft.com...
> Hello newsgroup,
> after searching around the web, some reading and testing I'm still having
> problems using MARS via ODBC with the SQLServer2005 June CTP.
> I'm trying to enable MARS by connection string, after some looking about
> "MARS_Connection=yes" should be the setting. That right?
> I found different settings for OLEDB and ADO.NET I don't know why three
> different strings are needed instead of one for all.
> Anyway, it's not working and I'm getting the "connection is already full
> with data from another result" message. I'm using the old odbc client for
> SQLServer 2000, is that the problem?
> I'd really like to use SQLServer in future, but I need MARS because else
> the
> application would need some restructuring and that's too expensive.
> Currently
> I'm working with two other DBMS and they work fine when it come to this
> feature.
> Thanks for help,
> florian

Connecting To SQLExpress From a VB Console Application

I've been reading through the forums and I have seen that I am not the only person to have trouble simply trying to connect to a database from VB.

Firstly I have created a console application, and from within that application I have created a simple 1 table database and added two rows of data. So I now the database is there and working.

Problem is I keep getting the following error which trying to apply a connection string to an SQLConnection object:

Message: "ExecuteReader requires an open and available Connection. The connection's current state is closed."

The code I am using is as follows:

strSQLConnection = "Data Source=.\SQLEXPRESS;AttachDbFilename=" + Chr(34) + _
My.Computer.FileSystem.CurrentDirectory.ToString() + "\MusicManager.mdf" + Chr(34) + _
";Integrated Security=True;User Instance=True"

Try
conn.ConnectionString = strSQLConnection
...
...

I know the file exists and is in the right location. Why won't SQL Express open the database?

I have successfully created Windows Applications that can connect to a database. What am I doing wrong?

Cheers,

Roy

Did you call Open() on the connection object before trying to execute a command against it?

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.|||Do I feel stupid or what...

That was it. Thanks.

How I missed that I do not know...

Roy|||I can't count the number of times I've forgotten to Open the DB connection or tell the DataReader to Read.

Uriel