Only when Principal database is brought down, mirroring works, but client cannot connect to failover db

Question

Env: 

---------

PricipalDB, MirrorDB and WitnessDB

 

Driver: Microsoft SQL Server 2005 JDBC Driver

Driver version: 1.2.2828.100

 

Database: Microsoft SQL Server

Database version: 9.00.3042

 

PricipalDB is configured to run on dynamic port

MirrorDBis configured to run on a static port

 

Connection url:

"jdbcqlserver://PricipalDB\\Inst01;databaseName=myDB;failoverPartner=MirrorDB\\Inst01;loginTimeout=5;network=DBMSSOCN;integratedSecurity=true"

 

 

 

 

Situation:

--------------

Using MMC console,

a) When i switch PricipalDB to failover to MirrorDB - Client can failover to MirrorDB

b) When i switch MirrorDB to failover to PricipalDB - Client can failover to PricipalDB

 

c) When i bring down MirrorDB, PricipalDB can form quorum with WitnessDB and failover works

    - Client can failover to PricipalDB

 

d) When i bring down PricipalDB, MirrorDB can form quorum with WitnessDB and failover works

    - Client CANNOT failover to MirrorDB

 

     The exception i get is "[com.microsoft.sqlserver.jdbc.S
QLServerException: The connection to the named instance  has failed. Error: java.net.SocketTimeoutException: R
eceive timed out.]"

 

    (This issue looks similar to the one in http://forums.microsoft.com/technet/showpost.aspx?postid=1003040&siteid=17 )

 

Been scratching my head for last 3 days, verified the following,  still scenario d) does not work :

i) SQL Browser service is up on both PricipalDB  and MirrorDB

ii) For remote connections, tried

- Using TCP/IP only, and

- Using both TCP/IP and named pipe

iii) The "ProtocolOrder" as in following registry key  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
 is :

   sm

   tcp

   np

   

   As per the following http://support.microsoft.com/kb/328383, tried adding DBMSSOCN in the connection string to force

   TCP/IP

 

 

Anything else i can try?

 

Thanks, MP101

 

 

 

Answer 1

have you setup failover partner connectivity in client connection string.

some thing like this "Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks2008R2; Network=dbmssocn"