Post reply

DB auto failover in c# does not work when the principal server physically goes offline

  • March 20, 2011 at 2:37 pm

    #248515

    Hey guys

    I'm setting up DB auto failover in C# with SQL Server 2008 and I have a 'high safety with automatic failover mirror' using a witness setup and my connection string looks like

    "Server=tcp:DC01; Failover Partner=tcp:DC02; database=dbname; uid=sewebsite;pwd=somerndpwd;Connect Timeout=10;Pooling=True;"

    During testing, when I turn off the SQL Server service on the principal server the auto failover works like a charm, but if I take the principal server offline (by shutting down the server or killing the network card) auto failover does not work and my website just times out.

    I found this article (http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/8daef498-e1f8-45f4-aa43-3dd0c4f8ba99) where the second last post suggests that its because we are using named pipes which does not work when the principal goes offline, but we force TCP in our connection string.

    What am I missing to get this DB auto failover working?

  • March 28, 2011 at 5:22 am

    #1304283

    Guess the quorum between the witness and mirror server instance was not formed.

    did you query sys.database_mirrorring table to check states of each participant ?

    Cheers

    Sat

    Cheer Satish 🙂

  • March 29, 2011 at 10:12 am

    #1305000

    I'm curious why you're doing this in C# and not just using the interface in SSMS?

    Do you see anything in the SQL logs like this:

    The mirroring connection to "TCP://myserver.mydoamin:5022" has timed out for database "mydatabase" after 10 seconds without a response. Check the service and network connections.

    (this shows that the witness or mirror see the failure.)

    If not I would check your code. I've setup many mirroring sessions with a witness using SSMS in a very troubled network environment and haven't had any failover issues (where it didn't failover as expected).

  • March 29, 2011 at 8:46 pm

    #1305234

    I've seen this issue before, we ended up having to work around it. The closest I got to an explanation was this: http://support.microsoft.com/kb/912151, may not apply in your case...

  • March 30, 2011 at 4:46 am

    #1305327

    hey

    Thanks, thats pretty much right on the money.

    Here is the solution that I found with MS

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply