none
Failover_Partner dose not work for me!

    問題

  • Hi:

    i use php_pdo_sqlsrv_52_ts_vc6.dll, php5.2.6, apache 2.2, SQLServer 2005
    and a ServerA as principal server, ServerB as mirror server.

    my php code:
    <?php

    $DB_UID = "sa";
    $DB_PWD = "sa";
    $DB_DSN = "sqlsrv:Server=ServerA;Failover_Partner=ServerB;Database=phptest;";


    try {
    $conn = new PDO($DB_DSN, $DB_UID, $DB_PWD);
    }catch( PDOException $e ) {
    echo "conn error";
    var_dump($e);
    exit();
    }
    ?>

    when i change the role between ServerA and ServerB(that is ServerA become mirror server and ServerB become principal server).
    i execute this code, then throw Exception :SQLSTATE[28000]: [Microsoft][SQL Server Native Client 10.0][SQL Server] user 'sa' login fail.
    but when i restart apache, it works great!

    Can someone let me know why I must restart apache and how to solve the problem?

    Thanks

    2012年2月23日 上午 09:11

解答

  • Hi,

    In short, the issue was caused by named instance SQL Server.
    Here, I will detail the cause to the issue:

    1. The clients use LoginWithFailoverPartner to login the database server when the "Failover_Partner" is provided in the connection string
    2. When the "server" in the connection string is not accessable, the clients will use "Failover_Partner" instead
    3. Once the connection is made, SQL Server downloads the failover_partner to the clients. The "Failover_Partner" in the connection string is ignored.  Please note: the "failover_partner" downloaded to the clients is in server name\instance name other than IP,Port. The clients cache the "Failover_Partner" in memory
    4. When failover happens, the clients try to connect to the "server" from cached connection got from connction pool at first, if it fails, the clients try to connect to "Failover_Partner" got from cache. Please note, it is server name\instace name format
    5. The clients try to connect to the "Failover_Partner" and "server" rounds. If both servers are not accessable, the connection will encounter timeout issue. Meanwhile, from the mirror database server, "Login failed" is logged.

    So when you are using named instance SQL Server, please ensure:

    1. SQL Server Browser service is running
    2. UDP 1434 port is opened from Firewall

    For more information about this topic, please see:
    Making the Initial Connection to a Database Mirroring Session: http://msdn.microsoft.com/en-us/library/ms366348(v=sql.90).aspx
    Connection Retry Algorithm (for TCP/IP Connections): http://msdn.microsoft.com/en-us/library/ms365783(v=sql.90).aspx
    SQL Server Browser Service: http://msdn.microsoft.com/en-us/library/ms181087(v=sql.90).aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    2012年3月23日 上午 10:37

