Error when failing over to mirrored database  
Author Message
Luis Bonilla





PostPosted: .NET Framework Data Access and Storage, Error when failing over to mirrored database Top

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://www.hide-link.com/ , 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 constring As String = "Server=SVR01.x.x.com\InstanceName;Failover Partner=SVR02.x.x.com\InstanceName;" _
& "Database=test;" _
& "Integrated Security=SSPI;"

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

Dim cmd As New SqlCommand()

Try
con.Open() '<------This is where the SQL Exception error is occurring
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
con.Dispose() 'Attempt to cleanup and reinitiate connection
con.ConnectionString = constring 'Attempt to cleanup and reinitiate connection
con.Open() 'Attempt to cleanup and reinitiate connection
End Try

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

Try
cmd.ExecuteNonQuery()
Catch
lblresult.Text = "Results = Error!!!"
GoTo errhandler
End Try

lblresult.Text = "Database responded successfully"

con.Close()

GoTo cont
errhandler:
con.Close()
con.Dispose()

cont:
End Using
Sleep(1000) 'For testing
k = k + 1 'For testing



.NET Development16  
 
 
Luis D. Rojas





PostPosted: .NET Framework Data Access and Storage, Error when failing over to mirrored database Top

hi,

The most common errors at the time of opening a connection is caused because an error in the connection string. In this case the more significant problem I found is that you are using Dispose for closing the connection - which is not bad -, but if you do dispose for a connection object It will call the close method for the connection but also it will remove the the object from memory, so you will have to do the instanciation again. I mean you will have to the con = new SqlConnection, otherwise you will reicieve an error.

By the way, try to handle the errors in catch blocks. Is hard to maintain if you use goto and error tags

Best Regards



 
 
Luis Bonilla





PostPosted: .NET Framework Data Access and Storage, Error when failing over to mirrored database Top

Thanks for the response.

I tried again by using the Close method and got rid of all code where I was using the Dispose method but that still didn't work.

I don't think it's something wrong with the connection string because if I reverse the roles of the Primary Database holder then the failover works fine.

For example, let's say that the currently holder of the database is SVR02 and the failover partner is SVR01. If I use the same connection string

Server=SVR01.x.x.com\InstanceName;Failover Partner=SVR02.x.x.com\InstanceName;Database=test;Integrated Security=SSPI;

When I start up my app the application tries to connect to SVR01 because that is stated as the primary sever in the connection string but that fails because it's not the current holder so my application connects to SVR02 with no problems. Once it connects it works fine and I write records to the database perfectly fine and if I even fail the mirror over to SVR01 while my application is writing records it still continues to work.

Any other ideas