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

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. 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.|||

All 3 servers are running 9.0.2047sp1/x64 on WS2k3sp1/x64.

I've also tried extending the connection timeout with

EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'120'

but I still get the same error after ~20 seconds (regardless of the value I specify). I've looked at the network traffic and it never seems to attempt to establish a connection to the secondary.

here's the entire script I'm using:

EXEC master.dbo.sp_dropserver @.server= N'MIRROR'

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

EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation compatible', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'dist', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'pub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'rpc out', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'sub', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'connect timeout', @.optvalue=N'1000'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'lazy schema validation', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'MIRROR', @.optname=N'use remote collation', @.optvalue=N'true'

select top 10 * from MIRROR.pubs.dbo.authors

What am I missing?

Piers.

Accessing SQL 2000 servers from 2005 Management Studio

Hi
Do you configure SQL Server 2005 remote connection to be enabled?
Can you show an exact error you are getting?
<PepperFleming@.gmail.com> wrote in message
news:1150642596.033192.219160@.u72g2000cwu.googlegroups.com...
> Hi,
> I have seen many post on this problem, but I have not seen a concise
> answer.
> I have 2 servers running SQL 2000. I have a fresh installation of XP
> and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
> servers using the SQL 2005 Management studio by Named Pipes or by
> TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
> 0, connection refused error.
> the 2000 severs can regsiter each other and a 4th machine with EM on it
> can register them, so I know this isn't a firewall or connection issue
> on the 2000 servers.
> Can anyone shed some light on how to open up my SQL 20005 installation
> to allow it to communicate with the 2000 servers.
> Thanks
> Pepper Fleming
>Hi Uri,
Thanks so much for responding. The message I am getting through the
Management studio when trying to connect to the server running SQL 2000
is:
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. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I have named pipes enabled on my laptop under the SQL Server
Configuration manager.
The message looks a little misleading to me, because the way I read it,
the studio thinks it is connecting to a SQL Server 2005 instance.
Thanks,
Pepper|||One more piece of information if it helps, I can connect to other SQL
2005 servers remotely, this seems specific to SQL 2000 servers.
Pepper|||These may help. Using portqry you should get similar output as below.
http://www.microsoft.com/downloads/...&displaylang=en
http://support.microsoft.com/defaul...kb;en-us;287932
F:\PortQryV2>
*** User Input***
F:\PortQryV2>portqry -i
PortQry Interactive Mode
Type 'help' for a list of commands
Default Node: 127.0.0.1
Current option values:
end port= 80
protocol= TCP
source port= 0 (ephemeral)
*** User Input***
> node 192.168.1.98
Default Node: 192.168.1.98
>
*** User Input***
> q sql
resolving service name using local services file...
TCP port resolved to the 'ms-sql-s' service
IP address resolved to pe1600.dspatrick.local.com
querying...
TCP port 1433 (ms-sql-s service): LISTENING
>
resolving service name using local services file...
UDP port resolved to the 'ms-sql-m' service
IP address resolved to pe1600.dspatrick.local.com
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName PE1600
InstanceName MSSQLSERVER
IsClustered No
Version 9.00.1399.06
tcp 1433
F
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
*** User Input***
> exit
exiting PortQry Interactive Mode...
F:\PortQryV2>
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<PepperFleming@.gmail.com> wrote:
| One more piece of information if it helps, I can connect to other SQL
| 2005 servers remotely, this seems specific to SQL 2000 servers.
|
| Pepper
||||Hi,
I have seen many post on this problem, but I have not seen a concise
answer.
I have 2 servers running SQL 2000. I have a fresh installation of XP
and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
servers using the SQL 2005 Management studio by Named Pipes or by
TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
0, connection refused error.
the 2000 severs can regsiter each other and a 4th machine with EM on it
can register them, so I know this isn't a firewall or connection issue
on the 2000 servers.
Can anyone shed some light on how to open up my SQL 20005 installation
to allow it to communicate with the 2000 servers.
Thanks
Pepper Fleming|||Hi
Do you configure SQL Server 2005 remote connection to be enabled?
Can you show an exact error you are getting?
<PepperFleming@.gmail.com> wrote in message
news:1150642596.033192.219160@.u72g2000cwu.googlegroups.com...
> Hi,
> I have seen many post on this problem, but I have not seen a concise
> answer.
> I have 2 servers running SQL 2000. I have a fresh installation of XP
> and SQL 2005 on my laptop (No Firewall). I cannot register the 2000
> servers using the SQL 2005 Management studio by Named Pipes or by
> TCPIP. Names Pipes throws the error 40 error, and TCP throws the error
> 0, connection refused error.
> the 2000 severs can regsiter each other and a 4th machine with EM on it
> can register them, so I know this isn't a firewall or connection issue
> on the 2000 servers.
> Can anyone shed some light on how to open up my SQL 20005 installation
> to allow it to communicate with the 2000 servers.
> Thanks
> Pepper Fleming
>|||Hi Uri,
Thanks so much for responding. The message I am getting through the
Management studio when trying to connect to the server running SQL 2000
is:
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. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I have named pipes enabled on my laptop under the SQL Server
Configuration manager.
The message looks a little misleading to me, because the way I read it,
the studio thinks it is connecting to a SQL Server 2005 instance.
Thanks,
Pepper|||One more piece of information if it helps, I can connect to other SQL
2005 servers remotely, this seems specific to SQL 2000 servers.
Pepper|||These may help. Using portqry you should get similar output as below.
http://www.microsoft.com/downloads/...&displaylang=en
http://support.microsoft.com/defaul...kb;en-us;287932
F:\PortQryV2>
*** User Input***
F:\PortQryV2>portqry -i
PortQry Interactive Mode
Type 'help' for a list of commands
Default Node: 127.0.0.1
Current option values:
end port= 80
protocol= TCP
source port= 0 (ephemeral)
*** User Input***
> node 192.168.1.98
Default Node: 192.168.1.98
>
*** User Input***
> q sql
resolving service name using local services file...
TCP port resolved to the 'ms-sql-s' service
IP address resolved to pe1600.dspatrick.local.com
querying...
TCP port 1433 (ms-sql-s service): LISTENING
>
resolving service name using local services file...
UDP port resolved to the 'ms-sql-m' service
IP address resolved to pe1600.dspatrick.local.com
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
Server's response:
ServerName PE1600
InstanceName MSSQLSERVER
IsClustered No
Version 9.00.1399.06
tcp 1433
F
==== End of SQL Server query response ====
UDP port 1434 is LISTENING
*** User Input***
> exit
exiting PortQry Interactive Mode...
F:\PortQryV2>
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
<PepperFleming@.gmail.com> wrote:
| One more piece of information if it helps, I can connect to other SQL
| 2005 servers remotely, this seems specific to SQL 2000 servers.
|
| Pepper
||||Thanks Dave,
I will run this, but does anyone know if my problem is unusual or is
this a configuration issue common with a standard SQL Server 2005
installation.
Can you manage SQL 2000 servers from the 2005 Management studio out of
the box?
Pepper