April 4, 2008 at 4:15 pm
I've written a small VB .Net app 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 the new mirror, I see a string of "18456 Login failed for user 'mipacsclient'"
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. 15 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=DB2\PACS,12345;Failover Partner=DB3\PACS,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.
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
April 6, 2008 at 8:10 am
I found this article that says the old connection may be timing out before attempting to connect to the failover partner..
It is worth a look.
April 6, 2008 at 9:05 am
The one thing that seems to be missing from your code is retry logic.
Here's an example of how you could use a loop to do that.
Dim iRetry as Integer = 1
Dim bDone as Boolean = false
Do
Try
iRetry +=1
If iRetry > 1 Then System.Threading.Thread.Sleep(2000)
' open connection
' execute command
' close connection
bDone = true
catch Ex as Exception
End Try
Loop Until (bDone = true) Or (iRetry >= 4)
April 6, 2008 at 9:27 am
I found this article that says the old connection may be timing out before attempting to connect to the failover partner..
It is worth a look.
It looked promising, but the HotPatch given in the KB article didn't have an effect.
I appreciate the lead though...
April 6, 2008 at 9:35 am
The one thing that seems to be missing from your code is retry logic.
Right now I'm re-trying manually (i.e. clicking the button again to try again). Eventually, yes, I'd make the code a little smarter.
If I wait long enough between re-tries, it will work (and connect to the new primary). Or if I change the connection string (e.g. just change the Application Name) and then try again it works. Or, I can just quit my app and restart it and then it will find the new primary.
This leads me to believe something about the original connection is being cached, and I don't know what it is that needs to be cleared, or how to clear it.
Thanks,
Len
April 6, 2008 at 10:23 am
April 6, 2008 at 5:05 pm
Have you tried closing the connection in your catch block?
Good suggestion.
I just tried though, and it didn't help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply