OneScript на страже порядка на сервере тестовых баз данных

14.06.22

База данных - Администрирование СУБД

Наводим порядок на сервере тестовых баз с помощью любимого инструмента - OneScript. Находим заброшенные базы на сервере MS SQL, определяем кандидатов на удаление.

Любите ли вы OneScript так, как люблю его я? Кажется, с помощью OneScript и небольшой смекалки можно сделать вообще все, что угодно.

Стало казаться, что на сервере MS SQL для тестовых целей слишком много развелось баз. Какие-то делали для подрядчиков, какие-то для своих разработчиков, поднимали копии для пользователей. И вроде бы хозяев на сервере раз-два, и базы при их заведении назывались по шаблону, чтобы не было сомнений, для кого и чего она предназначена, но уровень бардака повысился, это прямо чувствуется. А бардак я не люблю. Да и потребовалось место для новых баз.

Для того, чтобы осуществить круговорот тестовых баз в природе (чтобы добавить нужные, сначала удалить ненужные), надо обладать достоверными знаниями о том, что можно удалить.

И вот в "пустоту" (общий список рассылки айтишников компании) уходит письмо: "Уважаемые коллеги, признавайтесь чьи базы и нужны ли они...". Скудные ответы не дают ясности. Что же, придется поработать самому.

К делу!

 

Вспоминаем, как пишутся запросы в MS SQL

Поработаем в консоли сервера MS SQL, попробуем нащупать пути решения...

Я-ж программист, для начала выведу список баз на сервере и пространство, которое они занимают. В MS SQL у нас есть хранимая процедура sp_databases, она предоставляет список баз данных, которые находятся в экземпляре SQL Server. Отлично, есть имя базы и ее размер в килобайтах (тут размер всех файлов, относящихся к базе данных - и сама БД и логи).

 

exec sp_databases

 

В список баз попадают все базы - и 1С и системные master, model, tempdb. Но меня сейчас интересуют только базы 1С. Как же понять, что эта база используется сервером 1С? Можно посмотреть зарегистрированные базы на кластерах серверов 1С, но их у меня несколько, да и чувствую, что можно ответить на этот вопрос проще. В любой 1С-ной базе есть служебные таблицы, одна из них - config, где хранятся бинарные данные описания конфигурации. Это идеальная таблица для наших целей, ведь в ней есть столбец Modified, если взять максимальную дату из него - это будет дата последнего обновления конфигурации. Выполним запрос.

 

USE base1c
SELECT MAX(DATEADD(YEAR , -2000 , Modified)) Modified 
FROM dbo.Config

 

Здесь мы еще учли стандартное смещение дат, которое использует сервер 1С. И вуаля - этот запрос для базы 1С (base1c) в примере) вернет дату и время. А вот запрос для tempdb не выполнится - у базы данных tempdb нет таблицы config и колонки Modified в ней. Так одним запросом мы убьем двух зайцев - поймем, что это база 1С (если запрос выполнится) и узнаем время изменения конфигурации базы данных.

Отлично, выполним этот запрос для каждой из баз данных, полученных на предыдущем этапе, и поймем, 1С это или нет и время последнего изменения конфигурации.

А когда создали базу? Посмотрим системное представление sys.databases - выводит свойства каждой базы на сервере. Нам оттуда нужно только поле create_date

SELECT name DATABASE_NAME, CREATE_DATE
FROM sys.databases

 

Какая еще информация поможет мне разобраться со списком баз? Думаю, дата разворачивания из бекапа пригодится. Например, конфигурацию базы меняли год назад, но саму базу развернули из бекапа недавно - чтобы разобраться с каким-то изменением данных. В базе данных msdb есть таблица restorehistory, она содержит по одной строке для каждой операции восстановления базы. Сгруппируем данные по имени базы:

SELECT 
destination_database_name DATABASE_NAME, max(restore_date) LAST_RESTORE_DATE
FROM msdb..restorehistory
group by destination_database_name

 

И самое главное - как понять, что базой вообще пользуются? Когда пользователь заходит в базу, сервер 1С пишет какие-то данные в служебные и основные таблицы. А что происходит при записи в базу данных? Правильно, обновляются индексы. Обратимся к системному динамическому представлению sys.dm_db_index_usage_stats. Это представление возвращает количество различных операций с индексами и время, которое было затрачено на последнее выполнение операции каждого типа. Единственный недостаток - при перезапуске SQL сервера эти данные теряются. Кстати, по дате создания таблицы tempdb мы можем определить дату перезапуска сервера SQL, возьмем ее, когда будем анализировать данные таблиц.

