Каталог решений - Надежная регистрация изменений. Версионирование объектов. Аудит. Все средствами SQL Server

Следи за рукой

Знание — сила! А знание, подкрепленное фактами, достоверными данными и высокой актуальностью — это сила, ответственность и безграничные возможности в части анализа информации.

Не такая уж и редкая задача — получить историю изменения данных, узнать кто, что и когда изменил в том или ином объекте. Пореже нужно отправлять изменения в другие информационные системы или даже хранилища данных, для чего создаются различные способы регистрации изменений. Но хоть эти задачи и звучат просто, но чем больше система и чем сложнее архитектура потоков данных, тем больше проблем с этим возникает:

• Проблемы производительности при регистрации изменений в высоконагруженных системах
• Критичность ошибок регистрации данных, если часть изменений не будет зарегистрирована или потеряна
• Высокая детализация изменений приводит к значительному росту базы данных и таблиц регистрации, в частности, а также существенно может замедлять процессы в информационной системе
• Отправка изменений системам-подписчикам требует большой надежности и скорости работы
• И много других вопросов, с которыми придется столкнуться.

И это нормально! Не нормально — это пытаться использовать один "универсальный" инструмент для решения всех этих задач, проблем.

Так давайте пойдем дальше и посмотрим на один из путей решения. Один из множества. А именно на механизм Change Data Capture из SQL Server. И путь его использования с платформой 1С.

Обычный ход

Прежде чем начать нужно сказать пару слов о том как это обычно делается в мире 1С. Примерно это выглядит так:

• Планы обмена — объекты конфигурации 1С, задача которых предоставить инфраструктуру для регистрации изменений, формирования и обработки сообщений и возможность использования распределенной информационной базы (что не обязательно). При этом сам механизм имеет как плюсы, так и минусы. Подробно на этом останавливаться не будем, но в высоконагруженных системах использовать планы обмена дело последнее из-за узких мест в его работе. Подробнее смотрите статьи здесь, на Инфостарт.
• Собственная таблица регистрации — обычно реализуется на регистрах сведений, но бывают и более извращенные случаи. Фактически создается отдельная таблица, где разработчик может более гибким образом управлять статусами объекта. В основном так решаются задачи регистрации изменений только для обмена со сторонними системами. Истории изменений самих данных как таковых нет.
• Версионирование объектов — штатный механизм из БСП и его вариации в виде регистра сведений, в котором сохраняется сериализованная версия объекта на момент изменения. Вот его краткое описание на примере УТ 11. Относительно популярный подход хранения изменений объекта с детализацией до конкретных полей, но влияющий как на скорость записи в базу (обычно незначительно, относительно основного времени записи), так и на размер самой базы (а этот фактор уже очень сильный, иногда половину размера базы может занять история версий объектов, особенно если их не чистить).
  • Сам подход хранения версий был настолько популярен, что в итоге подобный же механизм включили на уровне самой платформы 1С в виде "Истории данных", который проще в использовании, но имеет меньше контроля над своей работой. И больше ошибок, хотя с ними сталкивался достаточно давно и в новых версиях может все работает намного лучше.
  • Механизм решает задачу хранения истории изменений, но работает относительно медленно при чтении данных версий, т.к. каждую из них нужно предварительно распаковать или нормализовать.
• Журнал регистрации — еще один штатных механизм для логирования изменений, но на верхнем уровне. Больше подходит для задач аудита и то не всегда. Содержит информацию кто, что и когда изменил, а также большое количество других событий для анализа. В части отслеживания изменений может помочь увидеть только сам факт изменения и его инициатора, но на вопросы кто изменил и что точный ответ дать не сможет. Хотя есть умельцы, которые версии объектов в виде текста сохраняют в журнал регистрации. Без комментариев. В целом механизм медленный и не поддающийся нормальной интеграции с другими инструментами и системами, только через различные костыли.

Это основное, что касается задач регистрации изменений и хранения истории версий объектов. Есть и другие пути, которые в мире 1С часто используют, немного выходя за границы платформы. Но об этом смысла рассказывать нет. Перейдем к теме статьи.

