How to Create a Linked Server?

Linked Server: It is a database object that lets you access to another SQL Instance within a same network. This is similar to database link in Oracle database. Linked servers are designed to enable the database engine to execute the SQL statement that includes table in another Instance of SQL Server or another database like Oracle. Linked Server will let you:

1. Access data from outside the SQL server
2. Issue distributed queries across the enterprise
3. Address diverse data sources similarly

If you are new and in a process of  learning a database, you might be wondering when and how often do you use this object? The most common use in any organization is to move data from production database to development database or vice versa. This is done to test the application functionality and study the behavior of the application when it goes to production. Also, Linked Server let you push a brand new table along with it's data  from development to production. This is one the most frequent task that database administrators are asked to do.

There are several different ways to move data around without Linked Server. Custom script and more manual process can accomplish what you can do with Linked Server. Using Linked server reduces the coding time and increase the accuracy of data movement. With this blog, I am going to show you how you can configure a Linked Server in your development SQL instance to query a database which is in Production SQL Server Instance.

Creating Linked Server:

USE [master]
GO
/****** Object:  LinkedServer [PROD_LS]    Script Date: 12/29/2014 10:50:30 AM ******/
/*** I ran this on my SQL_DEV Instance *****/
EXEC master.dbo.sp_addlinkedserver @server = N'PROD_LS', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL_PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'my_user',@rmtpassword='my_password'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'BJHPERSQL01', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BJHPERSQL01', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'BJHPERSQL01', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL_PROD', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Expand Server Objects in your SSMS tool, you will see a new Linked Server named PROD_LS under Linked  Servers, refresh if you don't see it.  Expand the Linked Server to view all the databases and their objects that you have access to. If get the result back for the query below then your Linked Server is set up correctly.

How to query data from linked Server: 

SELECT *
FROM   [Linked Server Name].[Database name].[dbo].[table name]

Example:

Select *
From [Prod_LS].[HR].[dbo].[employee];