Derik Hammer shows an example of “old-style” partitioning across servers:
SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even split it among different servers. Sharding is another term. It refers to partitioning data to horizontally scale out compute resources and storage.
There are different methods of handling sharding. Each of them need a central point of connection to handle querying the data on the other shards. This is typically called the control node. The method I am to discuss today is one where linked servers are used to connect the various shards.
This is useful for something like offloading old invoices which you rarely need to a separate server. Derik also shows that the optimizer can, if it knows your partitioning rules, avoid unnecessary cross-server hits.