SELECT MAX(last_user_update) As last_user_update, DB_NAME(database_id) AS DATABASE_NAME
FROM sys.dm_db_index_usage_stats
GROUP BY DB_NAME(database_id)

Здесь нам нужно только имя базы и максимальное время последнего update, поэтому сгруппируем данные.

 

 

Ох, как интересно, есть базы, в которые не заходили больше двух месяцев, а то и больше.

Что же, у нас есть все данные. Можно сделать мега-запрос и получать всю информацию из консоли сервера MS SQL, но лично мне тяжело поддерживать такие запросы. Я напишу программу на православном OneScript.

 

Напишем программу, которая будет делать все это сама

Нам надо научиться выполнять запросы к серверу MS SQL из OneScript. Впрочем учиться не надо, ведь в библиотеке пакетов для OneScript обязательно найдется что-то. Посмотрим на подходящие пакеты:

 

cpdb Набор скриптов oscript для копирования баз данных 1C / MS SQL и развертывания на целевой системе
TMSSQL Приложение для работы с базами данных на MS SQL Server
sql Внешняя компонента реализующая возможность работы с базами данных из onescript

 

Признаюсь, я работал с каждым из этих приложений-пакетов. cpdb - крутая штука, в основном годится для копирования баз данных (огромный респект Артему Кузнецову за функцию отключения от хранилища). Несмотря на то, что там предусмотрена возможность выполнять произвольные запросы, мне не очень удобно ее использовать для целей этой статьи. 

Пакет sql - мой выбор.

Посмотрите как легко выполнить запрос на сервере MS SQL:

#Использовать sql

Функция ВыполнитьЗапросНаСУБД(ТекстЗапроса, Таймаут)

	СоединениеБД = Новый Соединение();

	СоединениеБД.ТипСУБД = СоединениеБД.ТипыСУБД.MSSQLServer;
	СоединениеБД.Сервер = Сервер.Адрес;
	СоединениеБД.ИмяПользователя = Сервер.Логин;
	СоединениеБД.Пароль = Сервер.Пароль;

	СоединениеБД.Открыть();
    
        ЗапросСУБД = Новый Запрос();
	ЗапросСУБД.УстановитьСоединение(СоединениеБД);
	ЗапросСУБД.Текст = ТекстЗапроса;
	ЗапросСУБД.Таймаут = Таймаут;
	
	РезультатЗапроса = ЗапросСУБД.Выполнить().Выгрузить();

	СоединениеБД.Закрыть();
	
	Возврат ЗначениеДляВозврата;

КонецФункции

Почти как в 1С. Указываем текст запроса, таймаут, из переменной сервер берем данные подключения и вот у нас результат запроса.

Ну собственно, самое главное у нас есть. Сделаем мини-приложение на OneScript  со следующей структурой:

 

 

Мы изолируем функции работы с SQL в модуле КомандыSQL.os, модуль ДанныеСерверов.os будет содержать в себе параметры доступа к серверам - адрес сервера, имя пользователя, пароль и так далее. Основная работа будет проходить в скрипте base_list.os, подключим к нему вышеописанные модули инструкцией

#Использовать "."

В модуле КомандыSQL разместим функцию ВыполнитьЗапросНаСУБД и напишем функции получения каждой из таблиц, которые мы научились формировать ранее.

// Список баз на сервере - Получает список баз на сервере
//
// Параметры:
//  ПараметрыСервера - Структура - см. ДанныеСерверов.Сервера()[ИдентификаторСервера]      
// Возвращаемое значение:
//   ТаблицаЗначений:
//     * DATABASE_NAME - Строка - Имя базы данных 
//     * DATABASE_SIZE - Число - Размер базы данных (общий)
//
Функция СписокБазНаСервере(ПараметрыСервера) Экспорт
	
	Сервер = ПараметрыСервера;

	ТекстЗапроса = "
	|sp_databases
	|";
	
	Возврат ВыполнитьЗапросНаСУБД(ТекстЗапроса, 10);
		
КонецФункции

Аналогично сделаем и другие функции получения данных. По сути, эти функции содержат только одно изменение - текст запроса. Они получились лаконичными и легкими для понимания.

Теперь напишем основной скрипт.

Получаем данные доступа к серверу по его имени. Создаем таблицу значений с нужным нам составом колонок. Получаем список баз на сервере. Получаем остальные данные и обогащаем ими таблицу.

Сервер  = ДанныеСерверов.Сервера()[ИдентификаторСервера];

ИнформацияОБазах = СоздатьТаблицуИнформацияОБазах();

БазыСервера = КомандыSQL.СписокБазНаСервере(Сервер);

