Is there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ?
I google it for several minutes and I find nothing about it.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityIs there any benefit in use a Loopback Linked Server, maybe in a security or performance level, because I really don't get the idea ?
I google it for several minutes and I find nothing about it.
Basically Loopback linked servers are mainly useful when testing an application that uses distributed queries on a single server network.
Note that they cannot be used in a distributed transactions.
In terms of performance, there will be performance impact when you do selects or joins using LOOPBACK server as all the data has to go across wire.
For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.
Bottom line is linked servers are evil in terms of performance. Unless you really need them, dont / avoid using them.
For e.g -- Consider a basic example of selecting some data from a table
-- taken from : Aaron's article : "Best approaches for running totals – updated for SQL Server 2012"
-- http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals
CREATE TABLE dbo.SpeedingTickets
(
[Date] DATE NOT NULL,
TicketCount INT
);
GO
ALTER TABLE dbo.SpeedingTickets ADD CONSTRAINT pk PRIMARY KEY CLUSTERED ([Date]);
GO
;WITH x(d,h) AS
(
SELECT TOP (250)
ROW_NUMBER() OVER (ORDER BY [object_id]),
CONVERT(INT, RIGHT([object_id], 2))
FROM sys.all_objects
ORDER BY [object_id]
)
INSERT dbo.SpeedingTickets([Date], TicketCount)
SELECT TOP (10000)
d = DATEADD(DAY, x2.d + ((x.d-1)*250), '19831231'),
x2.h
FROM x CROSS JOIN x AS x2
ORDER BY d;
GO
SELECT [Date], TicketCount
FROM dbo.SpeedingTickets
ORDER BY [Date];
GO
--- select data as normally you would do
select [Date], TicketCount from test_kin.dbo.SpeedingTickets
Execution plan will have a clustered index scan
-- select data using loopback server (highly inefficient). Look at the CPU & Operator cost (though this is a simple example, but test out on a good data set with multiple joins, etc - you will clearly understand the difference - as to how expensive it is to use Linked (in this case loopback server))
Read up on :