SQL Server Query – How to Query Databases on Different Servers

Bad news. You said ETL process - that means heavy data loads, and inserts/updates/deletes have a single disk bottleneck: the transaction log file. You can add multiple transaction log files, but they're used sequentially, not load-balanced (except in some really edge-case scenarios.)

Instead, what you need is multiple databases. In data warehouses, you typically use a different source database than a destination database, like a staging database. That way, you can put its databases on different drives (and even different classes of drives). For example, some staging databases live on cheap local SSDs.

If you do find yourself storage-bottlenecked on data file reads or writes, you're better off solving that problem by adding memory (to cache the data and avoid reads altogether) or by striping the data across more drives, not less. By splitting the data files into smaller pieces, and giving them each fewer drives, you're usually making the problem worse.

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.