ТаблицаСДаннымиБекапа = КомандыSQL.ПоследниеРазворачиванияИзБекапа(Сервер);
ТаблицаСПоследнимиИзменениямиДанных = КомандыSQL.ПоследнееИзменениеДанных(Сервер);
ТаблицаСДатойСозданияБаз = КомандыSQL.ДатыСозданияБаз(Сервер);

Для Каждого СтрокаБазы Из БазыСервера Цикл
	
	ИмяБазы = СтрокаБазы.DATABASE_NAME;
	ДатаИзмененияКонфигурации = КомандыSQL.ВремяПоследнегоИзмененияКонфига(ИмяБазы, Сервер);
	
	НоваяСтрока = ИнформацияОБазах.Добавить();
	НоваяСтрока.DATABASE_NAME = ИмяБазы;
	НоваяСтрока.LAST_CONFIG_DATE = ДатаИзмененияКонфигурации;
	Если ЗначениеЗаполнено(ДатаИзмененияКонфигурации) Тогда
		НоваяСтрока.BASE_1C = Истина;
	Иначе
		НоваяСтрока.BASE_1C = Ложь;
	КонецЕсли;
	НоваяСтрока.DATABASE_SIZE = СтрокаБазы.DATABASE_SIZE;

	НайденнаяСтрока = ТаблицаСДаннымиБекапа.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.BACKUP_RESTORE_DATE = НайденнаяСтрока.last_restore_date;
	КонецЕсли;
	
	НайденнаяСтрока = ТаблицаСПоследнимиИзменениямиДанных.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.LAST_UPDATE_DATE = НайденнаяСтрока.last_user_update;
	КонецЕсли;
	
	НайденнаяСтрока = ТаблицаСДатойСозданияБаз.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.CREATE_DATE = НайденнаяСтрока.CREATE_DATE;
	КонецЕсли;

КонецЦикла;

Готово! Вся информация у нас есть. Осталось ее сохранить куда-нибудь на диск или выдать список баз - кандидатов на удаление.

Как сохранить таблицу значений в oscript на диск? Очень просто - я сформирую текстовый файл csv с разделителями, который без труда открывается обычным табличным редактором. Код простой, спрячу его под спойлер.

 
 Сохранение таблицы значений на диск

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

Да уж, есть базы, которыми не пользуются и они занимают порядочно места.

 

 

Текст скрипта опубликован на гитхабе - DB_Magic

Запускать его так:

 

OneScript MS SQL управление базами данных

См. также

DevOps и автоматизация разработки OneScript Системный администратор Программист Стажер Бесплатно (free)

Рассмотрим создание самоформирующейся документации через комментарии и соглашения: как это сделать и зачем, с описанием полного цикла от исходников конфигурации до странички в интернете

17.06.2024    2572    bayselonarrend    2    

56

Администрирование СУБД Системный администратор Платформа 1С v8.3 Бесплатно (free)

Ситуация: при обновлении серверной базы данных произошёл сбой и теперь невозможно войти ни в конфигуратор, ни в 1С:Предприятие по причине ошибки, вынесенной в заголовок. Рецепт лечения.

24.05.2024    1283    Kernelbug    9    

19

Администрирование СУБД Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

При хранении файлов в томах на диске они иногда исчезают. Разбираемся, почему.

23.05.2024    7890    human_new    18    

54

OneScript Программист Стажер Бесплатно (free)

Поговорим про меню, спиннеры, прогресс-бары и прочие свистелки для CLI приложений на OneScript

20.05.2024    2299    bayselonarrend    18    

70

Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Бесплатно (free)

Статья для тех, кто столкнулся с необходимостью пересчета итогов для "больших таблиц" и нет возможности поставить на паузу ИБ для проведения работ.

25.04.2024    2544    virustam    33    

12

Администрирование СУБД Системный администратор Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

В статье показано, как устранить ошибку выполнения скрипта "Создать сервис RAGENT" в системах 1С:Центр контроля качества или в 1С:Центр автоматизации. Будет полезна администраторам ЦКК и ЦА, которые только начали знакомство с этими системами.

18.04.2024    498    artemusII    0    

8

Администрирование СУБД Системный администратор Бесплатно (free)

Скрипт позволяет высвобождать место в операционную систему, занятое файлом базы MS SQL в итерациях с заданным количеством мегабайт

28.03.2024    1624    Garilia    6    

16

Групповая разработка (Git, хранилище) OneScript Системный администратор Программист Бесплатно (free)

Сегодня мы посмотрим на Github Actions - встроенный инструментарий Github для автоматизации рабочих процессов. Разберем, что это такое, зачем и причем тут OneScript.

25.03.2024    1890    bayselonarrend    3    

