Tuesday, October 12, 2010

What is Link Server and what are its advantages in SQL Server/ How to write Queries on tables and database which are on seperate sql server instance

Basically it provides us facility of Distributed transactions.

Some times we want to retrieve data from a sever and insert it in table of another server.
Some times we want to run sql queries and perform joins on tables of different database whose database are on different computer.

Link server provides us all above facility

Linked Servers allow you to submit a TSQL statement on one SQL Server instance, which retrieves data from a different SQL Server instances. In fact, linked server can be used to join data from multiple SQL Server instances using a single TSQL statement. When you have databases on multiple SQL Server instances, you might find it useful to use linked servers in your application to retrieve data from more than one instance. By using a linked server your application will only need to connect to one SQL Server instance to retrieve data from multiple SQL Server instances. On that single SQL Server instance, you would define linked servers so your application could retrieve data from the databases that reside on a different SQL Server instance. Next time you are considering how to handle retrieving data from multiple instances of SQL Server from a single connection or single TSQL statement you might consider looking into using a linked server.


SELECT e.* FROM SERVER2.AdventureWorks.Employee e inner join
Server1.AdventureWorks.Salary s on e.employeeid = s.employeeid

Performance of Distributed Query:

when you run a distributed query using a linked server, the query is processed locally. This may or may not be efficient, depending on how much data must be sent from the remote server to the local server for processing. Sometimes it is more efficient to pass through the query so that it is run on the remote server. This way, if the query must process many rows, it can process them on the remote server, and only return to the local server the results of the query

When running distributed queries on a linked server, if the linked server has the same character set and sort order (collation) as the local SQL Server, then you can reduce overhead and boost performance if you set the SP_SERVEROPTION "collation compatible" option to true. What this setting does is tell SQL Server to assume that all columns and character sets on the remote server are compatible with the local server. This same option can also be turned on for a linked server using Enterprise Manager or Management Studio.


Share This!

No comments:

Powered By Blogger · Designed By Seo Blogger Templates