Отслеживание изменений в корпоративной базе данных SQL Server - Как работает сбор данных изменений

Как работает сбор данных изменений

Сбор данных изменений в процессе транзакций не делает ничего, что изменяло бы отслеживаемую таблицу. Вместо этого операции вставки, записи и удаления, записываются в журнал транзакций как обычно и периодически собираются из журнала. Сбор выполняется задачей чтения журнала агента SQL, и собранные операции сохраняются в отдельной таблице, именуемой таблицей изменений. В какой-либо последующий момент таблицу изменений можно запросить, чтобы получить данные изменений, используя одну из двух функций. Комбинация таблицы изменений и двух функций называется экземпляром сбора. Рис. 3 показывает поток данных, использующий сбор данных изменений для управления процессом ETL хранилища данных.

Включения сбора данных изменений – процесс из двух частей. Сперва член фиксированной серверной роли администратора системы должен включить сбор данных изменений для базы данных, используя sys.sp_cdc_enable_db. Затем, член фиксированной серверной роли db_owner («владельца базы данных») должен включить сбор данных изменений для конкретной таблицы, используя sys.sp_cdc_enable_table. Эти требования к безопасности вызваны потенциалом повышенного использования места на диске при неверной настройке сбора данных изменений. Понятно, почему владелец таблицы не может сам включить эту функцию и преподнести администратору базы данных сюрприз в виде повышенного использования диска.

Когда для базы данных включается сбор данных изменений, к базе данных кое-что добавляется, включая схему (именуемую cdc), некоторые таблицы метаданных и триггер для записи событий языка определения данных (Data Definition Language – DDL). (Одна из возможностей, которая мне кажется замечательной – заброс списка изменения DDL в таблицу.)

Включение сбора данных изменений также создает экземпляр сбора для таблицы – таблицу изменений и до двух функций, для возвращения данных изменений. Имя таблицы изменений идентично имени экземпляра сбора, с добавленным _CT. Первая функция создается всегда и используется для возвращения данных изменений из таблицы изменений. Вторая функция создается, если указано допускать итоговые изменения. Это значит, что возвращается лишь финальный результат всех собранных изменений, без всех промежуточных изменений, возвращаемых первой функцией. Имена двух функций это, соответственно, fn_cdc_get_all_changes_ и fn_cdc_get_net_changes_, с добавленным именем экземпляра сбора. Отметьте, что (подобно функции отслеживания изменений) эта функция требует, чтобы у таблицы имелся основной ключ или иной уникальный индекс.

При работе с первой таблицей в базе данных, чтобы включить сбор данных изменений, можно создать две задачи агента SQL: задачу сбора и задачу очистки. Я говорю «можно», поскольку задача сбора идентична используемой для сбора транзакций в транзакционной репликации. Если транзакционная репликация уже настроена, то будет создана только задача очистки и существующая задача чтения журнала будет также использована как задача сбора. Это хорошо тем, что наличие двух задач чтения журнала очень быстро приведет к проблемам конфликтов с журналом и, следовательно, проблемам производительности. Так или иначе, агент SQL должен работать для использования сбора данных изменений.

Логика внутри средства чтения журнала автоматически справляется с включением и выключением таблиц для сбора данных изменений и изменяет собранное из журнала транзакций соответственно. Один важный момент, который следует заметить здесь, состоит в том, что после включения сбора данных журнал транзакций ведет себя точно так же, как в случае репликации транзакций – журнал нельзя обрезать, пока средство чтения журнала не обработало его. Это значит, что операция контрольной точки, даже в режиме восстановления SIMPLE, не обрежет журнал, если он уже не был обработан средством чтения журнала.

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

По умолчанию задача сбора работает непрерывно, проверяя журнал каждые пять секунд и обрабатывая максимум 500 транзакций из журнала. По умолчанию работа очистки выполняется каждый день в два часа ночи и удаляет все записи изменения данных, которым больше трех дней из таблиц изменений. Эти параметры можно изменить, используя процедуру sys.sp_cdc_change_job после чего изменения не вступят в силу, пока не перезапустить задачи, используя sys.sp_cdc_stop_job and sys.sp_cdc_start_job.

