THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Using linked servers, OPENROWSET and OPENQUERY

SQL Server has a few mechanisms to reach out to another server (even another server type) and query data from within a Transact-SQL statement. Among them are a set of stored credentials and information (called a Linked Server), a statement that uses a linked server called called OPENQUERY, another called OPENROWSET, and one called OPENDATASOURCE. This post isn’t about those particular functions or statements – hit the links for more if you’re new to those topics.

I’m actually more concerned about where I see these used than the particular method. In many cases, a Linked server isn’t another Relational Database Management System (RDMBS) like Oracle or DB2 (which is possible with a linked server), but another SQL Server. My concern is that linked servers are the new Data Transformation Services (DTS) from SQL Server 2000 – something that was designed for one purpose but which is being morphed into something much more.

In the case of DTS, most of us turned that feature into a full-fledged job system. What was designed as a simple data import and export system has been pressed into service doing logic, routing and timing. And of course we all know how painful it was to move off of a complex DTS system onto SQL Server Integration Services.

In the case of linked servers, what should be used as a method of running a simple query or two on another server where you have occasional connection or need a quick import of a small data set is morphing into a full federation strategy. In some cases I’ve seen a complex web of linked servers, and when credentials, names or anything else changes there are huge problems.

Now don’t get me wrong – linked servers and other forms of distributing queries is a fantastic set of tools that we have to move data around. I’m just saying that when you start having lots of workarounds and when things get really complicated, you might want to step back a little and ask if there’s a better way. Are you able to tolerate some latency? Perhaps you’re able to use Service Broker. Would you like to be platform-independent on the data source? Perhaps a middle-tier might make more sense, abstracting the queries there and sending them to the proper server. Designed properly, I’ve seen these systems scale further and be more resilient than loading up on linked servers.

Published Tuesday, March 16, 2010 6:41 AM by BuckWoody



NULLgarity said:

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

March 16, 2010 12:29 PM

Linchi Shea said:

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

Are you kidding?

March 16, 2010 11:19 PM

Adam Machanic said:

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.

March 17, 2010 10:01 AM

NULLgarity said:

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.

March 17, 2010 1:59 PM

Linchi Shea said:

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.

March 18, 2010 10:13 AM

Creature said:

What's the best thing for moving data, then?

February 17, 2014 11:35 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement