Solved

Passive Cluster Drops Linked Server Connection

Posted on 2009-07-07
3
473 Views
Last Modified: 2012-05-07
I have a passive cluster with SQL 2005, running on 2003 Enterprise Server, I am attempting to connect a linked server which is also running SQL 2005, on 2003 Enterprise Server. I can successfully connect using the SA username and password, but within an hour I permanently lose connection to the linked server from the clusterd environment. I have verified that both servers in the cluster have the link server set up.

In recap - Server A is a single instance server running SQL 2005 on 2003 Ent, I am attempting to connect to it from a passive clustered SQL 2005 on 2003 Ent environment. I lose connection within an hour, and have to completely rebuild the linked server connection.
0
Comment
Question by:concretesailors
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 24796621
Hi concretesailors,
This does not sound right at all, is there some kind of server level trigger or task that has been configured to remove things like this for maintenance purposes or the like?  I can think of nothing that would do this automatically.
The code below queries the servers view in the master database, when you are setup you should see it appearing in here.  Does anyone else have access who might want to remove these?  Have you tried adding the linked server details to another server to confirm the same does not happen there?

SELECT * FROM master.sys.servers order by name

Open in new window

0
 
LVL 7

Accepted Solution

by:
concretesailors earned 0 total points
ID: 24797203
Once I have made a connection through the linked server with SA, the other SQL user can access the linked server. (Again, the linked server is a stand alone server and single instance, but the connection is originating from the cluster; active-passive).

So for now, I have created a small job that queries the linked server every minute under SA to keep the connection open. This is a weird work around, but seems to be keeping the connection open.

I did run your query, and the linked server does appear from the cluster's master database.
I have included the result set from the query, but to me it appears to be correct.



SQL-Server-2005-Q-24549742.txt
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 24798599
If the connection remains stable try increasing the time period of your job doing the query - I would be interested to know when/if it disappears...
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question