Is there an elegant way to clear out the ADO connection pool used by the SQL
Server State Service following a cluster failover?

We are using a SQL Cluster to store both the ASP Session State database and
our application database.

When the SQL Server cluster is failed-over, any "pointers" to the live
connections stored in the connection pool are made invalid. When the
application tries to reference these "damaged" connections, it throws an
exception, and the pooling mechanism drops the bad connections from the
pool.

In our application, we are using a looping try/catch block to clear out the
bad connections from the pool following a failover, and the users typically
are not affected by the failover as a result of any exceptions in our
application code.

However, we don't have access to the source code for managing the ASPState
connections. It appears as though there is no mechanism for the ASPState
database to clear out the bad connections from the pool following a
failover, and thus the exceptions are affecting users.

Is SQL Server Session State Management even supposed to work with a SQL
Cluster?

Mike Olund
OpenRoad Communications

Same thing happens to us. We use ASP.Net connection pools very heavily and
they have some issues with bad connections after a failover. We also use a
try/catch to reconnect if there is a bad connection. If you find anything
else, please let me know.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com

I support the Professional Association for SQL Server
www.sqlpass.org

"news.microsoft.com" <molund (AT) oroad (DOT) com> wrote

there is support in asp.net 2.0, the supported method in asp.net 1.1 is to
change the connection string (thus not reusing any old connections).

if you want an unsupported method, you can use reflection to call an
undocumented method.

(http://www.sys-con.com/dotnet/article.cfm?id=483)


-- bruce (sqlwork.com)



"news.microsoft.com" <molund (AT) oroad (DOT) com> wrote