CODEWITHSUNDEEP
sql-serverado.netconnection-stringfailoverhigh-availability
Deltax76
Deltax76

Reputation: 14263

How to connect to mirrored SQL Server after failover?

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?

Upvotes: 6

Views: 6251

Answers (4)

trevdev
trevdev

Reputation: 164

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

Upvotes: 0

Deltax76
Deltax76

Reputation: 14263

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

Upvotes: 4

taylonr
taylonr

Reputation: 10790

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.

Upvotes: 0

gbn
gbn

Reputation: 432180

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

Upvotes: 2

Related Questions