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?
Best Answer
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.