Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1552

Re: pitfalls of accessing linked server over WAN

Show all headers | View raw

On Wednesday, August 21, 2013 11:47:05 PM UTC-7, Lutz wrote:
> > I tried it against a linked server while the remote box was down to simulate WAN down situation and it takes 50 sec to come back with messages
> 
> >
> 
> > OLE DB provider "SQLNCLI10" for linked server "REMOTEBOX" returned message "Login timeout expired".
> 
> > etc...
> 
> >
> 
> > That long wait is not acceptable for application, I'd like to know if we can possibly decrease that 50 seconds to 5 seconds, in our environment it is reasonable to conclude that link is down if connection is not obtained within 5 sec.
> 
> >
> 
> > I tried
> 
> > EXEC master.dbo.sp_serveroption @server=N'REMOTEBOX', @optname=N'connect timeout', @optvalue=N'5'
> 
> > EXEC master.dbo.sp_serveroption @server=N'REMOTEBOX', @optname=N'query timeout', @optvalue=N'5'
> 
> >
> 
> > but that had no effect on the long wait time for login.
> 
> >
> 
> > Are there any ways to do what I am trying to achieve?
> 
> >
> 
> 
> 
> Maybe this is your problem ...
> 
> 
> 
> http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec917a47-0478-4d1b-8425-958a7f33effe/syssptestlinkedserver
> 
> 
> 
> "Unfortunately there is no way to control this. When you give a 
> 
> non-existant server name, SQL Server is trying to resolve the name of 
> 
> the server using Windows API - this means that depending on the network 
> 
> protocols and your network topology it can take several minutes. For 
> 
> example, DNS name resolution is more than 1 1/2 minutes.
> 
> 
> 
> Btw, you can use Windows Registry tweaks to control some of the timeout 
> 
> values. Search MSKB for more details."
> 
> 
> 
> Can you try it with IP instead of server name?

I do have IP in my actual procedure. 

Thank you guys for the info.
I will look into Erland's suggestion of using CLR.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-20 18:31 -0700
  Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-21 07:20 +0000
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:24 -0700
      Re: pitfalls of accessing linked server over WAN Erland Sommarskog <esquel@sommarskog.se> - 2013-08-22 08:48 +0000
  Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-21 10:02 +0200
    Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-21 17:21 -0700
      Re: pitfalls of accessing linked server over WAN Lutz <news@invalid.invalid> - 2013-08-22 08:47 +0200
        Re: pitfalls of accessing linked server over WAN bradbury9 <ray.bradbury9@gmail.com> - 2013-08-22 00:10 -0700
        Re: pitfalls of accessing linked server over WAN migurus <migurus@yahoo.com> - 2013-08-22 11:32 -0700

csiph-web