5

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.

Skyhawk
  • 14,200
  • 4
  • 53
  • 95
token
  • 275
  • 1
  • 3
  • 9

2 Answers2

4

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?

Peter Schofield
  • 1,639
  • 9
  • 11
  • 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 08:34
0

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.

nopol
  • 1
  • 1