41
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. ktb 622 14.06.22 13:12 Сейчас в теме
Можно попробовать использовать https://github.com/ArKuznetsov/cpdb в качестве библиотеки, если каких-то функций не хватает, то сделать issue или PR, я добавлю.
И еще, для уменьшения занимаемого места можно использовать компрессию страниц в cpdb есть соответствующая функция, а если нужен отдельный скрипт, то тут: https://gist.github.com/arkuznetsov/3775de77ae6d05db297cd61d2e34aef9#file-ms_sql_all_db_compression-sql
JohnyDeath; ardn; +2 Ответить
6. ardn 629 14.06.22 14:13 Сейчас в теме
(1)
качестве библиотеки, если каких-то ф

Был опыт issue, не зашло - все-таки cpdb - инструмент для решения конкретных задач.
8. ktb 622 14.06.22 14:14 Сейчас в теме
(6) Ну вот расширился спектр решаемых задач на днях. А что за опыт с issue когда не зашло?
2. artbear 1556 14.06.22 13:40 Сейчас в теме
(0) Еще бы для Постгри таккой же скрипт )
ardn; ktb; +2 Ответить
3. ktb 622 14.06.22 13:56 Сейчас в теме
(2) В компоненте sql есть возможность работы с PostgreSQL, остается только запросы поправить. В cpdb в принципе есть разделение на компоненты, т.е. тоже нужно только реализовать запросы к PostgreSQL.
5. ardn 629 14.06.22 14:11 Сейчас в теме
(2) Пока до Постгри не дорос. Но наверное его уже пора изучать
4. ktb 622 14.06.22 13:58 Сейчас в теме
А еще можно сделать приложение на OneScript.Web и выдавать всю информацию сразу в браузер. :-)
7. ardn 629 14.06.22 14:13 Сейчас в теме
(4)Что ж вы душу то травите...
Dimanchik00; +1 Ответить
9. AntonProgma 47 16.06.22 08:17 Сейчас в теме
Неужели, использовать onescript проще, чем python?
10. ardn 629 16.06.22 09:56 Сейчас в теме
(9)
Конечно. Вы действительно думаете, что среди 1Сников много тех, кто знает питон?
11. AntonProgma 47 16.06.22 11:03 Сейчас в теме
(10) нет, но

во-первых, питон проще и выразительнее языка 1с;
во-вторых, для питона больше готовых библиотек;
в-третьих, обширная документация, огромное количество мануалов и примеров;
в-четвертых, onescript все равно предполагает использование незнакомых конструкций и объектов.
12. ardn 629 16.06.22 11:25 Сейчас в теме
(11)
Аргументы звучат здраво. Вы уже почти убедили меня начать изучать питон.
Не согласен с первым - 1С все же проще и выразительней, впрочем лично для меня.
Не согласен с четвертым - onescript почти полный аналог 1С, лично мне все знакомо.
14. AntonProgma 47 16.06.22 11:36 Сейчас в теме
(12) вы ещё не начали изучать питон, но считаете, что 1с проще и выразительней. Реально, попробуйте то же самое на питоне написать с использованием библиотеки sql от самой Майкрософт. Будете приятно удивлены.
13. artbear 1556 16.06.22 11:32 Сейчас в теме
(11) Дискуссия 1скрипт vs Python\Powershell идет давно, с момента появления 1скрипт.

И постепенно 1скрипт продвигается все дальше и дальше, потому что работают его главные плюсы
- 1снику не нужно слишком уж переучиваться
- готовые библиотеки и приложения
- опенсорс-поддержка

те, кто юзает Python\Powershell, попадают на довольно сильную зависимость от конкретных людей, знающих эти технологии.
а это минус для команды\компании, фактор автобуса.

каждый разработчик\администратор, каждая команда выбирает для себя, что использовать.
Berckk; ktb; NeLenin; ardn; +4 Ответить
15. AntonProgma 47 16.06.22 11:42 Сейчас в теме
(13) не могу понять, как можно испытывать трудности при изучении питона, это же язык для обучения программированию! Там всякие классы; функции, как объекты первого класса; модульность и так далее. С этими штуками кода получается в разы меньше.
16. artbear 1556 16.06.22 12:41 Сейчас в теме
(15) больше вопросов не при обучении, а в дальнейшей поддержке сделанных инструментов.
если обращаться к питону эпизодически, знания будут забываться и придется делать немало усилий для восстановления при очередной итерации поддержки\доработки.

