Failover causes a transport-level error - ms-sqlserver

http://dbaspot.com/forums/ms-sqlserver/141805-failover-causes-transport-level-error.html

Issue:

 

We have IIS connected to SQL 2005 with failover.

When we try a failover scenario (just stoping service on sqlserver1)
We refresh the aspx page and it fails with:
System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)

Then when we hit the browser refresh button again and it then works!

So our customers have to refresh the page manually to get it to work.
I could try/catch around cn.Open() and then re cn.Open() upon a failure but
it seems like failover should be automatic.

 

Solution:

This is actually an issue with the connection pool. If a connection is in
the connection pool when a failover happens, then it will still think the
connection is valid. When you then try to open the connection you get the
dreaded general network error instead of a valid connection. As soon as
this error happens all other connections in the connection pool are dropped
and new connections will succeeded. Microsoft recommends that you catch the
errors on opening the connection and retry.

Hope this helps.

-Stephen Mills

"dev648237923" wrote in message
news:eOIDBHuRHHA.3316@ TK2MSFTNGP02.phx.gbl...
We have IIS connected to SQL 2005 with failover.

When we try a failover scenario (just stoping service on sqlserver1)
We refresh the aspx page and it fails with:
System.Data.SqlClient.SqlException: A transport-level error has occurred
when sending the request to the server. (provider: TCP Provider, error: 0 -
An existing connection was forcibly closed by the remote host.)

Then when we hit the browser refresh button again and it then works!

So our customers have to refresh the page manually to get it to work.
I could try/catch around cn.Open() and then re cn.Open() upon a failure but
it seems like failover should be automatic.

Thank you!

 

 

Thanks for the info:
I think what is happening is:
-Assume no failover and page working normally to start
-A failover occurs
-Do a Page postback so now the page calls cn.Open
-cn.Open always works regardless (I assume it connects to the pool)
-when we try cmd.ExecuteReader() we then get an exception
(because cn is open to the pool but the pool is no longer correctly
connected)

So it looks like the place I have to test is when I do a cmd.ExecuteReader()
instead of at the cn.Open.

I do the following that seems to work but I wish there was a way to detect
this outside of the app code (that is that the failover would be seamless to
the app.

Soes the below seem corerct (I use it and it works but does not seem the
best!)

private void ddlSectorQuery()
{
string sql =
"SELECT id, name from table1";
SqlCommand cmd = new SqlCommand(sql, cn);
SqlDataReader dr = null;
try
{
dr = cmd.ExecuteReader();
}
catch
{
try
{
if (cn.State == ConnectionState.Open)
cn.Close();
cn.Open();
dr = cmd.ExecuteReader();
}
catch
{
Response.Write("An error occured. Please
href='Login.aspx'>re-login ");
Response.End();
return;
}
}

Thanks for your ideas and time!

 

Hello Matt,

I agree that disabling connection pool shall be a easy workaround here.
However, you could also use Clearpools()/Clearallpools() to clear the bad
pools after failover before reconnecting. These two methods were added to
ADO.net 2.0 to give users more control on the pool. We did not have this
functionality in the older version and we basically had to reset IIS for
clearing the pool. The reason why we don't check for invalid connections in
the pool is that it would
offset the performance gains that we get using the connection pool. From


Removing Connections
The connection pooler periodically scans connection pools looking for
unused connections that were not closed by Close or Dispose, and reclaims
those it finds. If your application does not explicitly close or dispose of
its connections, it can take quite a while for the connection pooler to
reclaim them, so it is best to ensure that you explicitly call Close and
Dispose in your connections.The connection pooler removes a connection from
the pool after it has been idle for an extended period of time, or if the
pooler detects that the connection with the server has been severed. Note
that a severed connection can be detected only after attempting to
communicate with the server. If a connection is found that is no longer
connected to the server, it is marked as invalid. Invalid connections are
removed from the connection pool only when they are closed or reclaimed.If
a connection exists to a server that has disappeared, it is possible for
this connection to be drawn from the pool even if the connection pooler has
not detected the severed connection and marked it as invalid. This is the
case because the overhead of checking that the connection is still valid
would eliminate the benefits of having a pooler by causing another round
trip to the server to occur. When this occurs, the first attempt to use the
connection will detect that the connection has been severed, and an
exception is thrown.

For some more discussion on this subject please refer to:


This also works fine against standalone servers when the server is cycled.
I understand that it might bring some code change and it is not automatic.
Your feedback is taken seriously and is routed to the product team. I also
encourage you submit via the link below

http://lab.msdn.microsoft.com/produc...k/default.aspx

Please let's know if you have any further comments or feedback. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.