How to connect to multiple database servers, display combined results?

Question

If I have same table and same multiple database servers. How to connect to multiple database servers, obtain those records from each database server and then display the first 10 of the combined results ?

Answer 1

Here are a couple solutions off the top of my head.

Solution 1:

1 - Create a staging database / table on server A.

2 - Import all data from all servers into table.

3 - Query table to get results.

Solution 2:

1 - Create a Linked server for each server B .. Z on server A.

2 - Create Query using 4 part notations on linked servers.

Overall, solution 2 can be slow since you are using distributed transactions.

Solution 1 allows you to store the aggregated results that can be indexed (for speed) and can be queried multiple times.

As for importing the data from server to server, just pick a way to do it. There are two many solutions are there to get into the particulars.

Answer 2

Say for instance you are querying the multiple instances using different connection strings for a sample table Orders, you could try the following:

var orders = ConfigurationManager.ConnectionStrings.Cast<ConnectionStringSettings>()
    // filter to the relevant connection strings
    .Where(s => s.ConnectionString.ToLower().Contains("metadata"))
    .SelectMany(s => {
         // for each connection string, select a data context        
         using(var context = new NorthwindEntities(s.ConnectionString)) {
             // for each context, select all relevant orders
             return context.Orders.ToArray();
         } // and dispose of the context when done with it
     })
    .Take(10) 
    .ToList();