Microsoft® SQL Server® 2012 Bible

368

Part III: Advanced T-SQL Data Types and Querying Techniques

Linking is a one-way confi guration between two servers. For example, if Server A links to

Server B, then Server A knows how to access and log in to Server B. As far as Server B is

concerned, Server A is just another user.

Linked Servers is not a new concept; it has been around for a while. Linking a server is not

the same as registering a server in SQL Server Management Studio (SSMS). Management

Studio communicates only with the servers as a client application and provides the abil-

ity to do the server linking, different from registering a server in Management Studio.

Linking the servers enables SQL Server Instance A to communicate directly with SQL Server

Instance B.

You can establish links in two ways: through code or through SQL Server Management

Studio. Using T-SQL code to link servers has the added advantage of repeatability in case

a rebuild is necessary. Obviously rebuilding the links via code requires more steps but pro-

vides the fl exibility and repeatability developers look for. OK, yes, you could create the link

in Management Studio and then script the link, but honestly, that’s cheating.

Linking a server goes far beyond just linking to SQL Server instances. A linked server can be a

SQL Server or any other data source with either an OLE DB provider or ODBC driver. Distributed

queries can select data and modify it depending on the features of the provider or driver. SQL

Server queries can reference external data by referring to the preconfi gured linked server or

specifying the link in the query code. You will see some examples of that later.

By physically linking the servers in Management Studio, declaring the link becomes admin-

istrative rather than developmental. Queries can easily refer to a named link without the

worry of location or the security concerns. Queries that use linked servers are more por-

table and easier to maintain simply because if the linked database moves to a new server, a

simple creation of the new link in Management Studio is all that you need, and the queries

can work without any modifi cation.

Linking to External Data Sources

To understand how linking works, fi rst take a look how it works within the same server.

Most of you have written queries that pull data from one database while connected to

another on the same server. A SQL Server query may access another database on the same

server by referring to the remote object in the other database, such as a table or stored pro-

cedure, using the three parts of the four-part name:

Server.Database.Schema.Object

When the database is on the same server, the server name is optional. If the objects are in

the dbo schema, then you can assume the dbo schema is as follows:

SELECT FirstName, LastName FROM AdventureWorks.dbo.Contact

You can also assume the schema to be dbo and ignore it by leaving the schema empty:

SELECT FirstName, LastName FROM AdventureWorks..Contact

c15.indd 368c15.indd 368 7/30/2012 4:50:38 PM7/30/2012 4:50:38 PM

http://www.it-ebooks.info