locked
Do SQL Native Client connections need to be reopened after a complete failure of the principal server? RRS feed

  • Question

  • Hi,
    I have a connectionstring as below...
    DRIVER=SQL NATIVE CLIENT;SERVER=TCP:NYAVDEVINTDCDB1,5550;FAILOVER_PARTNER=TCP:NYAVDEVINTDCDB2,5550;DATABASE=DMINTDC;TRUSTED_CONNECTION=YES;POOLING=TRUE;
    Do SQL Native Clients using this cxn String have to perform a reconnect when the NYAVDEVINTDCDB1 server gets completely disconnected/ loses connectivity from the network? If not, why do I get a...
    [SQL Native Client]Communication link failure
    when this situation arises? 
    I am using SQL Server Standard Edition 9.00.3152.00    SP2     
    Regards
    Hegs
    Monday, February 22, 2010 11:00 AM

Answers

  • btw I found the answer to this question and its actually yes they do need to be reopened..
    • Marked as answer by hegsie Sunday, April 4, 2010 6:48 AM
    Sunday, April 4, 2010 6:48 AM

All replies

  • What is the SQL Server Native Client Service pack? You need to use NativeClient >= 2005.90.2047.00

    In order for automatic failover to occur without disconnect, make sure that your initial database is set to the Mirrored database and not default or master.

    Also make sure you specify TCP as protocol in connection string. For ex: Server=tcp:SRV01; Failover Partner=tcp:SRV02

    http://blogs.technet.com/johnbaker/archive/2005/10/06/412126.aspx will be a good read for you.
    Thanks!
    Tuesday, February 23, 2010 4:40 PM
  • Hi Sakthivel,
    I'm actually using 2005.90.3042.00 of the SQL Server Native Client driver, My user I have confirmed is a sysadmin on both the Principal and the mirror and has the initial database set to my required DB. Also I am using the connection string above and I am not seeing a seamless switchover during failover...
    Regards
    Ben
    Wednesday, February 24, 2010 11:07 AM
  • Can you try adding a timeout period say 10 seconds in the connection string and test the failover?

    When failover occurs, are you able to see that Mirror becomes principal and it is only the application which fails to connect to the new Principal server?
    Thanks!
    Wednesday, February 24, 2010 2:38 PM
  • Hi Sakthivel,
    I have tried it with the timeout to no avail, during the failover I see the mirror become the principal/disconnected which is what I expect, as you say it is just the application using the odbc native client that doesn't failover.  I am in the process of rewriting this code to reconnect on failover, though I was hoping I wouldn't need to do this...
    Regards
    Ben
    Wednesday, February 24, 2010 3:45 PM
  • ah! That should be the last option. before that as a last try, coud you try this sample application code to check failover?
    http://blogs.msdn.com/grahamk/archive/2009/01/16/sample-example-application-to-test-database-mirroring-failover.aspx

    Steps to test Mirroring Failover:
    To test a server failure, just shut down the SQL Server instance, either through SQL Configuration Manager or using SHUTDOWN WITH NOWAIT.
    To test a communication failure, remove a network cable from the server.
    To test a database failure, stop the SQL Server service and rename the underlying .mdf file, and then restart the SQL Server.
    To cause a redo error on the mirror, add a file to the principal database on a driver volume that does not exist on the mirror server.
    Another way to cause a redo error on the mirror is to force the mirror server data file to run out of disk space.
    To force a database shutdown on the principal, force the principal's data file to run out disk space.
    To cause a log buffer hardening to fail on the principal or mirror, force the log file to run out of disk space

    If this works, may be change the provider in your connection string to all other providers which support database mirroring and test it once.


    Thanks!
    Wednesday, February 24, 2010 7:31 PM
  • I'm not really sure that this would be a valid test as I'm working in native C++ against the ODBC not in C#...
    Thursday, February 25, 2010 10:50 AM
  • Before you give up, last try.

    Download SQL Server Samples (corresponding to your SQL Server Platform) from http://www.codeplex.com/Wikipage?ProjectName=SqlServerSamples

    Once you install this, you will get a sample VC++ application to test Database Mirroring at C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Data Access\odbc\MirroringFailover\ location.

    Try testing your Database Mirroring environment with this.
    Thanks!
    Thursday, February 25, 2010 6:43 PM
  • So I've tried this sample application and I'm seeing the same behaviour here as in my testing, just to give you a bit more background.  I'm running the principal and mirror on two separate virtuallized Windows 2003 R2, the client aplications are connecting from identical machines/images and to perform the failover I completely disconnect the principal server from the network/stop the virtualized macine.  This performs the failover of the database but not the underlyig client connections.   I am trying to replicate what would happen in a disaster recovery situation, so performing a manual failover is not a valid test.  Unless I'm missing something fundamental I'm going to have to rewrite literally thousands of lines of code to make this work.  Any other thoughts?

    NB: To enact the test I'm performing you need to physically disconnect the principal machine fromt the network, not peform a graceful failover.

    Regards
    Hegs
    Friday, February 26, 2010 11:30 AM
  • Just to explore this a little further I tried to perform this test with using the failover button on the sample app and I saw similar problems... If I could upload an image I would show you the result of this test...
    Friday, February 26, 2010 11:57 AM
  • I've posted the image to my blog here ..
    Friday, February 26, 2010 12:01 PM
  • btw I found the answer to this question and its actually yes they do need to be reopened..
    • Marked as answer by hegsie Sunday, April 4, 2010 6:48 AM
    Sunday, April 4, 2010 6:48 AM