Linked Server timeouts
I had a puzzling little problem this week which caused
our remote users unable to access our system in our remote location. We have two SQL Server 2005 SP2
servers on different continents. The link between them is extremely slow around
100kbit. We have a linked server set up on the remote server, let’s call that
REMOTESQL01 pointing to the central SQL Server, let’s call that CENTRALSQL01.
We also have a form of replication set up which I will go into in another blog
post, but that’s not the important point here.
On the Friday evening – great time for a failure, we had some network problems causing an outage of 15 minutes. Once this error had cleared everything appeared to be working fine.
On the Sunday evening we have a batch process which dumps
out a cut of the data from CENTRALSQL01 and then compresses it and sends it to
REMOTESQL01, where a job bulk loads the data in. This is controlled with stored procedures connecting through Linked
Servers.
The job failed because the linked server on REMOTESQL01
could not connect to CENTRALSQL01 – I believe this is related to the
network outage – although according to our infrastructure
dept, everything is back to normal. To illustrate the issue, if I ran the following
on REMOTESQL01:
select * from [CENTRALSQL01].MyDatabase.dbo.myTable
I got the following error:
OLE DB provider "SQLNCLI" for linked server "CENTRALSQL01"
returned message "Unable to complete login process due to delay in opening
server connection".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"SQLNCLI" for linked server "CENTRALSQL01".
For completeness, my linked server settings are:
In the linked server properties, I decided to change the Connection Timeout and see what would happen if that was extended from the default of 20secs to 120secs. After several tests across the slow link, the new timeout period solved our problem. This is only a temporary fix until we find out the root cause on the network.
I hope this little tip is handy to people experiencing Linked Server connection errors across slow WAN links.