Failover_Partner dose not work for me!

Question

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

Answer 1

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

Answer 2

许荣华,

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

Answer 3

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.

Answer 4

许荣华,

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

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

I have the same problem!

Answer 9

许荣华,

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

Answer 10

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

Answer 11

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.

Answer 12

sorry， all the testing  databases  on windows2003 server 。

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

the errors of the SQL Server log  is only ' login failed for user sa'.......

Answer 17

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 @)

Answer 18

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.

Answer 19

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 @)

Answer 20

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 @)

Answer 21

Of course,qq or msn ?  

my qq: 2570073280

Answer 22

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);
	}
}
?>

Answer 23

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 @)

Answer 24

hi,

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

thanks.