Fetching the data from two different servers in SQL Server

Question

How can I fetch the data from two different databases that are on different servers in SQL Server?

Answer 1

You will need to create a linked server to the server where you will execute the Query. You can use sp_addlinkedserver to create one. You may reference here.

Sample would be for the same SQL Server

EXEC sp_addlinkedserver   
   N'SEATTLESales',  
   N'SQL Server';  
GO  

Once you have created the linked server. You can now reference it in your query by doing something like this:

SELECT * FROM [NameOfLinkedServer].[DatabaseInLinkedServer].[dbo].[TableInLinkedServer]

Or you can check Dave Brown's comment to your question: Here.

Answer 2

You can indeed do this, but I recommend against it. You can create a linked server on DB-SERVER-1, connecting it to DB-SERVER-2.

(A linked server definition is setup by the DBA. It contains the login credentials to the remote server. If you want to provide the login credentials yourself each time, that would be an openquery or openrowset command.)

You then could write a query using four part naming convention as follows, assuming you are running this query on DB-SERVER-1:

select * 
  from DB1.dbo.YourTable a
  join [DB-SERVER-2].DB2.dbo.OtherTable b on b.ID = a.ID

Cross server joins are notorious for having performance problems. I would only do it for administrative purposes, and definitely not against large result sets.