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.

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.

Client Side Redirect - Database Mirroring

Hi All,

My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)

I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.

Principal - SQL 2005 Enterprise Edition, SP1

Mirror - SQL 2005 Enterprise Edition, SP1

Witness - SQL 2005 Express, SP1

I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors?

(NB, the user running the code is a sys admin on all 3 servers)

Thanks in advance.

Imports System.Data.SqlClient

Public Class Form1

Dim DCTADS As New SqlClient.SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"

DCTADS.Open()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlClient.SqlCommand

cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"

cmd.Connection = DCTADS

Dim dr As SqlClient.SqlDataReader

dr = cmd.ExecuteReader

MsgBox(DCTADS.DataSource.ToString)

If dr.Read Then

MsgBox(dr("fulldesc").ToString)

End If

dr.Close()

End Sub

End Class

Here is more detailed discussion for client redirect (http://msdn2.microsoft.com/en-us/library/ms366348.aspx). From the code I did not see any problem. One thing to check is to make sure the TCP/IP and "allow remote connection" are enabled on both principal and mirror because by default these two settings are disable after SQL server is installed.

client redirect

Trying just to figure out how this works...how does it know the other server to reconnect to? What does the application see, just a SQL error and the application has to redrive the TX? Can it route TX to the MIRROR and redrive it like Oracle's TAF? Thanks!

Hello

I think you are talking about the transperent Client redirection thats available if you mirror your DB and have a whitness server available.

If so, then the above feature is "only" implemented into the native SQL drivers. If you use other drivers to access SQL you wont be able to use this feature. So basically the driver itself knows that there is a whitness server, and if the main server fails, then the whitness server will tell the driver to use the mirror server which then is the new primary server.

Hope this makes sense

|||

Thanks for this response, I just need a little more details. What happens to the Transaction? Does it carry over like Oracle TAF, or does the app have to redrive it. What does the application on the primary see while the client reconnects to the other databsae, a SQL error?

Thanks1

|||Ok -- one last question -- can you list for me what the native SQL Server interfaces are. I'll assume .NET for sure. OLEDB, ODBC, JDBC, that kind of stuff..is that supported. Can you give me some examples where it redirects and doesn't redirect? Thanks..

client redirect

Trying just to figure out how this works...how does it know the other server to reconnect to? What does the application see, just a SQL error and the application has to redrive the TX? Can it route TX to the MIRROR and redrive it like Oracle's TAF? Thanks!

Hello

I think you are talking about the transperent Client redirection thats available if you mirror your DB and have a whitness server available.

If so, then the above feature is "only" implemented into the native SQL drivers. If you use other drivers to access SQL you wont be able to use this feature. So basically the driver itself knows that there is a whitness server, and if the main server fails, then the whitness server will tell the driver to use the mirror server which then is the new primary server.

Hope this makes sense

|||

Thanks for this response, I just need a little more details. What happens to the Transaction? Does it carry over like Oracle TAF, or does the app have to redrive it. What does the application on the primary see while the client reconnects to the other databsae, a SQL error?

Thanks1

|||Ok -- one last question -- can you list for me what the native SQL Server interfaces are. I'll assume .NET for sure. OLEDB, ODBC, JDBC, that kind of stuff..is that supported. Can you give me some examples where it redirects and doesn't redirect? Thanks..