6

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Delta76
  • 13,931
  • 30
  • 95
  • 128
  • 1
    You should specify if there is a witness or if this is a manual fail-over configuration (you only get automatic fail over if you have a witness. see http://technet.microsoft.com/en-us/library/ms189852(SQL.90).aspx). You should also check the state of the databases on both the Principal and the Failover. – doug_w Feb 04 '10 at 15:51
  • @doug_w: I checked manually by T-SQL, everything works just fine :) @gbn: I'm using ADO.NET – Delta76 Feb 04 '10 at 15:58

4 Answers4

4

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

Delta76
  • 13,931
  • 30
  • 95
  • 128
  • Yep - connection pool can cause this type of thing with both clusters and mirrors – onupdatecascade Feb 04 '10 at 19:48
  • How do you determine when to clear the pool? Are you surrounding the attempt to open the conn with a try/catch and doing it if opening throws an exception? Or did you find any particular event you could hook onto for a notification of when you might need to clear the pool? Also, you can disable pooling in the connection string but that has performance implications, the same as always clearing the pool before opening a connection would. – quentin-starin Jul 11 '13 at 22:36
  • It was a long time ago, I do not have the code anymore but if I remember correctly I clear the connection pool in the catch exception. Not an ideal solution but it works for me as a demo. – Delta76 Jul 26 '13 at 05:46
  • Our org has a similar situation with a try/catch around any database attempts. The catch compares the error message in part to standard messages associated with failover situation stored in config, such as the "transport-level" error above, or errors about connecting to a database in restore (which is encountered when attempting to connect to a database which has been failed over and the server comes back online). We also have a config flag to enable/disable the connection pool clearing. So we look for these specific errors on DB exceptions, and if any are matched, the pool is cleared. – Alex S. Nov 12 '14 at 22:47
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

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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

Community
  • 1
  • 1
trevdev
  • 164
  • 6
0

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.

taylonr
  • 10,732
  • 5
  • 37
  • 66
  • SQL Server showed that the mirror has become the principal. I tried an T-SQL, it ran well :-s – Delta76 Feb 04 '10 at 16:57
  • Ok, just from playing with failover about a year ago, I know how easy it can be to miss those little things... Don't have an answer for you, sorry. – taylonr Feb 04 '10 at 16:59