Отправка запроса на указанные базы данных MS SQL Server всех указанных серверов подручными средствами

2017-12-22 в 13:39, admin, рубрики: Microsoft SQL Server, mssqlserver, sql, SQLCMD Mode, ssms, лайфхак

Написать эту статью меня побудила заметка уважаемого jobgemws "Отправка запроса на все базы данных всех указанных серверов на примере MS SQL Server и C#.NET"

Я расскажу, как схожую задачу можно решить штатными инструментами MSSQLSERVER, а именно – SSMS (или, для экстремалов от администрирования — sqlcmd), быстро, без программирования, с помощью крошечного лайфхака.

Итак, у нас в организации имеется несколько десятков разнотипных MSSQLSERVER, разных редакций. Express превалирует, но это – не важно. Инфраструктура – старая, целиком унаследованная, и — «кусочно-непрерывная».

Задача: «запустить один и тот же запрос/пакет на нескольких серверах в нескольких базах данных», у нас, что называется, «редко бывает, но часто случается».

И то, что это «редко бывает» — не позволяет изобрести достойного обоснования для закупки или написания полноценного софта для централизованного администрирования всего зоопарка, а то, что «часто случается» — бывает, требует мгновенного решения в стиле «5 секунд до взрыва».
Но всё это – лирическое отступление, дисклаймер и всё такое.

Что нам понадобится для запуска скрипта на множестве серверов, во множестве разных баз данных? Кроме самого этого скрипта, разумеется, который лежит у нас, ну, хотя бы, на рабочем столе: «C:Usersusr_rootDesktopcheck_version.sql».

Скрипт может быть абсолютно любым, сгенерированным, или написанным вручную, поэтому здесь его не привожу. Однако нужно иметь ввиду, что Use [database] из него нужно удалить.

Итак, нам понадобятся:

1. Привилегии, позволяющие к коннектиться к серверу и выполнить этот скрипт. Кто сказал sysadmin?

2. Список серверов с базами, в которых вы собираетесь запустить скрипт
Список, конечно, лучше хранить в табличке где-нибудь на сервере, но я, обычно, обхожусь табличным конструктором.

Ну и само собой, разумеется, лучше, чтобы коннект к серверам осуществлялся бы в Windows mode, что позволит не демонстрировать случайным глазам пароли привилегированных пользователей.

3. SSMS, на начальном этапе, чтобы сформировать скрипт для запуска на других серверах.

4. SSMS или sqlcmd, чтобы его выполнить.

Приступим.

1. Запустим на любом экземпляре сервера следующий скрипт:

Select N'
:connect ' + t.[server] + isNull(N' -U ' + t.[user] + N' -P ' + t.[pwd],N'') + N'
Use ' + t.base + N'
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO
'  
From (Values
			(N'dbt0300',N'5.144.18.12', N'sa', N'pwd@','Бурятия'),
			(N'dbt1100',N'S01100s0', NULL, NULL,'Коми'),
			(N'dbt1200',N'S01200s11', NULL, NULL,'Марий-Эл'),
			(N'dbt1300',N'S01300', NULL, NULL,'Мордовия'),
			(N'dbt1800',N'S01800', NULL, NULL,'Удмуртия'),
			(N'dbt2300',N'S02300', NULL, NULL,'Краснодар'),
			(N'dbt2800',N'S02800', NULL, NULL,'Благовещенск'),
			(N'dbt2900_1',N'S02900-backup', NULL, NULL,'Архангельск'),
			(N'dbt3000',N'S03000-2', NULL, NULL,'Астрахань'),
			(N'dbt3400',N'S03400_tds', NULL, NULL,'Волгоград'),
			(N'dbt3600',N'S03600_s1', NULL, NULL,'Воронеж'),
			(N'dbt3700_1',N'S03700-s1', NULL, NULL,'Иваново'),
			(N'dbt3900',N'S03900s1', NULL, NULL,'Калининград'),
			(N'dbt4100',N's04100s2', NULL, NULL,'Камчатка'),
			(N'dbt4900',N'S04900s0', NULL, NULL,'Магадан'),
			(N'dbt5600',N'S05600v', NULL, NULL,'Оренбург'),
			(N'dbt5700_release',N'S05700v', NULL, NULL,'Орел'),
			(N'dbt6100',N'S06100s1', NULL, NULL,'Ростов'),
			(N'dbt6200',N'S06200-1', NULL, NULL,'Рязань'),
			(N'dbt6500',N'S06500', NULL, NULL,'Сахалин'),
			(N'dbt6501',N'S06500-2', NULL, NULL,'Сахалин'),
			(N'dbt6600',N'S06600-1', NULL, NULL,'Екатеринбург'),
			(N'dbt7400',N'S07400-01', NULL, NULL,'Челябинск'),
			(N'dbt6700',N'S06700-bd01', NULL, NULL,'Смоленск'),
			(N'dbt7000_0',N'S07000-01', NULL, NULL,'Томск'),
			(N'dbt7200',N'S07200-02', NULL, NULL,'Тюмень'),
			(N'dbt7700',N'S07710-01', NULL, NULL,'Москва'),
			(N'dbt8600',N'S08600-s1', NULL, NULL,'Югорск'),
			(N'dbt8700',N'8.12.36.11',N'sa', N'sapwd' ,'Чукотка'),
			(N'dbt8900',N'S08950s3', NULL, NULL,'Ямало-ненецкий АО'),
			(N'dbt5000_zzz',N'S09700s1', NULL, NULL,'Московская область')) t(base, [server], [user], [pwd], [name])
