Client-side Redirect problem

Hi there.

I have a database mirroring session running, and both principal and mirror are working fine. But when i unplug the network cable from principal server, creating a failover scenario, the client doesn′t get redirected to the mirror.

I get an error saying: "A transport-level error has ocurred when received results from the server. The specified network name is no longer available."

connection = "Data Source=SISAD;Failover Partner=Projecto-SWS1;Initial Catalog=SIERE;Persist Security Info=True;User ID=sa;Password=testing;Connection Timeout=100";

//this method is called many times by a thread, emulating some request to the server

public static void Inserir(int codAutor)

{

SqlConnection sqlConnection = new SqlConnection(connection);

SqlCommand command = new SqlCommand("insert into Autor values(@.codAutor,'test')", sqlConnection);

command.CommandType = CommandType.Text;

command.Parameters.AddWithValue("@.codAutor", codAutor.ToString());

command.CommandTimeout = 200;

sqlConnection.Open();

command.ExecuteNonQuery();

sqlConnection.Close();

}

I think the error is because the ExecuteNonQuery() method return info about the rows affected on the server. But in this case how can i create a failover scenario with client-side redirect ?

Thanks in advance.

I think i′ve resolved the problem...

The issue here is that connections are separated into pools by connection string, when using ADO.NET connection pooling.

It seems that when failover occurs, the attempts to reconnect to the database are retrieving connections from the pool, and the datasource is still the failed principal and not the new principal.

I deactivated connection pooling, and now the clients are connecting to the mirror when when principal fails.

Can someone give me some feedback about this ? It′s seems that i can′t use connection pooling when using client-side redirect.

|||

Hi RDSC,

I believe your assessment is correct. The logic to failover to the partner server only occurs when a new connection is established. Rather than disabling connection pooling altogether, you can clear this individual pool (I believe that there is a clearpool connection property that you can set) and should see the connections succeed. If you do try this, please let me know how it turns out.

Thanks,

Il-Sung
Program Manager, SQL Server Protocols.

|||

Hi there.

You are allright. In my code i can see if there is a connection error, and than set clearpool property. Next time the connection will try to connect to the new principal. So connection pooling can be used with client side redirect.

There is something you should be aware. If clearpool property is used, the state of the connection changes to "Closed", because connections from the pool are set to be discarded, so if you still want to use that connection, you have to open it again.

Thanks for you help Sung Lee.