У меня есть набор CLR процедур которые запускают запрос на любом количестве серверов и складируют результат в таблицу на локальном сервере. Потом можно что угодно делать, сортировать, фильтровать, джойнить и т.д. Сервера передаются либо списком через ; либо запросом где первой колонкой должно быть имя сервера. Запросы запускаются многопоточно так что работает весьма быстро, но медленне чем SSMS, потому что SSMS держит все соединения открытыми пока не закроешь окно, процедуры же открывают/закрывают коннекшн каждый раз при запуске. Зато не падает как SSMS при большом количестве серверов. На паре тысяч работало нормально. Ну и намного быстрее чем в цикле через линкованные сервера.
Сохранется все в постоянную таблицу, а не во временную, потому что из других потоков очень сложно получить доступ в текущую сессию (context connection). Немного не удобно, но нормального решения пока не нашел.
Запускается по умолчанию от учетки SQL Server-а с windows авторизацией, соответственно "главный" сервер должен иметь права на всех остальных серверах. При желании можно передать логин/пароль для каждого сервера.
Если кому интересно, могу прислать, но только AS IS. Никаких гарантий :) Успешно работает последние пару лет собирая даныне с 1,600 серверов.
Запуск выглядит примерно так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
-- Drop "temp" tables. If the table exists the error will be raised.
-- The new tables will be created automatically based on the schema of the query result
IF OBJECT_ID('dbo.tmp_Test') IS NOT NULL DROP TABLE dbo.tmp_Test
IF OBJECT_ID('dbo.tmp_TestError') IS NOT NULL DROP TABLE dbo.tmp_TestError -- Special table to keep all the errors
-- This sp doesn't return any resultset, but saves the result into 2 tables
-- Parameters:
-- @serverNames -- Either semicolon separated list or a valid SQL query
-- @sqlquery -- Query that will be executed on the servers. If the schema of resultset is different on the servers the procedure will fail
-- @targetTable -- Table to store the result. Will be created automatically
-- @targetErrorTable -- Table to store all the errors. Will be created automatically or reused if already exists
EXEC dbo.sp_RunQuerySQLServers
@serverNames = 'select name, server_id as id from msdb.dbo.sysmanagement_shared_registered_servers_internal',
-- SQL Auth
-- @serverNames = 'select name, server_id as id, ''sa'' as login, ''###%%%$$$$'' as password from msdb.dbo.sysmanagement_shared_registered_servers_internal',
@sqlquery = 'select sum(size/1024)*8/1024 GB from sys.master_files',
@targetTable = 'dbo.tmp_Test',
@targetErrorTable = 'dbo.tmp_TestError'
-- Results
SELECT * FROM dbo.tmp_Test ORDER BY [@ServerName]
SELECT * FROM dbo.tmp_TestError
-- Drop "temp" tables
IF OBJECT_ID('dbo.tmp_Test') IS NOT NULL DROP TABLE dbo.tmp_Test
IF OBJECT_ID('dbo.tmp_TestError') IS NOT NULL DROP TABLE dbo.tmp_TestError -- Special table to keep all the errors
