Link Server : How to Create Configure and Manage Link Server in SQL Server Management Studio

Today our topic is How to Create Configure and Manage Link Server in SQL Server Management Studio. Lets move a head open SSMS and move to Server Object and then Linked Servers.

A linked server allows joining data from several SQL Server instances using a single T-SQL statement when data exists on multiple databases on different SQL instances. By using a linked server to retrieve data from several SQL instances, the only thing that should be done is to connect to one SQL instance.

 

 

 Right Click on Linked Servers and then New Linked Server

 

A window will open for the configuration of Linked Server.

 

 Enter the Name of the Server, the Name can be Customized that can be easily understandable for the Developer or while you will be using the Server Name in your PL/SQL or T-SQL Query.

Select the Provider in my case i have Native Clint 11.0 that i have SQL Server 2012 Installed on Client Machine.


Enter the product Name such as SQLServer2012
The Host would be the network IP to which your client Database is Installed. 

The Next Settings are for the Security Settings, Click on Security 


 

Thus I have Connected to my Local Machine Server as a Client Server.

 

The Open Query to access the linked Server Machine is written here for your ref.

 

SELECT [ID]
      ,[Name]
      ,[Age]
      ,[designation]
  FROM [NAME_OF_SERVER].[SampleDatabase].[dbo].employee

 

Where NAME_OF_SERVER is the Instance of my Server Name and then the Database Name  and then dbo is the Schema Name and then Tables Name.