gifts2017

Использование SQL Server Change Tracking для регистрации изменений данных объектов 1С:Предприятие 8

Опубликовал Дмитрий Жичкин (zhichkin) в раздел Обмен - Перенос данных из 1C8 в 1C8

SQL Server Change Tracking - способ регистрации изменений данных объектов 1С:Предприятие 8 средствами SQL Server версии 2008 и выше. Использование этой технологии снижает нагрузку на систему и увеличивает параллельность работы пользователей. Change Tracking разрабатывалась специально для повышения производительности и удобства использования в сценариях интеграции и синхронизации данных между приложениями.

Любой обмен данными начинается с регистрации изменений. SQL Server предоставляет для этих целей следующие возможности:

1. DML триггеры (любые версии SQL Server). Имеется возможность асинхронной регистрации изменений, если дополнительно использовать SQL Server Service Broker.

2. Change tracking (SQL Server 2008 и выше). Синхронная регистрация изменений.

3. Change Data Capture (SQL Server 2008 и выше). Изменения читаются из логов транзакций.

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

В этой небольшой публикации я рассмотрю особенности использования change tracking на платформе 1С:Предприятие 8. Подробная информация о том, что такое change tracking можно прочитать здесь: https://blogs.msdn.microsoft.com/alexejs/2009/08/09/change-tracking-2/. Официальную документацию Microsoft по этому вопросу можно почитать здесь: https://technet.microsoft.com/ru-ru/library/cc280462(v=sql.105).aspx.

Что это такое.

Change tracking работает похожим на план обмена образом: SQL Server создаёт для каждой отслеживаемой таблицы ещё одну, служебную, для записи в неё изменений. Эта таблица, кроме ключа изменённой записи, включает в себя:

- версию изменения (одно изменение - одна запись);

- вид изменения (insert, update, delete);

- битовую маску изменённых полей (так можно отслеживать какие поля были изменены);

- контекст изменения (дополнительная информация об изменении, обычно используется для разрешения коллизий).

Следует отметить, что change tracking не фиксирует изменения в разрезе узлов обмена, не содержит логики нумерации сообщений и квитирования. Эта технология имеет только одну логику и выполняет только одну функцию - регистрацию изменений. Что с ними делать решает код прикладного приложения. Нет ничего лишнего. При этом выполнение регистрации изменений оптимизировано на уровне ядра SQL Server, чтобы выполняться максимально быстро и оказывать минимально возможную нагрузку на систему. Мне как-то попалось на глаза мнение, что change tracking работает до выполнения триггеров и выполняет регистрацию изменений в 4 раза быстрее, чем если бы делать это при помощи триггеров. Честно говоря, я эту информацию не проверял.

Как включить.

Функция change tracking включается на уровне базы данных. После этого необходимо включить отслеживание изменений для тех таблиц в этой базе данных, для которых это нужно. При этом следует иметь ввиду, что такая таблица обязательно должна иметь ограничение primary key. Таким образом в контексте 1С:Предприятие 8 это означает, что мы можем использовать change tracking для таблиц всех ссылочных объектов платформы без особых проблем, но для, например, регистров сведений это сделать сразу не получится. Для этого придётся сначала удалить уникальный кластерный индекс регистра, а затем создать точно такой же индекс, но уже как первичный ключ (primary key). В контексте SQL Server будет правильнее сказать: "наложить на таблицу ограничение первичного ключа". При этом нужно помнить, что SQL Server не допускает для полей, входящих в состав первичного ключа, NULL значения. Платформа 1С:Предприятие 8 переносит подобное изменение совершенно спокойно. Однако, нужно иметь ввиду, что на больших таблицах эта операция может выполняться продолжительное время, что сопоставимо с перестроением индекса.

Реструктуризация 1С.

Существенным нюансом при использовании change tracking является внесение изменений в структуру объектов конфигурации 1С, которые влекут за собой реструктуризацию. Дело в том, что при выполнении реструктуризации данных 1С делает это следующим образом (в сокращённом варианте):

1. Переименовывает основную рабочую таблицу (используется системная хранимая процедура sp_rename).

2. Создаёт новую таблицу с теми изменениями, которые сделаны в конфигураторе.

3. Копирует данные из старой рабочей таблицы в новую.

4. Удаляет бывшую рабочую таблицу !

5. Переименовывает новую таблицу (она становится рабочей).

