accesssing mirrored databases via SQLNCLI linked server?

Does anyone know how to configure a mirrored pair as a linked server on a 3rd instance?

Say I have a mirrored database on two servers: PRIMARY and SECONDARY.

I want to create a linked server on a 3rd machine that allows me to access the database on the mirrored pair.

This is what I'm using:

EXEC master.dbo.sp_addlinkedserver
@.server = N'MIRROR',
@.srvproduct=N'',
@.provider=N'SQLNCLI',
@.provstr=N'Server=PRIMARY;FailoverPartner=SECONDARY;'

select count (*) from mirror.pubs.dbo.authors

and it works fine if the database on PRIMARY is alive. however when the mirror has failed over to SECONDARY and PRIMARY is no longer available, I get the following when I try to query the database via the linked server:

OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MIRROR" returned message "An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 10061, Level 16, State 1, Line 0
TCP Provider: No connection could be made because the target machine actively refused it.

As far as I can tell, it doesn't try to contact SECONDARY at all. It seems like SQL Server is ignoring the FailoverPartner attribute.

If I switch PRIMARY and SECONDARY in the connection string (ie @.provstr=N'Server=SECONDARY;FailoverPartner=PRIMARY;') then it works when SECONDARY is online, but not when the mirror has failed back to PRIMARY.

Any ideas?

Piers.

It is quit possible that the connection to the primary take long to fail before making second connection to the secondary. The default timeout value is 15 seconds for a connection. You can try extend the timeout value or you can upgrade to SP1 which has better retry logic that can deal with timeout better.

|||

I was able to reproduce this problem and we're investigating further. Will let you know the results.

|||great! I'd love to know if there's a workaround or patch available.|||

From the documentation at: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dataacc9/html/71b15712-7972-4465-9274-e0ddc271eedc.htm

You must explicitly specify the database to be used by a connection if you want to use this feature in a DSN, connection string, or connection property/attribute. SQL Native Client will not attempt to failover to the partner database if this is not done.

Mirroring is a feature of the database. Applications that use multiple databases might not be able to exploit this feature.

In addition, server names are case insensitive, but database names are case sensitive. You should therefore make sure that you use the same casing in DSNs and connection strings.

So basically you should specify the Database name to which to connect to either in the connection string or through @.catalog=<Database Name> parameter to sp_addlinkedserver. I tried doing that and the repro started working.

Thanks

Waseem