Question

I set up a database mirroring and then used this connectionstring to connect to it:

    Data Source={0};Failover Partner={1};Initial Catalog=AdventureWorks;
    Integrated Security=True;

After adding some data into database, I shutdown the principal server, so the mirror server becomes the principal server. I open the connection again, an get this error:

    System.Data.SqlClient.SqlException: A transport-level error has 
occurred when sending the request to the server. (provider: Shared Memory 
Provider, error: 0 - No process is on the other end of the pipe.)

I thought that with Failover Partner specified in the connection string, ADO.NET would do the work for me. So what should I do now?

Answer 1

Fortunately, I fixed this problem. All I need to do is calling ClearPool method:

SqlConnection.ClearPool(conn);

This method will clear the connection pool. Then, the problem s gone. I m so happy with it.

Thank you, all of you, for your support. :D

Answer 2

I m not an expert about .net stuff but you need the SQL native client (SQLNCLI) to handle failover. "ado.net" may be SQLOLEDB

Up to Windows Server 2003 at least it s not installed by default. It s either standalone or part of SQL client tools, so I suspect you re using SQLOLEDB

Answer 3

After you shut down the principal, did you verify that the failover actually became the principal? If it didn t have automatic failover on the Sql Server, then you the mirror is still a mirror.

Answer 4

We found that this would happen if you didnt have a large enough timeout set.

https://serverfault.com/questions/249589/db-auto-failover-in-c-does-not-work-when-the-principal-server-physically-goes-o

友情链接