При этом важно то, что DDL оператор DROP TABLE полностью удаляет все зарегистрированные на данный момент времени для этой таблицы изменения и выключает change tracking. Тоже самое касается пользовательских триггеров и индексов, если они были вами добавлены ранее. По большому счёту это не так страшно, но потеря регистрации изменений может быть неприятной.

Для защиты от непредвиденной потери регистрации изменений можно использовать DDL триггер RENAME (устанавливается на уровне базы данных). Эта возможность, так же как и change tracking, появилась в SQL Server 2008.

Вот и всё =)

По большому счёту на этом нюансы использования "родной" регистрации изменений SQL Server change tracking заканчиваются. Далее остаётся только периодически читать изменения и отправлять их в узел или узлы обмена данными, а может быть обрабатывать каким-то иным способом согласно логике вашего приложения. Каждое изменение можно читать по отдельности и формировать сообщения обмена так, как вам нужно. Если изменений по одному ключу несколько, то change tracking умеет выдавать одно результирующее изменение для таких случаев.

Если эта тема вызовет достаточный интерес, то я готов раскрыть её более подробно с примерами кода.

Практический пример.

В качестве демонстрации практической возможности использования этой технологии совместно с платформой 1С:Предприятие 8 можно использовать следующее готовое решение: http://infostart.ru/public/557097/

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Алексей Бочков (Aleksey.Bochkov) 27.10.16 07:16
(0) Change tracking требует обязательного наличия primary key на таблице, что делает использование в 1С, мягко говоря, неудобным.
Посмотрите как работает Change Data Capture - там такого ограничения нет и можно построить более стабильное решение.
https://msdn.microsoft.com/en-us/library/cc645937.aspx
2. Дмитрий Жичкин (zhichkin) 27.10.16 10:56
(1) Aleksey.Bochkov,
Как работает CDC я знаю =) На просторах Интернет можно найти много отличных статей, в которых приводится качественное сравнение обеих технологий и даются рекомендации в каких сценариях какую технологию лучше использовать. Если быть кратким, то наиболее существенными различиями являются следующие:
1. Change Data Capture доступна только в версии SQL Server Enterprise edition. Change Tracking доступна в любой версии SQL Server, даже в бесплатной Express edition.
2. CDC регистрирует изменения асинхронно, а CT - синхронно.
3. Использование CDC может приводить к разрастанию логов транзакций. CDC блокирует логи транзакций на время их обработки, что может создавать неудобства администрирования.

Кроме этого, про неудобства использования change tracking с 1С я написал. Более того, не все проекты интеграции требуют регистрации изменений для табличных типов данных как, например, регистры сведений или регистры накопления. А все ссылочные типы данных 1С как справочники или документы имеют primary key. Этого может быть вполне достаточно, так как остальные данные можно "вытягивать" через ведущие измерения, регистраторы и т.п. методами.

Я прошу прощения, но утверждение о более "стабильном" решении, которое использует CDC, мне показалось несколько легковесным. Каждой задаче - свой инструмент. Всё познаётся в контексте =)
3. Михаил Максимов (МихаилМ) 27.10.16 13:54
за статью спасибо . но как при реструктуризации поможет DDL триггер rename. что вне должно произойти ?
4. Дмитрий Жичкин (zhichkin) 27.10.16 15:45
(3) МихаилМ,
Для того, чтобы ответить на Ваш вопрос, я сначала должен описать то, как 1С:Предприятие 8 выполняет реструктуризацию. Для этого я вносил различные виды изменений (их не так много) в объекты конфигурации. Например, добавлял/удалял реквизит, менял его имя и тип данных. Затем смотрел при помощи SQL Profiler что происходит, когда сохраняешь конфигурацию и выполняешь реструктуризацию. Если убрать всё лишнее, то получается следующая последовательность операций:
1. Создаётся новая таблица с суффиксом NG, которая имеет конечную структуру данных. Для неё создаются все необходимые индексы. Здесь можно при помощи DDL триггера "отловить" событие CREATE_TABLE.
2. Данные из рабочей таблицы копируются в новую таблицу.
3. Рабочая таблица переименовывается в таблицу с суффиксом OG. Здесь используется sp_rename.
4. Новая таблица с суффиксом NG и уже загруженными в неё данными переименовывается в рабочее название без суффиксов. Снова используется sp_rename.
5. Выполняется удаление старой рабочей таблицы, которая в данный момент имеет суффикс OG. Происходит DDL событие DROP_TABLE. Вместе с этим удаляются все пользовательские модификации бывшей рабочей таблицы: триггеры, индексы, регистрации изменений change tracking или change data capture.

