Tuesday, August 23, 2011

How can I query from multiple sql server database that exist on different machine?

Problem: 
Some questions like following are often asked in communities:
> How can I query from multiple sql server database?
> How can I query from multiple sql server database that exist on different location?
> How to copy one data of one table from one database to another database on different server?


Solution:
The solution for working with multiple database that exist on different server is Four Part Naming. The structure of four part naming is: 
ServerName.DatabaseName.Schema.TableName                         - for multiple database on same server
Linked_Server_Name.DatabaseName.Schema.Object_Name      - for multiple database on different server


First part is your server name or linked server name.
Part two is your database name or catalog name.
Part three is Schema or Database owner name.
Part four is your object or table name.

When you want to query on two database that exist on same server you can try query as follows:

SELECT E.EmployeeId, E.EmployeeName, D.Designation
FROM MyServer.EmployeeInfo.dbo.Employee E, MyServer.HRInfo.dbo.Designation D


But in case of different server you have to create Linked server. You have to add all servers that you want to query to Linked Server list even though it is your local machine. You can visit here to know more about how to create linked server.

When your server is linked they you can query as follows:


SELECT E.EmployeeId, E.EmployeeName, D.Designation

FROM 192.168.102.101.EmployeeInfo.dbo.Employee E, 115.130.18.38.HRInfo.dbo.Designation D

Here IP addresses are name of linked servers.

Hope this will help.
Thanks.

1 comment:

  1. There's a performance caveat to point here. If you run on your server a query that has 4 part names (i.e server.database.schema.object) you will not be able to take advantage of statistics, cached plans, etc on the remote server.
    To ensure you are still performant, I can just think about two options for starting with.

    1) Use "openquery" (I haven't used "openrowset" or "opendatasource" but I assume they do something similar). In this case, the string with the T-SQL inside will be executed on the remote server and there will be able to take advantage of those things mentioned before

    2) You can use four-part-names for executing Stored Procedures located on the remote server. Same thing will happen: Query plan will be generated and executed on the remote server.

    Nice to have someone talking about linked servers!

    Don Roque

    ReplyDelete