Using linked servers, OPENROWSET and OPENQUERY

re: Using linked servers, OPENROWSET and OPENQUERY

NULLgarity — Tue, 16 Mar 2010 16:29:30 GMT

Couldn't agree more. Linked servers should only be for ad-hoc access, not for production-delivered code.

re: Using linked servers, OPENROWSET and OPENQUERY

Linchi Shea — Wed, 17 Mar 2010 03:19:55 GMT

> Linked servers should only be for ad-hoc access, not for production-delivered code.

Are you kidding?

re: Using linked servers, OPENROWSET and OPENQUERY

Adam Machanic — Wed, 17 Mar 2010 14:01:45 GMT

Alternate solutions might have more potential to scale, but at what cost to develop and maintain? Linked servers are cheap and easy to implement, and not everyone has the time or resources to invest in complex solutions using Service Broker or middle tier applications. Sometimes--actually, most times--getting -something- up and working today is more important than finding the perfect solution in weeks or months.

Microsoft should invest more time in making linked servers work better when both ends are SQL Server. Every single customer I visit makes extensive use of linked servers, for better or for worse, and almost never with remote sources other than SQL Server. This is not going to go away any time soon, and use cases are certainly not limited to non-production purposes. Quite the opposite, actually.

re: Using linked servers, OPENROWSET and OPENQUERY

NULLgarity — Wed, 17 Mar 2010 17:59:52 GMT

I wasn't kidding. However, re-reading what I wrote and Adam's subsequent comments, I should have phrased it differently and not made such a sweeping generalization.

I should have said that I "try" to only use linked servers for ad-hoc type access and that I "try" to avoid using them in production code. I also could have said that I am skeptical when I see a linked server-centric architecture by design (versus as a workaround).

I don't disagree with anything that Adam said. I also have found SSIS and snapshot replication to be cheap alternatives to linked servers in some cases, depending on how the linked servers are being used and the requirements.

re: Using linked servers, OPENROWSET and OPENQUERY

Linchi Shea — Thu, 18 Mar 2010 14:13:10 GMT

I don't see why linked servers should only be used for 'ad hoc' accesses. In particular, when it's used as a RPC mechanism, it is fantastically useful in many prod environments. Sure, the implementation can be much improved. But 'trying' to limit it to ad hoc use only serves to limit your ability to access info in a distributed envrionment.

Note that linked servers are not for moving data. They are configurations for accessing data. There is a big difference between moving data and accessing data.