Problem when failing over to mirrored database

I'm having a problem with SQL2005 Database mirroring.

I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.

About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.

I read in this article, http://msdn2.microsoft.com/en-us/library/ms366199.aspx, that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.

I would appreciate any help. Thanks

Luis Bonilla

Here is a sample of my code:

Dim k As Integer = 1
Dim constring As String = "Server=SVR01.XXX.XXX.com\InstanceName;Failover Partner=SVR02.XXX.XXX.com\InstanceName;" _
& "Database=test;" _
& "Integrated Security=SSPI;"

Do Until k = 60
Using con As New SqlConnection(constring)

Dim cmd As New SqlCommand()

Try
con.Open() '<--This is where the exception occurs
Catch ex As SqlException
Dim en As String = ex.ErrorCode
Dim em As String = ex.Message
Dim emm As String = ex.Number
Dim enn As Integer = ex.State
End Try

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

cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter" & CStr(k) & "','" & con.DataSource & "')"

Try
cmd.ExecuteNonQuery()
Catch
lblresult.Text = "Results = Error with record number " & CStr(k)
End Try

con.Close()
cont:
End Using
Sleep(1000) 'For testing
k = k + 1 'For testing
Loop

Here is the error information that I'm getting whenever I failover to my failover partner.

ErrorCode = -2146232060
Error Message = "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)"
Error Number = -1

I have already verified that Remove Connections is enabled on both SQL servers.

Any ideas?

|||

Can you connect to the 2nd server (the failover partner) from your app? Can you try temporarily changing the connection string by marking the 2nd server as primary to test it? This will eliminate any network layer level configuration problems.

Thanks

Waseem

|||

Yes, I can connect to the failover server.

If I mark the 2nd server as the primary and the 1st server as the failover failover works fine ONLY ONCE IF the primary role holder is the 1st server. But if failover happens again the same SQLException error occurs.