Берем под контроль

Отбросим штатные средства и попытаемся использовать механизм регистрации изменений Change Data Capture из SQL Server. Главная причина, почему будем использовать именно его — доступность (SQL Server до сих пор самая популярная СУБД для систем на базе 1С), высокая производительность и детализация истории изменений. Хотя и для PostgreSQL подобные возможности есть, но мы их рассматривать не будем. Поехали!

Общая архитектура

В чем суть механизма CDC? Вы включаете для базы данных поддержку использования CDC, затем включаете отслеживание изменений для конкретных таблиц базы данных. При внесении изменений в таблицы, информация об этом появляется в журнале транзакций, вне зависимости от модели восстановления базы данных (простой или полной) и вот именно эти данные из журнала транзакций и служат источником информации о вносимых изменениях.

Фоновый процесс сканирует записи в журнале транзакций и загружает их в таблицы регистрации изменений, которые механизм CDC создает автоматически на каждую отслеживаемую таблицу. Таким образом, регистрация изменений происходит асинхронно и не влияет явно на операции в базе данных.

При включении поддержки CDC для всей базы сначала создаются служебные таблицы в схеме "cdc", информацию о которых Вы можете узнать в официальной документации (в SSMS их можно найти в списке системных таблиц):

• [cdc].[change_tables]
• [cdc].[ddl_history]
• [cdc].[lsn_time_mapping]
• [cdc].[captured_columns]
• [cdc].[index_columns]

Как только Вы включаете регистрацию изменений для конкретной таблицы, например, для таблицы [dbo].[v8users], то сразу добавляется таблица для сохранения изменений [cdc].[dbo_v8users_CT] со структурой, аналогичной исходной таблице, но при этом с дополнительными служебными полями:

• [__$start_lsn] — Регистрационный номер транзакции в журнале (LSN), связанный с фиксацией транзакции изменения.
• [__$end_lsn] — Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется.
• [__$seqval] — Значение последовательности, используемое для упорядочивания изменений строк в пределах транзакции.
• [__$operation] — Определяет операцию языка обработки данных (DML). 1 — удаление, 2 — вставка, 3 — обновление (старые значения), 4 — обновление (новые значения).
• [__$command_id] — Отслеживает порядок операций в транзакции.

Таким образом, механизм CDC позволяет собрать историю изменения данных в таблицах на физическом уровне, что предоставляет максимальную детализацию истории изменений. Но это также создает некоторые особенности в части обработки этих данных, т.к. некоторые операции могут формировать несколько операций в истории изменения данных. Например, операция обновления может быть представлена операцией удаления и операцией добавления, но с одним идентификатором транзакции. Это нужно учитывать.

Кроме служебных таблиц в базе появляются также объекты:

• Множество служебных хранимых процедур в схеме "cdc".
• И функции для удобного получения истории изменений по таблицам.
• Задания для загрузки изменений из журнала транзакций и очистки исторических данных.
• И некоторые другие объекты.

И все было бы хорошо, но просто так CDC подружить с 1С не получится, т.к. появятся следующие проблемы:

• При проведении реструктуризации базы данных механизм CDC для обновляемой таблицы будет отключен и нужно будет его вручную включить обратно.
• В момент отключения механизма CDC из-за реструктуризации может появиться потеря информации об изменениях.
• И, конечно, будет проблема в части использования информации об изменении данных из CDC, ведь официальной поддержки у платформы 1С для этого механизма нет.

Но не стоит отчаиваться, так как все эти проблемы можно решить. Этим мы и займемся дальше. В одной статье полноценно все рассказать нельзя, но направление работ определить точно можно.

Делаем настройку

Данный подход по настройке CDC подходит для любых приложений, которые используют SQL Server в качестве СУБД. Подробные шаги по настройке описаны здесь. Мы будем адаптировать подход к информационной базе 1С.

Подготовка

На первом шаге создадим в базе новую схему данных "yy", чтобы все новые объекты хранить там и не смешивать их с объектами платформы 1С.