We have multiple MSSQL 2005 servers that contain silos of information. We want our database developers to be able to join across these silos in the most transparent and performant way possible. The databases vary in size, but average in the tens of millions of rows, and are updated frequently. The servers are physically co-located.
I want to be able to create a sort of "single logical database" from many different server instances.
Linked servers are the obvious choice, but I'm worried about performance and tight-coupling of a query to a particular server. Am I too worried? Do you have experiences to support this solution?
Other options I'm weighing are using replication, SSIS packages, service broker, or physically attaching more databases to one of our larger machines.
Also, I'm wondering if there are other vendors that provide a solution to this problem?