| Working with Linked
Servers by
Bill Wunder
Linked servers allow you to query
virtually any OLE DB data source from T-SQL running on a SQL
Server 2000 creating a tremendous backdrop for distributed data
architectures. If the OLE DB provider provides the necessary
interfaces you can also insert, update, and delete data at that
OLE DB data source. Reciprocally, SQL Server is also an OLE DB
data source that can be accessed through quite a variety of OLE
DB Providers including linked servers from other SQL Servers.
There really are quite a number
of interesting possible OLE DB data sources. The better known
include Excel, Access, Oracle, DB2, and ODBC. The less well
known include LDAP, Active Directory, Exchange, Outlook MySQL,
HTML and VSAM. That is certainly not an exhaustive list. In
reality give a good C/C++ programmer enough time and they can
turn just about anything that can be connected to a computer
into an OLE DB data source. There's a nice list of commercial
non-Microsoft OLE DB providers at
sqlsummit.com
and you may also want to consider this well discussed listing of
most current
Microsoft OLE DB
providers in the MSDN
Library and this discussion of
OLE DB Providers Tested with SQL Server
in Books Online where you will find examples of how to configure
a connection some common and interesting linked server OLE DB
providers. Microsoft provides the SQLOLEDB provider for
connection to a SQL Server 2000 as the data source.
Before linked servers, earlier
releases of SQL Server supported distributed queries to other
SQL Servers using "Remote Servers" via RPC. This required
configuration at both the data serving and the data requesting
SQL Server and had all the performance, scalability, and
security issues of the underlying RPC protocol. Remote Servers
are still available in SQL Server 2000 but provided "for
backward compatibility only". Looks like they are still there in
Yukon beta 1 but if you are using them now, it's a good idea to
begin migrating remote servers to linked servers.
Hopefully, in SQL Server 2005 we
will be able to use the native .Net providers when creating
linked servers and enjoy the many advantages of that provider.
Dino Esposito has a very nice online article in Visual
Studio magazine: "Create
More Efficient Database Code"
on the .Net provider and how it differs from the SQLOLEDB
provider. Since linked servers are not yet implemented in the
Yukon beta 1 release, I guess we'll just have to wait and see if
T-SQL linked servers will gain the new .Net powers and
efficiency.
So what is an OLE DB provider
anyway? According to the "OLE
DB Programmers Reference"
of Microsoft's MSDN library:
OLE DB architecture is built
upon the precept of an application accessing diverse data
stores through a small application built specifically for
that purpose. The application that uses OLE DB functionality
is called the consumer, while the one that accesses
the data by exposing OLE DB interfaces is called the
provider.
This leads us to an important
point when we talk about using linked servers in T-SQL queries.
Using a linked server makes the SQL Server where the linked
server call is executed the consumer of the provided data. That
means that we are asking a SQL Server to be a middle man for
data. The network loading and security issues of this can be
huge! I have seen many cases where a developer will use a linked
server to get data from a remote source rather than making a
connection to that source directly from his application. This is
just laziness - and not the good
fourth lazy form
kind of lazy either! - it is simply wrong. SQL Server is...,
well..., a server not a client application or a mid tier
transport utility. It should be obvious to anyone that moving
data from an OLE DB data source to a SQL Server to your
application is less efficient and creates a more complex
security model than moving data from an OLE DB data source to
your application.
OK, so what are Linked Servers
good for then? If you need to join data from two or more
different data sources linked servers are your friend. Even with
this usage linked servers can carry some performance and
security costs. Basically what you want to do is use the
principle of least
privilege and then make
sure the record sets transferred on a linked server connection
are small or make sure that the rows are moved across the linked
server connection as a "fire hose" or fast forward only cursor.
That's right! If you watch a linked server call that uses the
SQLOLEDB provider to get a multi-row data set from another SQL
Server in SQL Profiler trace on that remote server you will see
that the data is transferred using a
Server API cursor.
Just like cursors in any other context, they can slow you down.
There is more than one way to work with a query to produce a
"fire hose" from the linked server data source. One consistently
useful method is to insert the rows into a temp table and then
process the necessary join as a local query using the temp table
rather than joining across the network. Bottom line here is that
good testing will result in good results.
You can
configure linked
servers using Enterprise
Manager but - as with almost anything else you do in SQL Server
- I recommend using the system stored procedures to configure
linked servers. The system stored procedures are:
sp_addlinkedserver
- You'll find yet another listing of the OLE DB
provider names listed on the sp_addlinkedserver help
page in Books Online. Just remember that the
preferred provider to link to SQL Servers is
SQLOLEDB. Another interesting tip is that you do not
need to specify a catalog when defining a SQLOLEDB
linked server. The advantage is that the linked
server will not "break" if a database is dropped or
it's name is changed. That can be a very obscure
problem. The limitation is that this does require
the database name be provided in all linked server
queries. And that should be a best practice anyway.
sp_addlinkedsrvlogin
- There are a few
security
considerations
when creating linked servers that you must address
when creating logins. Using linked servers on
trusted connections without the benefit of Active
Directory Delegation can be a real pain. It's
definitely a security nightmare, but you can add [NT
AUTHORITYANONYMOUS LOGIN] as a trusted login at the
data source and grant permissions to this ambiguous
login to work around the delegation issue in an
environment without delegation. If a SQL login has
the same name but different passwords you'll have to
map that user as to the same user name with the
other password in the linked server login
configuration. That also means when you change the
password you have to manually update the password in
the linked server configuration. You can also map
users to other users in the linked server. Be
careful because that could get confusing in a hurry.
sp_droplinkedsrvlogin
- Keep in mind that when using system stored
procedures you must drop the logins to a linked
server before you can drop the linked server.
Enterprise Manager takes care of all of that for you
behind the scenes.
sp_dropserver
- Since linked servers are store in
master.dbo.sysservers along with remote servers
and even the local instance, use the same procedure
to remove any of them.
sp_linkedservers
or
sp_helpserver -
sp_helpserver will show you linked servers as well
as remote servers.
sp_serveroption
- This is an important one that can slip through the
cracks. Use this procedure to establish "data
access" - the ability to directly select, insert,
update, and delete - across the linked server, "rpc"
to call stored procedures on the link, and even
connection time out and query time out settings that
unfortunately default to unlimited in SQL Server
2000.
|
I use a
stored procedure that calls the
system stored procedures to assure that the linked servers
between the SQL Servers in my shop are all the same. I have also
experienced occasional corruption of a linked server so having a
stored procedure available that will quickly reproduce a linked
server has proven invaluable in crisis mode. I also take
advantage of the ability to alias linked servers. So for example
if I have an application stored procedure that must reference
another server I can create a linked server with the same name
in development, acceptance and production so that the
application stored procedure does not have to be edited when
promoted from development or acceptance.
Finally, you may be wondering how
are linked servers be used in T-SQL code. You can use linked
sever in fully qualified or four part named references (linked
server.database.owner.object) or you can use
OPENQUERY to execute
pass through queries. Using OPENQUERY can help you write those
fire hose queries I mentioned earlier without even needing to
think about it. Unfortunately, many developers find the
OPENQUERY syntax confusing and avoid using it.
To sum it up, linked servers can
be very useful, but are better used with small distributed data
sets that large data collections. My preference is that linked
server calls in applications are looking for a single row or
value on the remote data source. Of couse, as everyone likes to
remind me, I don't always get what I want.
Bill |