Linked Server in SQL Server

Often applications running on SQL Server require access to data residing on a different instance of the DBMS from Microsoft. That application may be physically different on the same machine the main instance, or it may reside on a different machine.

A solution in these cases is to use the so-called linked server (linked server), which allow applications to access data managed by different instances of SQL Server. In fact, the linked server can also be used to access other sources of data, so even non-SQL Server. In this article, however we will focus on aspects related to the use of linked servers to access information stored in a SQL Server database.

Before going to see how to set up a linked server, let's take on its basic architecture. A linked server is basically a mechanism that allows a query (question about the data) is sent to a server with a specific instance of SQL Server and then be redirected and processed by another instance of SQL Server. The results of this operation are then returned to the client that sent the query.

The following image is used to clarify this architecture

This image shows a generic client that can connect to query both an application server or directly to a server running an instance of SQL Server. If the client (or application running on application server) to Server1 sends a query that requires data that resides on Server2 it comes to distributed query. Define a linked server on Server1 Server2 allows for this type of query.

A distributed query that extracts information from Server2 using a linked server defined on Server1 looks like this

 



 SELECT name as 'Remote Database Name' FROM SERVER2.master.sys.databases

 

that is, the syntax of the data present in the FROM clause is as follows

 



 <nome_linked_server>. <database_name>. <schema>. <object>

 

Let us now see how to set up a linked server using SQL Server Management Studio. First, expand the Server Objects node and click the right mouse button on the entry Linked Servers, selecting the New Linked Server

• <<
• 1
• 2
• 3
• 4
• >>