Error After Db Failover If Client App Already Connected To Db

The following looks very much like the problem reported for the JDBC driver.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=688897&SiteID=1
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=191767

----------------------------

I've written a small VB .Net app using Visual Studio 2005 to test mirroring from the client's perspective.

I have a button that does the following: 1) opens a connection to a sql 2005 database (SP2), 2) uses the connection, 3) closes the database connection.

After running this code, but while the client app is still running, I failover the database to the failover partner.

When click my button again, I get the following error: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)". Checking the Event log on what is now the mirror, I see a string of "18456 Login failed for user 'testuser'"

If I close the app and launch it again, it does the right thing (and connects to the new primary). Also, instead of closing/re-opening the app, if I wait long enough (e.g. 10 minutes), then try again, it connects successfully to the new primary. Finally, instead of closing/re-opening the app if I tweak the connection string (e.g. change the application name) then try to connect again, that also works.

Based on articles I've read online, I've disabled the connection pool in my connection string, but that didn't solve my problem (although I'm getting a different error than if pooling is on).

Here is the connection string I am using:

Data Source=DB1PACS,12345;Failover Partner=DB2PACS,12345;Pooling=False;Initial Catalog=PACSTest_Client;Integrated Security=No;User ID=testuser;Password=*****

Here is the code snippet that I run before and after the failover. Based on the fact that I'm not using Pooling, the ClearAllPools() if overkill. Any help would be appreciated; I've been researching this for days without luck.

I can keep clicking the button that runs this code, and it keeps failing. If I wait long enough (e.g. 10 minutes) and then click the button again, it works. Or I could quit/restart the app, or tweak the connection string.

Dim TestClient_Connection As New SqlClient.SqlConnection(txtConnectionString.Text)

If Me.DataGridView1.Rows.Count > 0 Then Me.Test_ClientDataSet.Clear()

Try

TestClient_Connection.Open()

Me.User_TTableAdapter.Connection = TestClient_Connection
Me.User_TTableAdapter.Fill(Me.Test_ClientDataSet.User_T)

TestClient_Connection.Close()
TestClient_Connection.Dispose()
SqlConnection.ClearAllPools()

Catch sqlex As SqlClient.SqlException

txtErrorMessage.Text = "Error State: " _
+ sqlex.Errors.Item(0).Number.ToString _
+ " Message: " + sqlex.Message

End Try


Similar to the JDBC driver article I mentioned, when using the connection string above (where DB1 is the principal and DB2 is the failover partner) if DB2 is the actual principal when I initially query and close the database connection, then after failover to DB1 my application reconnect just fine.


Thanks