Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I manage a large scale cloud based product that uses SQL Server as the database engine. The model is designed such that each customer using the product has its own instance of MS SQL Server. I'm needing a way to query, update, and insert in these instances from a centralized SQL instance that is used to handle administration of the instances. I've already determined that creating linked server objects is the way to go, and it's how I've been handling ad hoc updates from these databases already. I'm working on expanding some functionality which will result in a significant increase in the amount and frequency of queries being executed from this centralized administration instance.

So, here is my question: What is the impact of having 100+ linked server objects open all the time from a single SQL instance? Is there any impact at all? Up to this point, my practice with linked servers has been to drop them once I'm done.

share|improve this question

migrated from stackoverflow.com Jan 24 '12 at 17:21

This question came from our site for professional and enthusiast programmers.

    
Interesting problem but off topic on SO. Please try ServerFault.com –  Paul Sasik Jan 24 '12 at 17:01
    
    
Thanks for the link. That might be the answer to my question. –  token Jan 24 '12 at 18:33

2 Answers 2

up vote 4 down vote accepted

Linked servers were a drain on resource, particularly RAM, in the 32bit days - hopefully you've moved on from those days. I think your proposed solution would work, and the "pain" would be at the centralised SQL Server end.

However, linked servers can be a bit problematic sometimes - have you thought about using powershell/vbscript/SMO scripts to query these instances, and then load the data into that centralised SQL Server?

share|improve this answer
    
Just a quick update on my experience here now that a few months have passed. I've not seen any performance issues with having the linked server objects open. Per your comment that there may be memory leak issues I went ahead and created a job that runs daily to close and open all of the connections; takes about 2 seconds to run. I'm not sure if this job is really needed as I've not seen any problems, but it's a simple solution to ensure no problems ever occur, so far so good. –  token Aug 20 '12 at 8:34

There will be an impact on the RAM memory used outside SQL server. This memory defaults to 256MB and is used to load external drivers and assemblies. This can be reconfigured using the -g startup option. I agree with Peter above plus, instead of centralizing the data in an admin server, have you considererd replication instead of linked server. Looks to me its going to be a pain to maintain if you're using linked servers.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.