Sql-server – Combine results of Multi-Server Query from Registered Servers

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.

What you can do is the following:

• Create a linked server for each of your managed servers
• Create a view for the data you need from each linked server

Once you have created a linked server, you can query data from it as follows:

SELECT * FROM [linked-server].[database].[schema].[tablename]

Depending on what you need in your business case, you can create a view for each such table you want to query:

CREATE VIEW [ls1] AS SELECT * FROM [linked-server-1].[database].[schema].[tablename]; GO
CREATE VIEW [ls2] AS SELECT * FROM [linked-server-2].[database].[schema].[tablename]; GO
...

You can query them as you would a regular table:

SELECT * FROM [ls1];
SELECT * FROM [ls2];
...

Or you could create a view that combines all the data from all those linked servers:

CREATE VIEW [ls_all] AS 
  SELECT * FROM [linked-server-1].[database].[schema].[tablename]
  UNION ALL
  SELECT * FROM [linked-server-2].[database].[schema].[tablename]
  UNION ALL
  ...
  UNION ALL
  SELECT * FROM [linked-server-50].[database].[schema].[tablename]

You can then query all data from all linked servers as follows:

SELECT * FROM [ls_all];

You can use these views from a SQL Server job if need be.

If you have a table in your CMS containing all the information required to create the linked servers, then you can keep track of which linked servers/views you already created and in your job create linked servers and views for those that haven't been created yet (or for a select-it-all view, recreate it to also select for newly added linked-servers).

AFAICT there is no automated way to execute a query against of a group of servers that comes out of the box in SQL Server. Perhaps there exist tools that automate this for your? I haven't worked with any but they might exist.

But suppose you created linked servers for all 50 servers in your CMS, and you've created a view for each of the tables in those servers you want to query. Is the script that selects one after the other, as in:

SELECT *
FROM [ls1];
SELECT * 
FROM [ls2];
 ...; 
SELECT *
FROM [ls50];

...not good enough?