| |
| A Linked Server configuration
allows us to execute commands against different Database Servers.
This uses OLE DB to connect to other servers. |
| |
| This allows us to: |
| |
| 1. |
Access Remote Servers to
Query, Insert, Update and Delete. |
| 2. |
Allows data comparison from one server
to another. |
| 3. |
Reduces the resource utilization of
Single server. |
|
| |
| Detailed Steps to configure
Linked server |
| |
| Lets take one Local server and
One Remote server to configure Linked server. |
| |
| Local Server |
Remote Server |
| TVMNTDB3 |
TVMNTDB2 |
|
| |
| 1. |
Using Query Analyzer, Login
to TVMNTDB3 server with SA id and execute. |
| |
USE
master
GO
EXEC sp_addlinkedserver 'TVMNTDB2','SQL Server'
GO |
|
| |
Note: Please add the remote server TVMNTDB2
using Client network Utility. |
| 2. |
Using Query Analyzer, Login to TVMNTDB2
(Remote) Server with SA id and add a new login named |
| |
Test1. |
| |
Test1 id will be used
from Local server to login to TVMNTDB2 server. |
| |
|
| |
Add the user test1 to
Pubs database |
| |
Use
pubs
go
sp_adduser test1 |
|
| 3. |
Grant Privileges to test1
user in Pubs database. |
| |
use pubs
go
sp_adduser test1
go
sp_addrolemember db_datareader,test1
go
sp_addrolemember db_datawriter,test1 |
|
| |
User Test1 has the privileges to Read
and write data in Pubs database. |
| 4. |
In Local Server TVMNTDB3 server Link
the Test1 user : |
| |
|
sp_addlinkedsrvlogin 'TVMNTDB2','false','sa','test1','test1' |
|
| |
Here SA id TVMNTDB3 Local server is
mapped to Test1 id of remote server.
The second Parameter is set to false. This allows us to
use a different account and password in
Remote server. |
| 5. |
Issue the below command to verify the
Link Login Mapping. |
| |
|
sp_helplinkedsrvlogin TVMNTDB2 ,sa |
|
| |
In the output we can see the Local Login
as SA and Remote login as test1 |
|
| |
| Various DML operations using
Linked servers |
| |
| Querying |
| |
Select * from TVMNTDB2.pubs.dbo.link_test
Where link_server = 'TVMNTDB2' |
|
| |
| OR Use Open Query |
| |
|
Select * from openquery(TVMNTDB2,'select * from TVMNTDB2.pubs.dbo.link_test
') |
|
| |
| If we use open query Variables
can be used in selection criteria |
| |
| Insertion |
| |
declare @t char(20)
select @t=@@servername
Insert into TVMNTDB2.pubs.dbo.link_test(link_server ,date_entry)
values(@t , getdate()) |
|
| |
| Updation |
| |
Update TVMNTDB2.pubs.dbo.link_test
Set link_server='Record updation' |
|
| |
| Deletion |
| |
|
Delete from TVMNTDB2.pubs.dbo.link_test |
|
| |
| Handling Multiple tables |
| |
| Joining the table link_test
in remote server TVMNTDB2 with local server table link_local |
| |
Select * from TVMNTDB2.pubs.dbo.link_test a , pubs..link_local
b
Where a.link_id = b.link_id |
|
| |
| Creating Views using Linked
server / (Partitioned Views) |
| |
Create view link_view_on_tvmntdb2
As
Select a.link_id,a.link_server from TVMNTDB2.pubs.dbo.link_test
a , pubs..link_local b
Where a.link_id = b.link_id |
|
| |
| |
