Impact of keeping 100+ Linked Server Objects open in MS SQL Server

Question

I manage a large scale cloud based product that uses SQL Server as the database engine. The model is designed that each customer using the product has their 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 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 an impact at all? Up to this point my practice of linked servers has been to drop them once I'm done.

Thank you.

Answer 1

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?

Answer 2

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.