How to Collect Data with Central Management Servers in SQL Server

central-management-serversql serversql server 2014

I have 50 servers. I manage them from a central management server on SQL Server 2014 Enterprise Edition. I don't use repository.

I could open a query and get result for each one at the same time. But I want to do it in an automated way, like a job, to collect data from each server and store/handle it locally.

Is this possible and if so, how?

Best Answer

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?

Related Question