мы этого наелись и с питоном, и с пауэршеллом (
viktor3d; ardn; +2 Ответить
17. AntonProgma 47 16.06.22 13:03 Сейчас в теме
(16) дело хозяйское. Но на мой взгляд похоже на добровольное монашество.
20. Сто27001 18.06.22 19:13 Сейчас в теме
(15) что бы быстро бегать, надо бегать регулярно. Много ли 1Сников, которые регулярно пишут на питоне? Нет.
21. AntonProgma 47 19.06.22 13:20 Сейчас в теме
(20) Лучше и не скажешь! Хочешь уметь быстро и долго бегать - практикуйся и велосипедом не пользуйся.

Мне кажется, питоновский код доступен для понимания программисту, который питон вообще не знает:
def СоздатьФайл(ИмяСервера):
    Поля = 'DATABASE_NAME;BASE_1C;DATABASE_SIZE;BACKUP_RESTORE_DATE;LAST_UPDATE_DATE;LAST_CONFIG_DATE;CREATE_DATE;SERVER_RESTART_DATE'
    СписокПолей = Поля.split(';')

    Сервер = СЕРВЕРЫ.get(ИмяСервера, {})
    ДанныеСервера = ПолучитьДанныеСервера(Сервер)
        
    ИмяФайла = f'out_{ИмяСервера}_{ПредставлениеЗначения(datetime.date.today())}.csv'
    with open(ИмяФайла, 'w') as Файл:
        Файл.write(Поля)
        for СтрокаДанных in ДанныеСервера:
            Файл.write('\n' + ';'.join([ПредставлениеЗначения(СтрокаДанных[Поле]) for Поле in СписокПолей]))
        print(f'Create file: {ИмяФайла}', file=sys.stdout)
Показать

Это адаптация создания csv-файла из текущей публикации.

Единственную сложность может представлять непривычная конструкция [ПредставлениеЗначения(СтрокаДанных[Поле]) for Поле in СписокПолей]. Но она означает создать список (массив) из вызовов ПредставлениеЗначения(СтрокаДанных[Поле]) для каждого Поля из СпискаПолей. Красивая возможность написать "сложноподчиненное предложение" вместо нескольких односложных:
СтрокаФайла = []
for Поле in СписокПолей:
        СтрокаФайла.append(ПредставлениеЗначения(СтрокаДанных[Поле])
Файл.write('\n' + ';'.join(СтрокаФайла))
18. AntonProgma 47 16.06.22 18:09 Сейчас в теме
(13) возникло 3 вопроса:

Не приводит ли юзание onescript к зависимости от людей, знающих эту технологию?

Кто чаще встречается в природе, питонщики или ванскрипторы?

Какая технология имеет шансы раньше загнуться: питон или ванскрипт?
19. ardn 629 16.06.22 18:15 Сейчас в теме
(18) Позволите ответить?
1. Все дело в том, что любой 1с-ник без труда прочитает и отредактирует код, написанный на OneScript, все знакомо
2. В организации, где автоматизируют на 1С, как правило 1С-ников больше, соответственно и ванскрипторов. У нас в конторе, например, нет ни одного питонщика.
3. Пока мы пишем на OneScript, он живет.
22. AntonProgma 47 19.06.22 14:01 Сейчас в теме
(19) Ради интереса переписал код публикации на питоне, на котором последний раз писал пару лет назад. Могу выложить, если захотите. Но главное, что я понял, -- предложения использовать более выразительный инструмент составления программы вас не заинтересует, потому что и на 1с можно было бы написать элегантнее. Позволите, критику от коллеги?
1) наверняка, можно использовать одно соединение с базой данных, а не создавать его под каждый запрос;
2) если можно, то лучше сделать 1 запрос с левыми соединениями вместо нескольких, чтобы в коде не сопоставлять строки разных таблиц;
3) почему бы сразу не писать в текстовый файл, минуя промежуточное создание таблицы значений? Или для упрощения кода записи в файл можно вместо таблицы сделать массив массивов, а потом СтрСоеднить поля через ";", а строки через Символ.ПС
4) каким-то сложным кажется использование СтрШаблон для обычной конкатенации строк. Плюсик был бы очевидней.
5) также сложными являются классические конструкции типа
Если ЗначениеЗаполнено(ДатаИзмененияКонфигурации) Тогда
	НоваяСтрока.BASE_1C = Истина;
Иначе
	НоваяСтрока.BASE_1C = Ложь;
КонецЕсли;

не лучше ли?
НоваяСтрока.BASE_1C = ЗначениеЗаполнено(ДатаИзмененияКонфигурации)
artbear; ardn; +2 Ответить
23. ardn 629 20.06.22 10:12 Сейчас в теме
(22)
являются классические конструкции типа

Отлично, спасибо!
Оставьте свое сообщение