For xml path(N''), type

2. Щелкнем по результату выполнения

Отправка запроса на указанные базы данных MS SQL Server всех указанных серверов подручными средствами - 1

3. В открывшемся окне сделаем Ctrl+A, Ctrl+C.

4. Откроем окно нового запроса, сделаем Ctrl+V.

5. В пункте меню Query SSMS выберем «SQLCMD mode». Скрипт сразу окрасится в веселые серые тона, что говорит о том, что команды sqlcmd — распознаны:

Отправка запроса на указанные базы данных MS SQL Server всех указанных серверов подручными средствами - 2

6. Вуаля! Можно запускать. Скрипт поочередно выполнится на нескольких серверах, без регистрации и смс… эээ… пардон, без C#, PowerShell и прочих чудес, на которых обычно нет ни времени, ни… нет, ну желание есть. Просто лень.

Какой скрипт получился в результате(+)


:connect 5.144.18.12 -U sa -P pwd@
Use dbt0300
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S01100s0
Use dbt1100
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S01200s11
Use dbt1200
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S01300
Use dbt1300
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S01800
Use dbt1800
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S02300
Use dbt2300
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S02800
Use dbt2800
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S02900-backup
Use dbt2900_1
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S03000-2
Use dbt3000
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S03400_tds
Use dbt3400
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S03600_s1
Use dbt3600
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S03700-s1
Use dbt3700_1
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S03900s1
Use dbt3900
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect s04100s2
Use dbt4100
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S04900s0
Use dbt4900
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S05600v
Use dbt5600
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S05700v
Use dbt5700_release
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06100s1
Use dbt6100
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06200-1
Use dbt6200
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06500
Use dbt6500
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06500-2
Use dbt6501
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06600-1
Use dbt6600
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S07400-01
Use dbt7400
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S06700-bd01
Use dbt6700
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S07000-01
Use dbt7000_0
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S07200-02
Use dbt7200
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S07710-01
Use dbt7700
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S08600-s1
Use dbt8600
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect 8.12.36.11 -U sa -P sapwd
Use dbt8700
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S08950s3
Use dbt8900
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

:connect S09700s1
Use dbt5000_zzz
GO 
:r "C:Usersusr_rootDesktopcheck_version.sql"
GO

Дополнительно хочу сказать, что если скрипт выбирает какие-то данные, генерируя (возможно, несколько) результирующих наборов данных – то они, как обычно, появятся в «подвале для данных» окошка запроса SSMS, откуда их можно будет выгрести чем-нибудь типа ssmsbooster, который, с некоторых пор, к сожалению, стал сурово платным.

Ну или просто поменять режим отображения результата на «Result to Text» или «Result to File» а потом распарсить полученный текст с разделителями чем-то вроде Excel.

Впрочем, можно воспользоваться и командой :out , которую также можно прописать в скрипт, наряду с :connect и :r
Вот, собственно, и вся магия.

К недостаткам метода можно отнести, что скрипты выполняются на серверах последовательно, но, т.к. этот лайфхак относится, скорее, к разряду подручных средств – вряд ли это имеет большое значение.

Ну и ссылки по теме:

Edit SQLCMD Scripts with Query Editor
An Introduction to the SQLCMD Mode in SSMS
Using the SSMS Query Editor in SQLCMD Mode

Автор: uaggster

Источник