application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "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.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you