Collect data with central management servers and keep them in local table

Question

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?

Answer 1

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?

Answer 2

Surely you can automate via CMS as explained in Automate Your Central Management Server Registrations (SQL Server Central, free registration required), which I've used and works well in 2012 and should be the same for 2014 as mentioned:

1. Script 03_populate_cms_folders_from_inventory.sql could be scheduled as a SQL Agent job to regularly refresh the CMS folder contents

2. Adding a section to [dbo].[ap_CMS_Populate_CMS_folder] to allow for SQL 2014 could be done (though I haven’t tested it)

Therefore by running the stored procedures from above article at the desired frequency via SQL Server Agent you can gather the data at your local table and later query for same.

One more way of doing this (apart from CMS) is to use Data Collector, which has quite significant changes in 2014 as explained in the article which also will help you automate the process.

Adding: Also you can use both as explained in:

Centralize Your Database Monitoring Process (SimpleTalk)

I hope the above articles/methods will help you in automating the tasks.

Answer 3

I used Toad for SQL Server for this purpose before. Here is a link on how to execute queries on multiple SQL Servers.