Всем привет! Меня зовут Александр Гаврилов, я архитектор баз данных и аналитических систем в GRI. Если вы когда-нибудь пытались выполнить одну и ту же операцию с похожими таблицами в разных базах, да ещё и на разных серверах, то знаете, насколько это может быть мучительно.
В этой статье я покажу один из рабочих вариантов, как упростить такую задачу, и заодно расскажу про интересную функцию XQuery, которая может неожиданно помочь.
Проблема
Достаточно часто в моей практике возникают ситуации, когда с разными таблицами, часто даже расположенными в разных базах, требуется производить однотипные операции. Например, очищать данные в таблицах за определённый период в нескольких одинаковых конфигурациях 1С.
Логика во всех базах может быть одинаковая, но на уровне базы данных названия таблиц и полей могут быть совершенно разные. И даже если тиражировать эти базы 1С из эталонной, при дальнейших изменениях конфигурации на уровне БД новые таблицы могут называться не единообразно. Так, в конфигурации 1С таблица может носить название «Журнал запросов», а на уровне SQL при развёртывании конфигурации в первый раз она может получить название [_InfoReg345], во второй — [_InfoReg543].
Кроме того, даже в пределах одной базы данных может быть много таблиц с журналами: в одной фиксируются подключения, в другой — количество слов в чате, и т. п.
Время идёт, количество записей копится и приходит момент, когда решают, что нужно вычистить устаревшие данные.
Формализация требований
Прежде чем начать что-то делать, нужно понять:
что у нас есть;
что от нас хотят;
что мы хотим получить в результате.
Формализация требований — очень важный этап любой разработки. Именно на этом этапе определяется объём функциональности, который закроет требования, но не будет избыточным. Избыточность функциональности — это такая же ошибка проектирования: потраченные силы и время на то, что никогда не пригодится.
Также при формализации требований нужно не забыть про такой момент, как последующая поддержка получившегося продукта.
При постановке задачи не будет лишним ответить на вопросы: «Что?», «Где?», «Когда?». Ответ на «Что?» должен содержать информацию о выполняемом действии, ответ на «Где?» — границы области применения, а ответ на «Когда?» должен формулировать, насколько часто должно выполняться указанное действие.
Итак, ответим на вопросы:
Вопрос: «ЧТО?»
Ответ: удалить данные из таблицы, фильтруя их по полю типа «дата». Удаляются данные старше определённого количества дней. Для каждой таблицы этот параметр может быть установлен отдельно.
Вопрос: «ГДЕ?»
Ответ: на произвольном сервере в произвольной базе данных. Список серверов, баз, таблиц и полей получаем из подготовленного списка.
Вопрос: «Когда?»
Ответ: раз в неделю по расписанию.
Варианты выбора решения. Он же мозговой штурм
На этом этапе может возникнуть несколько вариантов решений. Возможно, некоторые из них выглядят изначально бредово, но не стоит отвергать их сразу. Даже в идее, которая не будет впоследствии использована, может быть рациональное зерно, которое приведёт к правильной мысли. Рассмотрим примеры.
Первый вариант. Просто скрипт. Если нам нужно выполнить данное действие один раз, то достаточно просто написать скрипт для удаления, запустить его, полюбоваться очищенной таблицей и считать задачу выполненной. Но требование периодического выполнения перечёркивает этот вариант.
Достоинства: простота реализации.
Недостатки: практически не поддерживаемое решение.
Второй вариант. Скрипт в хранимке. Написать свою хранимую процедуру для каждой базы и таблицы. Можно даже упростить задачу: составить список таблиц и полей, написать генератор кода, разлить по серверам и каждый раз менять исходный код хранимой процедуры при изменении требований. Кроме того, для каждой таблицы будет своя отдельная процедура.
Достоинства: несложная реализация.
Недостатки:
трудности с поддержкой;
захламление базы данных объектами.
Третий вариант. Параметризованная хранимая процедура. Сделать хранимую процедуру, которая в качестве входных параметров будет принимать название таблицы, название поля, по которому будет выполняться фильтрация, и дату, до которой нужно удалять. Это уже лучше, чем вышеописанные варианты, так как значительно упрощается написание и поддержка. Однако большое количество параметров может вызвать путаницу и привести к ошибкам при указании, в какой базе и с какой таблицей что делать.
Достоинства:
гораздо проще поддерживать;
одна хранимая процедура на базу данных.
Недостатки: остаётся опасность перепутать таблицы при заполнении параметров.
Четвёртый вариант. Config для процедуры. Создать файл конфигурации, в который внести все варианты выполнения хранимой процедуры, передавать эту конфигурацию в виде параметра в хранимую процедуру и просто указывать, что в этот раз мы хотим сделать.
Достоинства:
все данные по базам и таблицам собраны в одном месте;
код хранимой процедуры не изменяется со временем;
можно сделать единую утилиту для запуска как в пределах одного сервера, так и в рамках всех серверов;
более лёгкий менеджмент при помещении в систему хранения версии, например, Git или gitlab. Кроме того в рамках того же gitlab можно настроить авторазвёртывание этого решения.
Недостатки: более сложная реализация.
Хотя этот вариант выглядит сложнее в реализации, он может оказаться проще всего в последующей поддержке. Кроме того, в четвёртом варианте код будет одинаковый для всех серверов баз данных и таблиц.
В рамках мозгового штурма можно придумать ещё много разных вариантов. И хотя для реализации уже был выбран четвёртый вариант, можно привести пару из из тех, что были придуманы. Возможно, эти варианты могут получить развитие, если существуют соответствующие им корпоративные стандарты или если есть личные предпочтения к одному из ниже предложенных вариантов решения.
Дополнительные варианты
Пятый вариант. Integration Service. Написать пакет для Integration Service, в котором список серверов баз данных и таблиц будем получать из хранимой на каком-либо сервере таблицы словаря, с последующим в рамках этого пакета подключением к базам данных и удалением необходимых элементов из таблиц.
Недостатки:
Усложнённая управляемость изменениями.
Всё равно придётся писать код формирования динамического TSQL.
Усложнение управления настройками. Как минимум, для их изменения или добавления придётся редактировать записи в базе данных, что может повлечь за собой создание графического интерфейса.
Шестой вариант. Отдельное приложение. Написать приложение на каком-либо языке высокого уровня, например, С#, Python, Powershell или ином. В настоящее время большинство языков высокого уровня позволяют достаточно просто работать с базами данных. Однако этот вариант опять приводит нас к необходимости хранения конфигурационного файла или таблицы с настройками. И итогом всё равно будет формирование динамического TSQL с последующим его выполнением на SQL Server/
Достоинства:
Позволяет достаточно просто хранить исходный код в системе хранения версий.
Обладает примерно теми же достоинствами, что и четвёртый вариант, при условии хранения настроек в локальном конфигурационном файле.
Недостатки: в отличие от четвёртого варианта, выполнение на стороне MS SQL-сервера становится более трудоёмким, если настраивать выполнение в SQL Server Agent.
Реализация конфигурационного файла
Сначала нужно определить, что будем хранить в конфигурационном файле:
Метаданные таблицы, которую будем обрабатывать, — это необходимый минимум.
Данные сервера и базы данных, в которой это будет происходить, чтобы контролировать, что выполнение происходит на нужном сервере и в нужной базе данных.
Данные по ограничениям для удаления. То, ради чего это всё затевалось.
Возможно метаданные таблицы журнала, чтобы записывать в него наши действия.
В результате должно получиться что-то похожее:
<TestContour dbName="dev_database" serverName="SQL-SERVER1"> <TableToClear name="_Reference12" description="Справочник.ЖурналЧегоТоТам"> <CreateDate name="_Fld53" description="ДатаСоздания" daysToLeft="100"/> </TableToClear> <LogTable name="_Reference12051" description="Справочник.ЖурналОбработки"> <TextField name="_Fld57" description="ТекстСообщения" /> </LogTable> </TestContour>
Почему выбрали XML, а не JSON или CSV? Если рассматривать объективно, то все три формата нативно поддерживаются MS SQL. В данном случае на выбор повлияло то, что формат XML удобнее в чтении. Так, например, в атрибуте description можно указать расшифровку таблицы или поля, которое описывается в данной строке. В примере выше определён тег TestContour, в котором собрана вся информация, относящаяся к таблицам, посвящённым удалению данных из таблицы reference12. В самом теге TestContour атрибутами определены названия базы данных в dbName и название сервера в serverName.
Также внутри тега определены дочерние теги, описывающие структуры:
TableToClear— структура таблицы, подлежащей очистки;необязательный тег
LogTableдля таблицы журнала, в которую можно записывать информацию о выполненных действиях.
Но это для одной таблицы, а их много. Превращаем XML в конфигурационный файл для всех случаев.
<Settings> <TestContour dbName="dev_database" serverName=”SQL-SERVER1” > <TableToClear name="_Reference12" description="Справочник.ЖурналЧегоТоТам"> <CreateDate name="_Fld53" description="ДатаСоздания" daysToLeft="100"/> </TableToClear> <LogTable name="_Reference513" description="Справочник.ЖурналОбработки"> <TextField name="_Fld157" description="ТекстСообщения" /> </LogTable> </TestContour> <Database1 dbName="database_prod1" serverName=”SQL-SERVER2” > <TableToClear name="_Reference123" description="Справочник.ЖурналЧегоТоТам"> <CreateDate name="_Fld533" description="ДатаСоздания" daysToLeft="100"/> </TableToClear> <LogTable name="_Reference513" description="Справочник.ЖурналОбработки"> <TextField name="_Fld57" description="ТекстСообщения" /> </LogTable> </Database1> <Database2 dbName="database_prod2" serverName=”SQL-SERVER2” > <TableToClear name="_Reference214" description="Справочник.ЖурналЧегоТоТам"> <CreateDate name="_Fld523" description="ДатаСоздания" daysToLeft="100"/> </TableToClear> </Database2> </Settings>
Сейчас в конфигурационном файле данные для трёх таблиц, расположенных на двух разных серверах. Для каждой таблицы информация вынесена в отдельный блок с уникальным именем. Таким образом, при выполнении операции можно будет указать по названию блока, какую таблицу и на каком сервере мы хотим очистить.
Заполнение конфигурационного файла завершено, можно переходить к реализации его чтения и использования в хранимой процедуре.
Использование конфигурационного файла в процедуре
Ранее говорилось, что в хранимую процедуру будет передаваться конфигурация и название выполняемого действия.
CREATE OR ALTER PROCEDURE dbo.sp_ExecuteCleanupByConfig @xmlConfig XML, @settingsName NVARCHAR(128)
Далее в хранимой процедуре нужно отпарсить XML и получить нужные настройки. Использование XML, описанного в конфигурационном файле, позволяет вариативно настраивать получение данных. В зависимости от возникающих требований и предполагаемой логики удаления данных можно использовать жёсткое ограничение, что в одном уникальном теге верхнего уровня описывается только одна таблица, или можно настроить пакетное выполнение на определённом в верхнеуровневом теге сервере таблиц.
Если упростить вышесказанное, то предполагается, что в теге TestContour может быть определено несколько дочерних тегов TableToClear, в каждом из которых определена таблица, подлежащая очистке.
Допустимость такого в определяемом конфигурационном файле можно ограничивать сразу после получения данных из него. Ниже мы это рассмотрим.
Предположим, что по каким-то причинам для одного тега верхнего уровня было определено несколько записей таблиц. Для получения этих данных определим табличную переменную:
DECLARE @SettingsValues TABLE ( serverName NVARCHAR(128), dbName NVARCHAR(128), tableName NVARCHAR(128), fieldName NVARCHAR(128), daysToLeft INT )
Использование табличной переменной вместо временной таблицы не принципиально, но в случае работы с высоконагруженными системами всё-таки предпочтительнее, так как табличная переменная не создаёт транзакционной нагрузки. А с учётом количества предполагаемых записей производительность работы с табличной переменной будет на высоком уровне.
Первоначально мы получим из XML значение для названия сервера базы данных, таблицы и поля в ней, по которому будет выполняться фильтрация, а также количество дней, которые необходимо оставить в базе данных.
Перейдём к получению данных в эту таблицу. Для этого используем встроенные в MS SQL возможности для работы с XML и XQuery.
INSERT INTO @SettingsValues (serverName,dbName,tableName, fieldName, daysToLeft) SELECT T.c.value('@dbName', 'NVARCHAR(128)') AS dbName, T.c.value('@serverName', 'NVARCHAR(128)') AS serverName, T.c.value('(TableToClear/@name)[1]', 'NVARCHAR(128)') AS tableName, T.c.value('(TableToClear/CreateDate/@name)[1]', 'NVARCHAR(128)') AS fieldName , T.c.value('(TableToClear/CreateDate/@daysToLeft)[1]', 'INT') AS fieldName FROM @xmlConfig.nodes('/Settings/*[local-name()=sql:variable("@settingsName")]') AS T(c);
В этом коде обратите внимание на конструкцию получения строк из XML:
FROM @xmlConfig.nodes('/Settings/*[local-name()=sql:variable("@settingsName")]') AS T(c)
Здесь используется запрос xQuery, который для всех дочерних тегов settings выбирает только те, имя которых равняется переданному в параметре @settingsName. Для получения значения имени текущей иерархии используется функция XQuery local-name(). Также хочу обратить внимание на конструкцию использования внутри XQuery переменной, объявленной в коде TSQL.
Далее в коде хранимой процедуры можно добавлять проверки:
на уникальность записи;
корректность сервера;
корректность базы данных;
что выполнение производится на SQL-сервере, объявленном в переданном в параметре теге настроек, и в корректной базе данных.
-- предполагаем что для одной настройки мы удаляем данные не более чем из одной таблицы IF (SELECT COUNT(*) FROM @SettingsValues) > 0 BEGIN RAISERROR('ОШИБКА! Попытка обработать более 1 таблицы!', 11, 1); END -- контроль что цуказанный в настройках сервре равен текущему серверу IF EXISTS(SELECT 1 FROM @SettingsValues WHERE serverName <> @@SERVERNAME ) BEGIN RAISERROR('ОШИБКА! Некорректная база данных!', 11, 2); END -- контроль что указанная в настройках база соответствует текеущей IF EXISTS(SELECT 1 FROM @SettingsValues WHERE dbName <> DB_NAME() ) BEGIN RAISERROR('ОШИБКА! Некорректная база данных!', 11, 2); END
Небольшая ремарка по использованию функциональности RAISERROR: использование уровня ошибки с первой по десятую не вызывает срабатывания CATCH и не прерывает выполнение.
Формирование скрипта удаления
После заполнения табличной переменной у нас есть вся необходимая информация для формирования динамического TSQL. Применим комбинированный подход: непосредственно формирование тела скрипта, внутри которого определяются переменные для фильтрации записей.
Категорически рекомендую использовать функцию QUOTENAME для исключения возможности использования эксплойтов, если файл конфигурации окажется в открытом доступе.
Предпочтительно удалять данные в цикле с количеством ~ 1000 строк за раз. Это позволит избежать продолжительных транзакций и возникающих в связи с этим блокировок. Также советую при выполнении этой операции использовать хинт ROWLOCK — это ограничит блокировку удаляемой строкой и снизит вероятность возникновения блокировок.
Дополнительным блоком внутри цикла стоит предусмотреть остановку удаления. Как сигнал для остановки можно использовать существование глобальной временной таблицы.
Таким образом формирование TSQL принимает следующий вид
DECLARE @deleteQuery NVARCHAR(MAX); SET @deleteQuery = ' DECLARE @deletedRowsCount INT = 1; WHILE @deletedRowsCount > 0 BEGIN DELETE TOP(1000) FROM ' + QUOTENAME((SELECT TOP 1 tableName FROM @SettingsValues)) + ' WITH(ROWLOCK) WHERE ' + QUOTENAME((SELECT TOP 1 fieldName FROM @SettingsValues)) + ' < @filterValue; SET @deletedRowsCount = @@ROWCOUNT IF (OBJECT_ID(''tempdb..##StopDeleteByConfig'') IS NOT NULL) BEGIN DROP TABLE ##StopDeleteByConfig; SET @deletedRowsCount = 0 END END';
Остановлюсь на некоторых моментах.
В конструкции QUOTENAME ((SELECT TOP 1 fieldName FROM @SettingsValues)) необходимо не забыть взять внутренний SELECT в дополнительные скобки, в противном случае интерпретатор кода сообщит об ошибке неправильного синтакса около ключевого слова Select.
< @filterValue — переменная для параметризации запроса, что облегчит работу компилятора планов запросов и исключит двоякое толкование для него.
OBJECT_ID(‘tempdb..##StopDeleteByConfig’) — получение идентификатора глобальной временной таблицы в базе temptb. В случае присутствия в базе такой таблицы будет возвращено значение, отличное от NULL.
Глобальную временную таблицу создаёт скрипт:
CREATE TABLE ##StopDeleteByConfig ( id int )
При создании глобальной временной таблицы для остановки выполнения скрипта не требуется наличие определённого набора полей. Интересен только факт существования такой таблицы.
Глобальная временная таблица после создания будет существовать до тех пор, пока её не удалят или пока существует хотя бы одно подключение, которое к ней обращается, включая то, в котором она создана.
Перейдём к вызову скрипта удаления. Вычисляем дату, по которую нужно удалять:
DECLARE @DaystoLeft INT; SET @DaystoLeft = (SELECT TOP 1 daysToLeft FROM @SettingsValues ) DECLARE @dateToFilter DATETIME; SET @dateToFilter = DATEADD(DAY, -@dateToFilter, GETDATE());
Сам вызов:
EXEC sp_executesql @deleteQuery, N’@filterValue DATETIME’, @dateToFilter;
Почему не просто EXEC? Ведь конструкция EXEC (@deleteQuery) вполне допустима. В качестве ответа можно обратиться к официальной документации, где сказано:
Скомпилированные в среде выполнения инструкции Transact-SQL могут открывать путь вредоносным атакам. При использовании
sp_executesqlследует параметризировать запросы.
При этом процедура sp_executesql позволяет не формировать значение параметров непосредственно в динамическом SQL, а передавать их в технические запросы в виде переменных. Дополнительную информацию по использованию данной хранимой процедуры можно прочитать в документации.
Отдельно скажу про выполнение этого кода в базах 1С. Практически всегда, за исключением достаточно старых баз, при работе с датами 1С используют смещение даты на 2000 лет вперёд. Это позволяет избежать переполнения значения для типа datetime при работе с датами ранее 1753 года. Значение смещения дат в конкретной базе данных 1С всегда можно получить запросом SELECT offset FROM _YearrOffset. Полученное значение необходимо добавлять к вычисленному значению даты.
В итоге общий вид хранимый процедуры принимает следующий вид:
sp_ExecuteCleanupByConfig.sql
CREATE OR ALTER PROCEDURE dbo.sp_ExecuteCleanupByConfig @xmlConfig XML, @settingsName NVARCHAR(128) AS BEGIN -- Создаем табличную переменную для получения информации по DECLARE @SettingsValues TABLE ( serverName NVARCHAR(128), dbName NVARCHAR(128), tableName NVARCHAR(128), fieldName NVARCHAR(128), daysToLeft INT ); INSERT INTO @SettingsValues (serverName, dbName, tableName, fieldName, daysToLeft) SELECT T.c.value('@dbName', 'NVARCHAR(128)') AS dbName, T.c.value('@serverName', 'NVARCHAR(128)') AS serverName, T.c.value('(TableToClear/@name)[1]', 'NVARCHAR(128)') AS tableName, T.c.value('(TableToClear/CreateDate/@name)[1]', 'NVARCHAR(128)') AS fieldName , T.c.value('(TableToClear/CreateDate/@daysToLeft)[1]', 'INT') AS daysToLeft FROM @xmlConfig.nodes('/Settings/*[local-name()=sql:variable("@settingsName")]') AS T(c); -- предполагаем что для одной настройки мы удаляем данные не более чем из одной таблицы IF (SELECT COUNT(*) FROM @SettingsValues) > 0 BEGIN RAISERROR('ОШИБКА! Попытка обработать более 1 таблицы!', 11, 1); END -- контроль что цуказанный в настройках сервре равен текущему серверу IF EXISTS(SELECT 1 FROM @SettingsValues WHERE serverName <> @@SERVERNAME ) BEGIN RAISERROR('ОШИБКА! Некорректная база данных!', 11, 2); END -- контроль что указанная в настройках база соответствует текеущей IF EXISTS(SELECT 1 FROM @SettingsValues WHERE dbName <> DB_NAME() ) BEGIN RAISERROR('ОШИБКА! Некорректная база данных!', 11, 2); END DECLARE @deleteQuery NVARCHAR(MAX); SET @deleteQuery = ' DECLARE @deletedRowsCount INT = 1; WHILE @deletedRowsCount > 0 BEGIN DELETE TOP(1000) FROM ' + QUOTENAME((SELECT TOP 1 tableName FROM @SettingsValues)) + ' WITH(ROWLOCK) WHERE ' + QUOTENAME((SELECT TOP 1 fieldName FROM @SettingsValues)) + ' < @filterValue; SET @deletedRowsCount = @@ROWCOUNT IF (OBJECT_ID(''tempdb..##StopDeleteByConfig'') IS NOT NULL) BEGIN DROP TABLE ##StopDeleteByConfig; SET @deletedRowsCount = 0 END END'; DECLARE @DaystoLeft INT; SET @DaystoLeft = (SELECT TOP 1 daysToLeft FROM @SettingsValues); DECLARE @dateToFilter DATETIME; SET @dateToFilter = DATEADD(DAY, -@DaystoLeft, GETDATE()); EXEC sp_executesql @deleteQuery, N'@filterValue DATETIME', @dateToFilter; END
Запуск на выполнение
В качестве варианта запуска на выполнение процедур можно рассмотреть два способа, учитывая, что наша цель — периодический запуск удаления данных. Каждый из способов имеет свои достоинства и недостатки, но оба минимизируют и упрощают участие разработчика в поддержке этого решения.
Вариант первый. Можно использовать как для тестирования, так и для последовательного выполнения хранимой процедуры на разных серверах. Это самый простой вариант, так как вся информация по названиям серверов и баз данных, которым необходимо будет подключаться, расположена в нашей XML-конфигурации.
В качестве средства автоматизации запуска можно использовать находящийся всегда под руками Powershell.
executeOnServer.ps1
param( # название конфигурационного файла. [Parameter(Mandatory=0)] [string]$ConfigFile = "Config.xml", #название секции, которую требуется выполнить. [Parameter(Mandatory=0)] [string] $SettingName ) # Загружаем наш файл с конфигурацией # Кодировку выставляем в UTF-8, иначе русские символы будут нечитаемы [xml]$config = Get-Content $ConfigFile -Encoding UTF8 # список с названиями серверов и баз данных $settingsList = @(); # если указан параметр какую настройку выполнить # то из файла конфигурации ются данные только ждя этой настройки # это удобно использовать для тестирования if ($SettingName -ne [string]::Empty) { # тут опять используем функцию XQuery local-name() - но уже в powershewll $databaseNode = $config.Settings.SelectSingleNode("*[local-name() = '$SettingName']") #проверяем что настройка получена if ($null -eq $databaseNode) { Write-Error "Settings for '$SettingName' not found in configuration file." exit 1 } $settingsList += New-Object psobject -Property @{ SettingsName = $SettingName; DatabaseName = $databaseNode.dbName; ServerName = $databaseNode.serverName } ; } else { # получаем список всех настроек $firstLevelTags = $config.Settings.ChildNodes | Where-Object { $_.NodeType -eq "Element" } # для каждой настройки получаем данные foreach ($tag in $firstLevelTags) { $settingsList += New-Object psobject -Property @{ SettingsName = $tag.Name; ServerName = $tag.serverName; DatabaseName = $tag.dbName } ; } } # по полученному списку объектов производим вызов хранимой процедуры на SQL Server foreach ($setting in $settingsList) { # для построения строки соединения с базой данных используется System.Data.SqlClient.SqlConnectionStringBuilder [System.Data.SqlClient.SqlConnectionStringBuilder] $sqlConnectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder; $sqlConnectionStringBuilder["Data Source"] = $setting.ServerName; $sqlConnectionStringBuilder["Initial Catalog"] = $setting.DatabaseName; $sqlConnectionStringBuilder["Integrated Security"] = $true; [string] $sqlConnectionString = $sqlConnectionStringBuilder.ToString(); [System.Data.SqlClient.SqlConnection] $connection = New-Object System.Data.SqlClient.SqlConnection; $connection.ConnectionString = $sqlConnectionString; $connection.Open(); [System.Data.SqlClient.SqlCommand] $command = $connection.CreateCommand(); $command.CommandText = "sp_ExecuteCleanupByConfig"; $command.CommandType = [System.Data.CommandType]::StoredProcedure; # передаем конфиг-файл внутрь хранимой процедуры. $command.Parameters.Add("xmlConfig", [System.Data.SqlDbType]::Xml).Value = $config.OuterXml; $command.Parameters.Add("settingsName", [System.Data.SqlDbType]::NVarChar).Value = ($setting.SettingsName) $command.ExecuteNonQuery(); $connection.Close(); }
Вариант второй. Выполнять полностью на стороне MS SQL, не прибегая к дополнительным программным средствам. В данном случае основной вопрос: где хранить конфигурационный файл в формате XML? Добавлять текст XML в каждый скрипт запуска некорректно, так как это полностью перечеркнёт попытки оптимизировать сопровождение. Выход из данной ситуации: формирование функции, которая возвращается XML с конфигурацией, на этапе развёртывания решения на серверах.
Усложнение решения в данном случае заключается только в том, что необходимо написать дополнительную хранимую процедуру, которая будет получать входным параметром название действия и, получив из функции текст конфигурации, вызывать хранимую процедуру удаления sp_ExecuteCleanupByConfig, передавая ей XML-конфигурации и полученное значение действия. Это выглядит так:
# GenerateFunction.ps1 Powershell script формировании текста функции # Объявляем переменные для имен файлов [string] $configFileName = "config.xml" [string] $functionFileName = "fn_ExecuteCleanupConfig.sql" [string] $functionHeader = @"CREATE OR ALTER FUNCTION dbo.fn_ExecuteCleanupConfig() RETURNS XML AS RETURN CAST('<<xml>>' as XML); " # Читаем содержимое конфигурационного файла $configContent = Get-Content -Path $configFileName -Raw # Заменяем <<xml>> на реальное содержимое XML $finalFunction = $functionHeader -replace '<<xml>>', $configContent # Записываем итоговую функцию в файл, перезаписывая его при повторном запуске Set-Content -Path $functionFileName -Value $finalFunction -Force Write-Host "Функция успешно создана в файле $functionFileName"
#прокси-процедура запуска CREATE OR ALTER PROCEDURE dbo.sp_CallExecuteCleanupByConfig @settingsName NVARCHAR(128) AS BEGIN DECLARE @configXML XML SET @configXML = dbo.fn_ExecuteCleanupConfig(); EXEC dbo.sp_ExecuteCleanupByConfig @xmlConfig = @configXML, @settingsName = @settingsName; END
В этом случае вызов процедуры удаления упрощается до:
EXEC dbo.sp_CallExecuteCleanupByConfig @settingsName = ‘название действия’
После упрощения вызова это можно использовать как отдельные шаги в задании MS SQL Server Agent.
Развёртывание выполнения удаления заданием агента будет рассмотрено ниже в главе «Развёртывание».
Промежуточный итог
После всех предыдущих действий получилось несколько файлов с XML, скриптами TSQL и Powershell:
config.xml — Файл с данными конфигурации.
sp_ExecuteCleanupByConfig.sql — Хранимая процедура для генерации динамического SQL, непосредственно выполнение самих действий по удалению данных.
GenerateFunction.ps1 — Powershell script для формирования функции с текстом xml.
fn_ExecuteCleanupConfig.sql — Формируемая скриптом powershell функция с текстом xml для хранения на странице сервера.
sp_CallExecuteCleanupByConfig.sql — Прокси процедура для вызова хранимой процедуры с передачей xml конфигурации.
Непосредственно для развёртывания будем использовать только три файла:
sp_ExecuteCleanupByConfig.sql
fn_ExecuteCleanupConfig.sql
sp_CallExecuteCleanupByConfig.sql
Важно, что в файлах TSQL не должно быть использовано ключевое слово GO, его можно использовать только в скриптах, выполняемых в SQL Server Management Studio (SSMS), если его не изменили в настройках на другое ключевое слово.
Развёртывание
Современные системы хранения версий могут автоматизровать развёртывание решений. Например, Gitlab предлагает решение CI/CD Pipelines (Continuous Integration/Continuous Delivery or Deployment) — это очень мощный и популярный инструмент. Однако, в текущем решении его использование затруднительно, так как решения будет развёрнуто на нескольких серверах одновременно, как и настройка выполнения.
Разделим развёртывание на два этапа:
Создание или обновление хранимой процедуры и функции на SQL.
Настройка регламентного выполнения удаления данных.
На обоих этапах нужно получить список SQL-серверов и баз данных, на которых требуется развернуть наше решение:
# Код powershell для получения списка function Get-DeployListFromConfig { # список с названиями серверов и баз данных $settingsList = @(); [string]$ConfigFile = "Config.xml"; # получаем список всех настроек [xml]$config = Get-Content $ConfigFile -Encoding UTF8 $firstLevelTags = $config.Settings.ChildNodes | Where-Object { $_.NodeType -eq "Element" } # для каждой настройки получаем данные foreach ($tag in $firstLevelTags) { $settingsList += New-Object psobject -Property @{ ServerName = $tag.serverName; DatabaseName = $tag.dbName } ; } return $settingsList }
После получения списка серверов и баз развёртываем хранимые процедуры и функции на сервере. Это повторно используемый код, также выносим его функцию, в которую будет передаваться информация о сервере и базе данных, в которой необходимо разместить объекты:
# функция для выполнения содержимого файла на сервере function Invoke-DeployFileToServer { param( # название файла, содержимое которого нужно выполнить на сервере, обязательный параметр [Parameter(Mandatory=1)] [string] $fileName, # название севера , обязательный параметр [Parameter(Mandatory=1)] [string] $serverName, # название базы данных, обязательный параметр [Parameter(Mandatory=1)] [string] $databaseName ) # использование [IO.File]::ReadAllText вместо Get-Content обусловлено тем что требуется raw содержимое файла. # Аналогом можно использовать Get-Content с параметром -Raw, # но на практике [IO.File]::ReadAllText показал большее доверие $script = [IO.File]::ReadAllText($fileName); # создание строки соединения [System.Data.SqlClient.SqlConnectionStringBuilder] $sqlConnectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder; $sqlConnectionStringBuilder["Data Source"] = $serverName; $sqlConnectionStringBuilder["Initial Catalog"] = $databaseName; $sqlConnectionStringBuilder["Integrated Security"] = $true; [string] $sqlConnectionString = $sqlConnectionStringBuilder.ToString(); [System.Data.SqlClient.SqlConnection] $connection = New-Object System.Data.SqlClient.SqlConnection; $connection.ConnectionString = $sqlConnectionString; $connection.Open(); [System.Data.SqlClient.SqlCommand] $command = $connection.CreateCommand(); $command.CommandText = $script; $command.CommandType = [System.Data.CommandType]::Text; $command.ExecuteNonQuery(); $connection.Close(); }
Так как для размещения на сервере предназначены всего три файла, для упрощения кода можно использовать вызов с их названиями:
$seversToDeploy = Get-DeployListFromConfig; foreach($server in $seversToDeploy) { Invoke-DeployFileToServer -serverName $server.ServerName -databaseName $server.DatabaseName -fileName "sp_ExecuteCleanupByConfig.sql" Invoke-DeployFileToServer -serverName $server.ServerName -databaseName $server.DatabaseName -fileName "fn_ExecuteCleanupConfig.sql" Invoke-DeployFileToServer -serverName $server.ServerName -databaseName $server.DatabaseName -fileName "sp_CallExecuteCleanupByConfig.sql" }
После выполнения этого скрипта развёртывание объектов можно считать выполненным.
Второй шаг — настройка периодического выполнения удаления. Запускать эту операцию будет задание SQL Server Agent. Для его создания используем скрипты SQL. Для каждой операции применим отдельное задание агента с одним шагом. Имя задания определим как «ClearDB (имя базы) Имя операции».
CreateNewJobs.ps1
# Создание MS SQL Agent Job function Invoke-DeployJobToServer { param( # название файла, содержимое которого нужно выполнить на сервере, обязательный параметр [Parameter(Mandatory=1)] [string] $SettingsName, # название севера , обязательный параметр [Parameter(Mandatory=1)] [string] $serverName, # название базы данных, обязательный параметр [Parameter(Mandatory=1)] [string] $databaseName ) # ранее согласованное имя задания [string] $jobName = "ClearDB ($databaseName) $SettingsName"; # создание соединения [System.Data.SqlClient.SqlConnectionStringBuilder] $sqlConnectionStringBuilder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder; $sqlConnectionStringBuilder["Data Source"] = $setting.ServerName; $sqlConnectionStringBuilder["Initial Catalog"] = $setting.DatabaseName; $sqlConnectionStringBuilder["Integrated Security"] = $true; [string] $sqlConnectionString = $sqlConnectionStringBuilder.ToString(); [System.Data.SqlClient.SqlConnection] $connection = New-Object System.Data.SqlClient.SqlConnection; $connection.ConnectionString = $sqlConnectionString; $connection.Open(); [System.Data.SqlClient.SqlCommand] $command = $connection.CreateCommand(); $command.CommandText = "SET @JobId = COALESCE( (SELECT TOP 1 job_id FROM msdb.dbo.sysjobs job WITH(NOLOCK) WHERE job.name = @jobName) , CAST(0x00 AS uniqueidentifier) );"; $command.CommandType = [System.Data.CommandType]::Text; # создаем output параметр для получения GUID задания # если задания не существует, то будет пустой GUID [System.Data.SqlClient.SqlParameter] $JobId = $command.CreateParameter(); $JobId.ParameterName = "JobId"; $JobId.SqlDbType = [System.Data.SqlDbType]::UniqueIdentifier; $JobId.Direction = [System.Data.ParameterDirection]::Output; $command.Parameters.Add($JobId) | Out-Null; $command.Parameters.Add("JobName", [System.Data.SqlDbType]::NVarChar).Value = $jobName; $command.ExecuteNonQuery(); # приводим полученный GUID к сравниваемому виду [Sytem.GUID] $JobGuid = [System.Guid]::NewGuid($JobId.Value) # проверка что задание уже создано if ($JobGuid -eq [System.Guid]::Empty) { #задания ещё не существует #созадем задание агента одним скриптом #для этого используем отдельный экземпляр [System.Data.SqlClient.SqlCommand] #планируем на запуск в 20:00 попятницам. [System.Data.SqlClient.SqlCommand] $createJobCommand = $connection.CreateCommand(); $createJobCommand.CommandText = "USE [msdb] DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'$jobName', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_page=2, @delete_level=0, @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT EXEC msdb.dbo.sp_add_jobserver @job_name=N'$jobName', @server_name = N'$($setting.ServerName)' EXEC msdb.dbo.sp_add_jobstep @job_name=N'$jobName', @step_name=N'run cleanup $SettingsName', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' exec dbo.sp_CallExecuteCleanupByConfig @settingsName =''$SettingsName'' ', @database_name=N'$($setting.DatabaseName)', @flags=0 EXEC msdb.dbo.sp_update_job @job_name=N'$jobName', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @notify_email_operator_name=N'', @notify_page_operator_name=N'' DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_name=N'$jobName', @name=N'Cleanup schedule ($SettingsName))', @enabled=1, @freq_type=8, @freq_interval=32, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20251216, @active_end_date=99991231, @active_start_time=200000, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT "; $createJobCommand.CommandType = [System.Data.CommandType]::Text; $createJobCommand.ExecuteNonQuery(); } else { return [string] "EXISTS"; } $connection.Close(); }
Используя полученный из Get-DeployListFromConfig список, создадим задание:
$seversToDeploy = Get-DeployListFromConfig; foreach($server in $seversToDeploy) { Invoke-DeployJobToServer -serverName $server.ServerName -databaseName $server.DatabaseName -SettingsName "sp_ExecuteCleanupByConfig.sql" Invoke-DeployJobToServer -serverName $server.ServerName -databaseName $server.DatabaseName -SettingsName "fn_ExecuteCleanupConfig.sql" Invoke-DeployJobToServer -serverName $server.ServerName -databaseName $server.DatabaseName -SettingsName "sp_CallExecuteCleanupByConfig.sql" }
После завершения проверяем, что на серверах появились задания.
Развёртывание завершено!
Выводы
Мы рассмотрели один из вариантов организации удаления данных из разнородных таблиц по одному полю типа «дата». Предлагаемое решение имеет большой запас для улучшения и расширения. Например:
Можно реализовать запись в таблицу журнала. В конфигурационном файле для описания таблицы журнала даже предусмотрен тег.
Скрипты на Powershell можно дополнить консольным выводом дополнительной информации о ходе выполнения. Также их можно дополнить журналированием действий при помощи, например, Start-Transcript и Stop-Transcript.
Управление созданием заданий для выполнения можно сделать с использованием SMO (SqlManagementObjects). Не смотря на то, что производительность этой библиотеки далека от идеала, её использование оправдано унификацией.
Дополнить конфигурационный файл информацией о периодах запуска задания.
Реализовать размещение операций в рамках одного сервера в одном задании агента, где каждый шаг соответствовал бы отдельной операции.
Это всего лишь небольшая часть того, что можно ещё придумать и реализовать в рамках этой задачи, но идеал, как известно, недостижим. Улучшать и дописывать можно бесконечно.