所有回覆

  • Hi 许荣华,

    Could you please have a try to use sqlsrv_connect to connect SQL Server as below?

    $serverName = "Server_A"; 
    $connectionInfo = array( "Database"=>"ExampleDB" 
                             , "UID"=>"UserName" 
                             , "PWD"=>"Password" 
                             , "Failover_Partner" => "Server_B"); 
    $conn = sqlsrv_connect( $serverName, $connectionInfo);
    For more information, please see:
    SQL Server Driver for PHP Connection Options: Failover_Partner.


     


    Stephanie Lv

    TechNet Community Support

    2012年2月27日 上午 03:39
  • 许荣华,

    We have demonstrated the failover scenario according to your description. The code block is ok, and it works after failover, no restart action required. 

    However, we are able to reproduce the error on the original post. The reason to this issue is that, the SQL Server login ‘sa’ with password ‘sa’ is on principal server, ServerA, but is not enabled on the secondary server, ServerB. When failover occurs, the connection will be automatically redirected to the new principal server ServerB. In this case, the authentication can be failed, for the SQL Server login ‘sa’ is not enabled on ServerB. If you restart the ServerB, the database mirroring will be automatically failover to ServerA again, with a witness server configured. At this point, you can connect to the SQL Server successfully, since you are connecting to original principal server, ServerA with ‘sa’.

    That’s the result based on our test.  Please pay attention the server name output from the code below after you restart the new principle using the following code:

    <?php
    echo "##Start to testing the connection<BR><BR>";
    $DB_UID = "sa";
    $DB_PWD = "sa";
    $DB_DSN = "sqlsrv:Server=ServerA;Failover_Partner=ServerB;Database=phptest;";
    try {
    $conn = new PDO($DB_DSN, $DB_UID, $DB_PWD);
    echo "##Connection is fine<BR>";
    $stmt =  $conn->prepare("SELECT @@ServerName");
       $stmt->execute();
       while ($row = $stmt->fetch()) {
         print_r($row);
       }
    }
    catch( PDOException $e ) {
    echo "conn error";
    var_dump($e);
    exit();
    }
    ?>

    Please let me know if you have any question.  


    Stephanie Lv

    TechNet Community Support



    2012年2月28日 上午 09:48
  • Stephanie Lv:

    thanks for your reply.

    first,my code block works when i restart Apache not ServerB.
    second,I sure 'sa' with password 'sa'  on ServerB enabled, but i am not have a witness Server.

    2012年3月1日 上午 05:25
  • 许荣华,

    Could you please have a try to use the latest provider for PHP? Since the code works in our environment, is it possible to provide detail information, with which we can reproduce this issue?

    Stephanie Lv

    TechNet Community Support

    2012年3月1日 上午 08:40
  • Actually, please try the release candidate build located here:
    http://download.microsoft.com/download/C/D/B/CDB0A3BB-600E-42ED-8D5E-E4630C905371/SQLSRV30.EXE

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    2012年3月1日 下午 06:38
  • I try to upgrade PHP Driver 2.0 to PHP Driver 3.0, but it dose not work. 

    I try to change Apache2.2 to IIS7.0, i dose not work.

    finally, I upgrade SQL SERVER 2005 to SQL SERVER 2008 R2, the problem was solved. 

    Thanks.

    2012年3月2日 上午 08:18
  • Hi:

    i just test SQL SERVER 2008, it didn't work too.

    can you tell me, what is SQL SERVER version in your environment ? 

    by testing, just SQL SERVER 2008 R2 can solve the problem.

    I must upgrade my system to SQL SERVER 2008 R2 ?

    Thanks

    2012年3月2日 上午 08:48
  • I have the same problem!
    2012年3月2日 上午 10:13
  • 许荣华,

    I have test the scenario again as the environments below, all of them works successfully.

    php_pdo_sqlsrv_52_ts_vc6.dll
    Microsoft Driver for PHP for SQL Server 2.0
    Apache 2.2.22 on Windows 7
    PHP 5.2.6

    SQL Server versions:
    SQL Server 2005 Enterprise Edition SP1
    SQL Server 2008 Enterprise Edition SP1
    SQL Server 2008 R2 Enterprise Edition SP1

    Could you please provide detail information, also with detail steps about database mirroring configuration, which can help us to reproduce this issue in our environment?


    Stephanie Lv

    TechNet Community Support

    2012年3月7日 上午 05:49
  • our database mirroring conf:

    --server p
    create master key encryption by password = '123456'
    
    create  certificate mirror_11_cer_for_private
    with subject='for mirror',
    start_date='11/11/2000',expiry_date='11/11/2111';
    
    backup certificate mirror_11_cer_for_private 
    to file='c:\mirror_11_cer_for_public';
    
    create endpoint endpoint_mirroring
    state=started
    as tcp(listener_port=7022,listener_ip=all)
    for data_mirroring
    (authentication=certificate mirror_11_cer_for_private,
     encryption=REQUIRED ALGORITHM AES,
     role=all);
     
     
    --exchange cert
    create login mirror_22_login with password='123456';
    create user mirror_22_user for login mirror_22_login;
    
    create certificate mirror_22_cer_for_public
    authorization mirror_22_user
    from file='c:\mirror_22_cer_for_public';
    
    create login mirror_33_login with password='123456';
    create user mirror_33_user for login mirror_33_login;
    
    create certificate mirror_33_cer_for_public
    authorization mirror_33_user
    from file='c:\mirror_33_cer_for_public';
    
    
    grant connect on endpoint::endpoint_mirroring to mirror_22_login;
    grant connect on endpoint::endpoint_mirroring to mirror_33_login;
    
    --server m
    create master key encryption by password = '123456'
    
    create  certificate mirror_22_cer_for_private
    with subject='for mirror',
    start_date='11/11/2000',expiry_date='11/11/2221';
    
    backup certificate mirror_22_cer_for_private 
    to file='c:\mirror_22_cer_for_public';
    
    create endpoint endpoint_mirroring
    state=started
    as tcp(listener_port=7022,listener_ip=all)
    for data_mirroring
    (authentication=certificate mirror_22_cer_for_private,
     encryption=REQUIRED ALGORITHM AES,
     role=all);
     
     
    --exchange cert
    create login mirror_11_login with password='123456';
    create user mirror_11_user for login mirror_11_login;
    
    create certificate mirror_11_cer_for_public
    authorization mirror_11_user
    from file='c:\mirror_11_cer_for_public';
    
    create login mirror_33_login with password='123456';
    create user mirror_33_user for login mirror_33_login;
    
    create certificate mirror_33_cer_for_public
    authorization mirror_33_user
    from file='c:\mirror_33_cer_for_public';
    
    
    grant connect on endpoint::endpoint_mirroring to mirror_11_login;
    grant connect on endpoint::endpoint_mirroring to mirror_33_login;
    
    
    --server w
    create master key encryption by password = '123456'
    
    create  certificate mirror_33_cer_for_private
    with subject='for mirror',
    start_date='11/11/2000',expiry_date='11/11/3321';
    
    backup certificate mirror_33_cer_for_private 
    to file='c:\mirror_33_cer_for_public';
    
    create endpoint endpoint_mirroring
    state=started
    as tcp(listener_port=7022,listener_ip=all)
    for data_mirroring
    (authentication=certificate mirror_33_cer_for_private,
     encryption=REQUIRED ALGORITHM AES,
     role=all);
     
     
    --exchange cert
    create login mirror_11_login with password='123456';
    create user mirror_11_user for login mirror_11_login;
    
    create certificate mirror_11_cer_for_public
    authorization mirror_11_user
    from file='c:\mirror_11_cer_for_public';
    
    create login mirror_22_login with password='123456';
    create user mirror_22_user for login mirror_22_login;
    
    create certificate mirror_22_cer_for_public
    authorization mirror_22_user
    from file='c:\mirror_22_cer_for_public';
    
    grant connect on endpoint::endpoint_mirroring to mirror_11_login;
    grant connect on endpoint::endpoint_mirroring to mirror_22_login;
    
    
    --create database testdb  backup it and  restore on mirror server
    
    --server m
    alter database testdb
    	set partner 
    ='tcp://jxzhu.mirror.com:7022';
    
    --server p
    alter database testdb
    	set partner 
    ='tcp://jxfu.mirror.com:7022';
    
    alter database testdb
    	set witness
    =N'TCP://jx.witness.com:7022'
    
    
    172.16.88.11   jxzhu.mirror.com  server p
    172.16.88.22   jxfu.mirror.com   server m
    172.16.77.33   jx.witness.com    server w
    

    2012年3月14日 上午 02:57
  • I have test the scenario again as the environments below, only SQL Server2008 R2 works successfully.

    php_pdo_sqlsrv_52_ts_vc6.dll 
    Microsoft Driver for PHP for SQL Server 2.0
    Apache 2.2.22 on Windows 2003 server
    PHP 5.2.6

    SQL Server versions:
    SQL Server 2005  SP3
    SQL Server 2008  SP1
    SQL Server 2008 R2  SP1  on windows 2008 server.


    2012年3月14日 上午 03:54
  • sorry, all the testing  databases  on windows2003 server 。
    2012年3月14日 上午 09:41
  • Do you see any connection attempts in your server logs happening on your secondary in the database versions which do not work?

    Cheers,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    2012年3月14日 下午 07:02
  • hi,Jonathan

    i had other testing, the results are as follows:

    win2003server+sqlserver2005sp3
    php failed
    java success

    win2003server+sqlserver2008sp1
    php failed
    java success

    win2003server+sqlserver2008 R2
    php success
    java success

    win2008server R2 + sqlserver2005 sp3
    php success
    java success

    is it the cause of the operating  system version?
    could you have a testing with win2003+sql2005?

    thanks.

    2012年3月15日 上午 08:54
  • Hi,

    It would be much easier to diagnose this if we could see the errors from the SQL Server log.

    Thanks,

    Jonathan


    This posting is provided 'AS IS' with no warranties, and confers no rights.

    2012年3月15日 下午 06:07
  • the errors of the SQL Server log  is only ' login failed for user sa'.......
    2012年3月16日 上午 02:30
  • Hi,

    I implemented an exact same environment(Windows Server 2003, SQL Server 2005 with SP3) with yours, and tried to repro the issue in-house. However, everything works as expected. I meant when I stoped the principal server, the PHP connected to the Mirror(it is now the principal server) correctly without any error.

    There are some other possible causes:

    • The sa is not available from the Mirror server.
    • The error(you mentioned) may be not releted to this issue.

    So, could you please:

    1. Disable sa account from the Mirror server and enable it again
    2. Check if there is any error from the Event Viewer
    3. Deploy the apache server in another server, and try to repro the issue

    In addition, could you please use the SQL Server Profiler to trace the connection to the Mirror server?

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    2012年3月16日 上午 07:50
  • chen jinchun,

      thanks for your reply.

      i have test the scenario again on a new server, with a new login 'phper' which having enough permission,but it still  can't work. there is nothing  about  phper log  from  the SQL Server profiler and the SQL Server logs.  and when i restart my Apache  it works successfully.

    can you tell me , what are the php, Apache  ,ms drivers for php version in your environment? and have a look your php code.

    thanks.

    2012年3月19日 上午 08:23
  • Hi 许荣华,

    I used the following environment:

    php_pdo_sqlsrv_52_ts_vc6.dll
    Microsoft Driver for PHP for SQL Server 2.0
    Apache 2.2.22 on Windows 7
    PHP 5.2.6

    Seems the only difference is Apache is running on Windows 7. Let me try to deploy it on a Windows Server 2003, and have a testing again.

    In addition, is it possible for you to have a remote assistance? It would be helpful to troubleshoot the issue.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    2012年3月19日 上午 08:28
  • Hi,

    I just tested it again with Apache 2.2.22 on Windows Server 2003, it is fine too. I used this code:

    <?php
    echo "##Start to testing the connection<BR><BR>";
    $DB_UID = "sa";
    $DB_PWD = "sa";
    $DB_DSN = "sqlsrv:Server=ServerA;Failover_Partner=ServerB;Database=phptest;";
    try {
    $conn = new PDO($DB_DSN, $DB_UID, $DB_PWD);
    echo "##Connection is fine<BR>";
    $stmt =  $conn->prepare("SELECT @@ServerName");
       $stmt->execute();
       while ($row = $stmt->fetch()) {
         print_r($row);
       }
    }
    catch( PDOException $e ) {
    echo "conn error";
    var_dump($e);
    exit();
    }
    ?>
    Could you please share the exact code you are using?

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)


    2012年3月19日 上午 08:51
  • Of course,qq or msn ?  

    my qq: 2570073280

    2012年3月19日 上午 09:14
  • my code :

    $DB_UID = "phper";
    $DB_PWD = "testpwd";
    $DB_DSN = "sqlsrv:Server=172.16.88.11,5433;Failover_Partner=172.16.88.22,1433;Database=testdb;";
    
    try {
    	$conn = new PDO($DB_DSN, $DB_UID, $DB_PWD);
    }catch( PDOException $e ) {
    	//其他错误
    	echo "conn error";
    	var_dump($e);
    	exit();
    }
    
    //查询$conn
    $query = "SELECT TOP 1000 [id],[name] FROM [testdb].[dbo].[Table]";
    
    $stmt = $conn->query( $query );
    if (empty($stmt)) {
    
    }else {
    	while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){
    		var_dump($row);
    	}
    }
    ?>

    2012年3月19日 上午 09:22
  • Hi,

    In short, the issue was caused by named instance SQL Server.
    Here, I will detail the cause to the issue:

    1. The clients use LoginWithFailoverPartner to login the database server when the "Failover_Partner" is provided in the connection string
    2. When the "server" in the connection string is not accessable, the clients will use "Failover_Partner" instead
    3. Once the connection is made, SQL Server downloads the failover_partner to the clients. The "Failover_Partner" in the connection string is ignored.  Please note: the "failover_partner" downloaded to the clients is in server name\instance name other than IP,Port. The clients cache the "Failover_Partner" in memory
    4. When failover happens, the clients try to connect to the "server" from cached connection got from connction pool at first, if it fails, the clients try to connect to "Failover_Partner" got from cache. Please note, it is server name\instace name format
    5. The clients try to connect to the "Failover_Partner" and "server" rounds. If both servers are not accessable, the connection will encounter timeout issue. Meanwhile, from the mirror database server, "Login failed" is logged.

    So when you are using named instance SQL Server, please ensure:

    1. SQL Server Browser service is running
    2. UDP 1434 port is opened from Firewall

    For more information about this topic, please see:
    Making the Initial Connection to a Database Mirroring Session: http://msdn.microsoft.com/en-us/library/ms366348(v=sql.90).aspx
    Connection Retry Algorithm (for TCP/IP Connections): http://msdn.microsoft.com/en-us/library/ms365783(v=sql.90).aspx
    SQL Server Browser Service: http://msdn.microsoft.com/en-us/library/ms181087(v=sql.90).aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jinchun Chen


    Jinchun Chen
    Forum Support
    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

    2012年3月23日 上午 10:37
  • hi,

    the problem has been solved, thanks very much for Mr. Chen these days of testing and interpretation.

    thanks.

    2012年3月25日 上午 03:06