5

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?

4

I upvoted datagod's answer 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 seems 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. 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 be forewarned, but it answers exactly what you need to do to properly use Linked Servers for the Federated Servers/Distributed Views purpose.

I wanted one server 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; Recommended because it's needed for federated servers and distributed queries, Yes; HIGHLY recommended, not without thorough understanding and testing of the techniques.

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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