Link Server in SQL Server 2012

 

To run or call commands against database data sources outside of its instance, you must configure a linked server. Usually, to connect to external data sources such as Oracle, linked server needs to be configured to allow the database engine to run the SQL statement. External data sources such as Oracle, MS Access and Excel can be configured as linked servers. This feature also allows DBAs to load balance queries, commands, and updates among different sources in the organization. You can use SQL management studio and Transact SQL to configure linked servers. Link server consists of OLE DB provider and OLE DB data source. It’s a Dynamic Link Library that communicates with a particular data source. Usually, queries are distributed when receiving requested from client applications.

Configuring a Linked Server via SQL Management studio:

  1. Launch SQL Management Studio, open Object explorer, under “server objects”, Right-click “Linked Servers” and then click New Linked Server.

  2. Type in the SQL Server instance that you wish to link to.

  3. Select SQL Server to dictate that the linked server is a different instance.

  4. Configure security settings and click OK

 

 

Configuring a Linked Server via TransactSQL:

  1. Launch query editor, run below transactSQL command.
    USE [master]

    GO

    EXEC master.dbo.sp_addlinkedserver

    @server = N'SQL01\instance1',

    @srvproduct=N'SQL Server' ;

    GO

     

  2. Test the linked server by running below command.
    SELECT name FROM [SQL01\instance1].master.sys.sysdatabases ;

GO