Unanswered: Linked Servers Fail
I have a Windows 2000 active/passive cluster. I have configured 6 linked servers using OpenLink Database Proxy Client.
I have had to repeatedly cycle the cluster (stop/restart SQL services) because the linked servers "fail". By failing, I mean that the linked servers stop responding to user queries. The user query simply spins with no activity.
The linked servers are configured in the following manner:
1. I configure a DSN using the OpenLink client
2. I configure SQL Server linked server to point to the DSN
Each node in the cluster has identical DSN entries (same name, same driver, same settings). We have tested failing the cluster from Node 1 to Node 2 and back again. We have tested the failover process with queries actively running (they fail). We are always able to reconnect and resume normal processing after the backup node recovers the SQL server.
The linked server failures don't appear to have any real pattern to them. There seems to be a weak correlation between a failover and failure in the linked servers (the linked servers fail 1 - 2 days later).
This is not making any sense to me at all. I am NOT a fan of linked servers, but I have no choice here: I inherited these and the developers are NOT interested in alternative solutions.
If you have any thoughts on the matter, please drop a line.
Windows 2000 SP4
SQL 2000 SP3 (hotfix 0818)
MDAC 2.7 SP1
OpenLink MultiTier 5.1
Have you hugged your backup today?