Несмотря на то, что процесс чтения журнала обычно не будет иметь большого эффекта на производительность системы, на сильно загруженных системах OLTP, с большими объемами изменяющихся данных возможно возникновение конфликтов на журнале транзакций из-за добавления всего лишь одного процесса чтения журнала. Сам конфликт будет вызван необходимостью для головок жесткого диска перемещаться назад и вперед между точкой, в которую транзакции записывают журнал, и точкой, в котором он считывается процессом средства чтения журнала. В этом случае может быть необходимо изменить частоту работы задачи сбора, чтобы производительность OLTP не страдала. Это, однако, создает классический конфликт между пространством на диске и производительностью – журнал продолжит расти, пока задача сбора не обработает его.

Та же проблема возникает при изменении частоты работы задачи очистки или изменении периодов хранения данных – таблицы изменений продолжат расти, пока данные изменений не будут очищены. Это ведет к общим архитектурным размышлениям о том, что нужно отслеживать и сколько хранить. Важные вопросы, которые стоит учесть, включают в себя следующие.

• Список требуемых столбцов для экземпляра сбора. По мере сбора большего числа столбцов, больше данных изменений вставляется в таблицы изменений.
• Объем пространства на диске, используемого таблицами изменений.
• Частота, с которой работает процесс, потребляющий данные изменений. Имейте в виду, что данные не могут быть удалены, если они еще не были использованы.
• Частота, с которой работает процесс очистки, – данных изменений может быть так много, что процесс, удаляющий их, может, например, работать только по выходным, потому что иначе он создает слишком большой журнал транзакций.

Сбор данных изменений может быть установлен на простое отслеживание изменений в таблице или отслеживание части столбцов в таблице. Использование только части может быть полезно, если часть несущественных столбцов – очень широкие столбцы varchar, или столбцы больших двоичных объектов (BLOB), таких как текст, изображение или XML); иначе пространство, используемое таблицей изменений может очень быстро стать неудобно большим.

Учитывая повышенный потенциал использования пространства на диске, положение таблицы изменений в файловой группе можно установить при включении сбора данных изменений. Это упрощает управление пространством лежащего в основе диска и значит, что все данные об изменениях потенциально могут быть сохранены в томе с менее затратным уровнем RAID, чем основная база данных. Кроме того, хотя параметры задачи очистки применяются ко всем экземплярам сбора, отдельный экземпляр сбора можно очистить отдельно и в любое время, если пространство на диске становится проблемой. Использование пространства диска можно легко отслеживать, используя sp_spaceused на таблицах сбора.

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

Изменения DDL неограниченны, когда включен сбор данных изменения. Однако они могут возыметь некоторый эффект на собранных данных изменений в случае добавления или сброса столбцов. Если отслеживаемый столбец сброшен, во всех дальнейших записях в экземпляре сбора для этого экземпляра будет стоять NULL. Если столбец добавлен, он будет проигнорирован экземпляром сбора. Другими словами, форма экземпляра сбора определяется в момент его создания.

Если требуются изменения столбцов, то возможно создать другой экземпляр сбора для таблицы (до максимума в два экземпляра сбора на таблицу) и позволить потребителям данных изменений перейти на новую схему таблицы. Но при этом следует соблюдать осторожность, поскольку два экземпляра сбора для отслеживаемой таблицы означают удвоенные ввод/вывод, запись в журнал и потребление пространства диска.

Не углубляясь слишком далеко, данные извлекаются из таблиц изменений с помощью описанных мною функций. Функции берут начальный номер последовательности журнала и конечный номер последовательности журнала, предоставлены также и другие функции, позволяющие преобразовать обычное время в номер последовательности журнала . При извлечении обновлений можно даже указать, нужно ли увидеть предыдущее и последующее значения или только предыдущее. Демонстрационный ролик, где я использую сбор данных изменений, доступен по адресу www.technetmagazine.com/video.