You are here:Home » tsql » Working with Linked Servers in SQL Server

Working with Linked Servers in SQL Server

We may have a need for some logic to move the data from one server database to another server database on production environment. Also for the distributed transactions or for the cross server database queries we require it.

How to do it? With Linked servers we can perform any distributed transactions between servers. Also, we can execute remote servers stored procedure with linked server communication.

How to create it?
Herewith we have small demo which I have captured during setup it.
  1. Go to the Server Objects --> Linked Servers, Right click on it and click on New Linked Servers.
  2. Specify Server name which you want to linked to current server.
  3. Go to Security tab and select the option for link.
The option details as msdn and book online are following,

Local login : Specify the local login that can connect to the linked server. The local login can be either a login using SQL Server Authentication or a Windows Authentication login. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

Impersonate : Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

Remote User : Use the remote user to map users not defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

Remote Password : Specify the password of the Remote User.

Not be made : Specify that a connection will not be made for logins not defined in the list.

Be made without using a security context : Specify that a connection will be made without using a security context for logins not defined in the list.

Be made using the logins current security context : Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Be made using this security context : Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

We have a some server options like RPC, RPC out as follows


We have seen as how can we create linked server from SSMS. Now we will have script to create linked servers as well.
 USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'PARESH-PC1', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'use remote collation', @optvalue=N'true'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PARESH-PC1', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba@test'
GO

How to get list of linked servers?

SQL Server provide system stored procedure as well system tables , from we can have the details for the same.
  • sp_linkedservers
  • sys.servers
 
-- 1. Fetch the data using tsql

SELECT 
* 
FROM paresh-pc.demo.dbo.dempteable

-- 2. Running remote stored procedures

EXECUTE paresh-pc.demo.dbo.DemoSP 'test_param'
I hope you enjoyed linked servers. Please share your comments what you are performing with linked servers.