Вариантов защиты от нежелательных последствий пункта 5 несколько. Самый популярный (об этом есть публикации на инфостарте) следующий: перед выполнением сохранения конфигурации и реструктуризации рабочая таблица "прячется" при помощи переименования, а 1С "подсовывается" такая же, только пустая таблица. Затем делают всё, что нужно 1С. Потом меняют ранее спрятанную таблицу средствами SQL Server и возвращают на место. Всё это делается вручную, как правило, для ускорения операции реструктуризации на больших таблицах.

При помощи DDL триггеров можно поставить защиту или напоминание администратору 1С о том, что до выполнения реструктуризации необходимо выполнить какие-либо манипуляции, например, выгрузить все изменения зарегистрированные при помощи change tracking. А можно сделать это прямо в самом триггере, вызвав соответствующую хранимую процедуру, например. Ну, тут фантазия программиста вообще не ограничена =) Я указал на нюанс и возможность ...
5. aspirator 23 (aspirator23) 28.10.16 13:24
(4) zhichkin, ...Если эта тема вызовет достаточный интерес, то я готов раскрыть её более подробно с примерами кода....

Было бы очень интересно. Типовой обмен существует и вроде работает, и вроде больные места его знают, и вроде борются с ним, и вроде как-то получается.
Но радикального-то ускорения нет. В небольших сетях можно пережить, а на больших - это действительно проблема.
Поэтому альтернативные варианты хотелось бы посмотреть. С кодом хотя бы ключевых моментов.
6. Дмитрий Жичкин (zhichkin) 29.10.16 00:49
(5) aspirator23,
По всей видимости нужно писать отдельную статью. Уточните, пожалуйста, что Вы имеете ввиду под "типовые обмены"? Я типовыми обменами не пользовался, я всегда их переписывал =) Насколько я понимаю, речь идёт о РИБ и КД.

От КД мы отказались ещё в 2006 когда файл обмена весом около 1 Гб заходил в базу около 8 часов. На самом деле больше, так как у нас лопнуло терпение и мы прекратили этот процесс. Затем был РИБ. Это было быстрее, но при очередном разрушении базы дочернего узла при обновлении (это была 8.0), мы решили больше не экспериментировать.

Тесты показали, что самый быстрый вариант загрузки именно файла обмена средствами 1С это РИБ. Это не удивительно, так как XML там максимально лёгкий для 1С и загрузка идёт сразу из файла в БД. Нет конвертаций, поиска по кодам и прочей логики. Проблема РИБ - это, прежде всего, архитектура планов обмена. Одна строка изменений потенциально имеет 3-х конкурентов: писатель изменения, читатель изменения для выгрузки, писатель квитанции, подтверждающей получение изменения в другой базе. На практике первых двух хватает выше крыши.

Таким образом, для highload обменов нужно решить две основные проблемы:
1. Читатели изменений не должны блокировать писателей (и наоборот).
2. Максимально быстро выполнять запись изменений и их чтение.
3. Выполнять параллельные операции при помощи фоновых заданий.

Если решать проблемы только средствами 1С, то регистрацию делать нужно свою. Есть два хороших варианта:
1. Регистр сведений с отметкой времени (версии) изменения - для каждого объекта будет несколько записей. Тут нужно научиться получать результирующее изменение, чтобы не грузить всё по очереди.
2. Во все отслеживаемые объекты добавить реквизит "ДатаПоследнейМодификации" и указывать там текущую дату каждый раз при записи. Впоследствии выгружать только те объекты, которые имеют дату модификации больше, чем та, которая была в момент последней выгрузки.
Оба варианта разводят писателей и читателей по углам. Никто никого не блокирует.

Если говорить о скорости загрузки файлов обмена, то нужно однозначно использовать формат XML, используемый РИБ. Он пишется и читается достаточно быстро. Для не одинаковых конфигураций придётся писать свой КД. Кстати, это не так сложно, как кажется - ведь нужно просто сопоставить свойства и всё. Без всяких обработчиков событий и прочее. Эту логику можно вынести в процедуры наподобие отложенного проведения и т.п.

По третьему варианту добавить нечего. Хотя есть нюансы в виде PAGE_LOCK. По умолчанию SQL Server блокирует записи постранично (одна страница = 8 Кб). Если записи разных потоков находятся на разных страницах, то это может приводить даже к deadlock'ам. В таких случаях можно пробовать использовать хинт WITH(ROWLOCK), но только средствами 1С это невозможно.

