Take the 2-minute tour ×
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's 100% free, no registration required.

Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2). Both are SQL Server databases. Is it possible to query both databases in the same T-SQL command or function? Perhaps by connecting to one, then getting a result set, then connecting to the other, and getting that result set. Then performing a union with both result sets?

I can do this from within .NET or Java code, but I want to do this strictly from the database side. Is this even possible?

share|improve this question
add comment

2 Answers 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. 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.

share|improve this answer
    
+1 how would you write query across servers? –  MacGyver Jan 9 '13 at 21:04
    
The code I included above is an example of a cross-server join. YourTable is being joined to OtherTable, but they each exist on different servers. –  datagod Jan 10 '13 at 19:07
    
@datagod but where is the login command to databases? I think we should login to both databases and execute query. Can you add login command to your code? –  Omid Shariati Apr 24 '13 at 7:37
    
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. I am not sure if you could do the joins that way though... –  datagod Apr 26 '13 at 0:51
add comment

I one up'd the answer above because, technically, it's correct on how to accomplish it. However, I would make the opposite recommendation. Meaning, I would recommend using linked servers for what you are doing. This is because it seams like you're asking the question about architecting Federated Servers using Distributed Partitioned Views techniques.

There are many rules and standards to implement when architecting. And it's true that Linked Servers shouldn't just be thrown into any mix to connect more than one server result sets. There are reasons to use objects and techniques for the right reasons.

Case in point, I successfully used Federated Servers/Distributed Views within a VLDB scenario that required multiple servers. That's what this technique is all about. It's advanced stuff, so fore-warned, but it answers exactly what you need to do to properly use Linked Servers for the Federated Servers/Distributed Views purpose.

I wanted one sever to be our current year of shipping data, and then the next 2-7 years on additional servers for archive. I needed to use multiple servers because of the volume of data and utilize the independent server resources for each query result set, depending on the year of the request. The technology of the Distributed View in the engine knew which server to go to and get the results. Because of the Check Constraints. Is it expensive, yes, does it work as is required, yes. Are linked servers stable, yes, but we had to account for the failure point. So, it isn't anything that can't be normally planned with similar requirements.

Make sure the Distributed Queries are properly architected and tested for performance and failure points.

In summary ... Possible, Yes; Do-able, Yes; recommend because it's needed for federated severs and distributed queries, Yes; HIGHLY recommend, not without thorough understanding and testing of the techniques.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.