What is a
Linked Server?
Think of a Linked Server as
an alias on your local SQL server that points to an external data source. This
external data source can be Access, Oracle, Excel or almost any other data
system that can be accessed by OLE or ODBC--including other MS SQL servers. An
MS SQL linked server is similar to the MS Access feature of creating a "Link
Table."
Why use a
Linked Server?
With a linked server, you
can create very clean, easy to follow, SQL statements that allow remote data to
be retrieved, joined and combined with local data.
While it would be convenient
to have all of our business data in one place, there are too many obstacles
such as Vendor applications built for a specific data store, data sets too
large for one server, legacy flat file applications that are cost prohibitive
to recreate and changing business standards, preventing this from happening.
"Replication Manager" has
made moving data from one SQL Server to another on a regular basis relatively
easy. However, duplicating data to an application server is not always the best
solution. If your source is large, and you cannot predict what subset of data you
will need, then a linked server may be a better solution.
If you have a very large
data set, there may be performance benefits to splitting your data into pieces,
and moving those pieces onto different servers. Then using distributed
partitioned views to present the data as one source. If so, linked servers are
the technology that makes it possible.
Why not use a Linked Server?
If the remote data is not
yours, and the owning department will not allow you remote access, then a
linked server is out. You will have to rely on some type of scheduled pickup
and exchange.
When absolute, best possible
performance is required, local data will out perform a linked server.
If the physical link between
your SQL Server and the remote data is slow, or not reliable, then a linked
server is not a good solution.
History
Linked servers are a
superset of "remote servers." Remote servers allowed the running of stored
procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support
for remote servers is provided for backward compatibility only. New
applications that must execute stored procedures against remote instances of
SQL Server should use linked servers instead." Support for remote servers may be discontinued in the future. In
addition, remote servers only allowed stored procedures to be run. Linked
servers allow both stored procedures and ad hoc queries.
Distributed Transaction Coordinator (DTC)
Before starting the
examples, we need to start the Distributed Transaction Coordinator. The DTC
manages the committing of transactions when there are several different data
sources involved. For Windows 2000, service pack 1 is required.
+ Open the services MMC,
locate and start the Distributed Transaction Coordinator using the default
settings.
IE 7
Firefox 2.0