Если говорить об оптимизации любыми другими средствами, кроме 1С, то мы, например, делали обмен средствами SQL Server. Был такой набор:
- свой формат XML сообщений: компактный и удобный для работы на уровне SQL Server (с поддержкой группировки объектов по транзакциям);
- своя регистрация изменений в специальный регистр сведений (сейчас я бы использовал change tracking);
- экспорт изменений в очереди сообщений SQL Server Service Broker;
- использование SQL Server Service Broker в качестве транспорта;
- полное шифрование сообщений сертификатами SSL;
- использование вспомогательной логики для маршрутизации сообщений по узлам;
- загрузка входящих сообщений Service Broker в целевую БД при помощи CLR хранимой процедуры, написанной на C# (родные средства SQL Server для работы с XML ооочень медленные).
В итоге сообщение обмена в формате XML весом в 1 Гб "залетало" в целевую БД за несколько минут. При этом на тот момент времени 1 - 1,5 Гб это был объём сообщений за сутки. Мы имели огромный запас прочности. Система работала настолько стабильно, что мы периодически забывали про неё и вспоминали только если где-то падала сеть или что-то в этом роде. Чинилось это элементарно - просто восстанавливалась инфраструктура и SQL Server дальше продолжал свою работу. Это было ещё на 1С:Предприятие 8.0 лет 10 назад. SQL Server был ещё 2005 версии.
Для справки: трое программистов разработали всё это великолепие примерно за 1 месяц.

Сейчас я работаю в крупной компании, где центральная база больше 1 Тб, больше 100 узлов обмена и он тоже выполняется средствами SQL Server. И, кстати, сказать я бы не сказал, что там можно расслабиться. Сейчас активно идёт работа по проекту свёртки, чтобы, в том числе, дать запас прочности обменам.

Где бы я не работал, везде возникал вопрос обменов средствами SQL Server и именно таким образом решалась проблема. Не думаю, что мне "просто повезло" ...
7. aspirator 23 (aspirator23) 30.10.16 15:26
(6) zhichkin, Да, конечно речь идет о Риб. Отличный комментарий. Здорово его слегка причесать, развернуть сложные моменты, добавить несколько прикладных примеров и это тянет на хорошую статью. Учитывая, что по данной теме в таком виде очень мало информации, она получилась бы очень свежая и интересная.
8. Дмитрий Жичкин (zhichkin) 30.10.16 22:00
(7) aspirator23,
Спасибо за Ваш интерес! Теперь я уверен, что такая статья нужна. До Нового года постараюсь написать =) Скорее всего это будет несколько публикаций: тема большая. Впрочем: "Дорогу осилит идущий".

Буду признателен, если Вы сможете описать требования к РИБ, которые сложились из Вашей практики. Например, количество документов в день, их объём в Мб, требования по скорости обмена выраженной в объёмах данных в единицу времени.

Вообще было бы интересно провести честное исследование на тему "Предел производительности РИБ средствами 1С:Предприятие 8 и возможности преодоления этого предела альтернативными способами" или что-то в этом роде =) Для этого нужна методика оценки производительности РИБ =) Постараюсь что-то предложить в своей будущей статье.

На вскидку: нужно взять пару каких-нибудь "эталонных" документов и погонять их туда-сюда ... Нужно подумать как эмулировать блокировки при чтении изменений на тестовом стенде ... В общем есть над чем подумать =)

Буду очень признателен за конструктивные предложения!
9. Артур Аюханов (artbear) 02.11.16 19:55
10. Роман Романов (romansun) 03.11.16 17:29
(5) (6) всё ж для хайлоад обменов и интеграций есть же специальные решения, специальное ПО. Целые конторы этим занимаются. В том числе и для 1С-баз.

шины, месседж-брокеры - вот это вот всё

нет денег на IBM, знаю, ставят какие-то opensource решения
11. Дмитрий Жичкин (zhichkin) 03.11.16 18:40
(10) romansun,
Чтобы воспользоваться "вот этим всем" нужно как минимум сгенерировать сообщение.
Чтобы сгенерировать сообщение необходимо зарегистрировать изменение состояния системы.
Change tracking является одним из многих способов регистрации изменений. Статья об этом.
12. Гаджи Гаджиев (gadjik) 08.11.16 16:15
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа