Сведения об отслеживании изменений данных (SQL Server)

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

Хорошим примером потребителя данных, предназначенного для этой технологии, является приложение извлечения, преобразования и загрузки (ETL). Приложение ETL постепенно загружает измененные данные из исходных таблиц SQL Server в хранилище данных или витрину данных. Хотя представление исходных таблиц в хранилище данных должно отражать изменения в исходных таблицах, сквозная технология, которая обновляет реплику источника, не подходит. Вместо этого необходим надежный поток информации об изменениях, структурированный таким образом, чтобы клиенты могли применить его к другим целевым предоставлениям данных. Технология фиксации изменений данных предоставляется SQL Server.

Изменение потока данных записи данных

На следующем рисунке показан основной поток данных для системы отслеживания измененных данных.

Источником данных для отслеживания изменений данных является журнал транзакций SQL Server. По мере того, как в исходных отслеживаемых таблицах выполняются операции вставки, обновления и удаления, в журнал добавляются записи, описывающие эти изменения. Журнал служит входом для процесса отслеживания. Это считывает журнал и добавляет сведения об изменениях в связанную таблицу изменений отслеживаемой таблицы. Предусмотрены функции для перечисления изменений, появляющихся в таблицах изменений в заданном диапазоне, а также для возврата данных в виде отфильтрованного результирующего набора. Отфильтрованный результирующий набор обычно используется процессом приложения для обновления представления источника во внешней среде.

Общие сведения об отслеживании измененных данных и экземпляре записи

Прежде чем можно будет отслеживать изменения в отдельных таблицах базы данных, необходимо явно активировать систему отслеживания измененных данных в этой базе данных. Это делается с помощью хранимой процедуры sys.sp_cdc_enable_db. После того как база данных будет активирована, с помощью хранимой процедуры sys.sp_cdc_enable_tableисходные таблицы можно определить как отслеживаемые. Если для таблицы активирована система отслеживания измененных данных, создается связанный экземпляр системы отслеживания изменений для распространения данных об изменениях в исходной таблице. Экземпляр системы отслеживания состоит из таблицы изменений и одной-двух функций запроса. Метаданные, описывающие сведения о конфигурации экземпляра захвата, сохраняются в таблицах метаданных отслеживания изменений cdc.change_tables, cdc.index_columns и cdc.captured_columns. Эти сведения можно получить с помощью хранимой процедуры sys.sp_cdc_help_change_data_capture.

Все объекты, связанные с экземпляром захвата, создаются в схеме захвата измененных данных для активированной базы данных. Требования к имени экземпляра захвата заключаются в том, что оно должно быть допустимым именем объекта и уникальным среди всех экземпляров захвата базы данных. По умолчанию имя — <имя схемы_имя таблицы> исходной таблицы. Связанная с ним таблица изменений именуется путем добавления ключевого слова _CT к имени экземпляра системы отслеживания. Функция, используемая для запроса всех изменений, называется путем добавления fn_cdc_get_all_changes_ к имени экземпляра захвата. Если экземпляр захвата настроен для поддержки net changes, функция запроса net_changes также создается и называется добавлением fn_cdc_get_net_changes_ к имени экземпляра захвата.

Изменение таблицы

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

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

Каждая строка в таблице изменений также содержит дополнительные метаданные для разрешения интерпретации действия изменения. Столбец __$start_lsn идентифицирует номер последовательности журнала фиксации (LSN), который был присвоен изменению. Номер LSN определяет как изменения, которые были зафиксированы в рамках одной транзакции, так и порядок этих транзакций. Столбец __$seqval можно использовать для упорядочивания дополнительных изменений, происходящих в той же транзакции. Столбец __$operation регистрирует операцию, связанную с изменением: 1 = удаление, 2 = вставка, 3 = обновление (исходный образ), 4 = обновление (результирующий образ). Столбец __$update_mask представляет собой переменную битовую маску с одним определенным битом для каждого захваченного столбца. Для вставки и удаления записей маска обновления всегда будет иметь все биты установлены. Однако обновление строк будет иметь только те биты, которые соответствуют измененным столбцам.

Интервал валидности захвата изменённых данных для базы данных

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

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

На завершающем этапе, как только процесс внесения изменений фиксирует каждый новый пакет данных о изменениях, новые записи добавляются в cdc.lsn_time_mapping для каждой транзакции, которая имеет записи в таблице изменений. В таблице сопоставлений сохраняются регистрационный номер транзакции в журнале и время фиксации транзакции (столбцы start_lsn и tran_end_time соответственно). Максимальное значение LSN, найденное в cdc.lsn_time_mapping, представляет собой верхнюю точку интервала допустимости базы данных. Соответствующее время фиксации используется в качестве основы, от которой очистка на основе удержания данных вычисляет новый нижний порог.

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

Интервал допустимости отслеживания измененных данных для экземпляра записи

Хотя интервал действия базы данных и интервал действия отдельного экземпляра захвата обычно совпадают, это не всегда верно. Период действия экземпляра отслеживания начинается, когда процесс отслеживания распознает экземпляр отслеживания и начинает записывать связанные с ним изменения в таблицу изменений. В результате, если экземпляры записи создаются в разное время, каждая из них изначально будет иметь другую низкую конечную точку. Столбец start_lsn результирующего набора, который возвращается функцией sys.sp_cdc_help_change_data_capture , показывает текущую нижнюю конечную точку для каждого определенного экземпляра отслеживания. Когда процесс очистки очищает записи таблицы изменений, он корректирует значения start_lsn для всех экземпляров захвата, чтобы отразить новую нижнюю границу для доступных данных об изменениях. Корректируются только те экземпляры захвата, текущие значения start_lsn которых меньше, чем новый нижний порог. Со временем, если не создаются новые экземпляры захвата, интервалы допустимости для всех экземпляров будут постепенно совпадать с интервалом действительности базы данных.

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

Функция sys.fn_cdc_get_min_lsn используется для получения текущего минимального номера LSN для экземпляра отслеживания, а функция sys.fn_cdc_get_max_lsn — для извлечения текущего максимального номера LSN. При запросе данных об изменении, если указанный диапазон LSN не попадает в интервал между этими двумя значениями LSN, выполнение функций запроса на отслеживание измененных данных приведет к ошибке.

Обработка изменений в исходных таблицах

Чтобы учесть изменения столбцов в отслеживаемых исходных таблицах, это сложная проблема для клиентов нижнего уровня. Хотя активация захвата изменений данных в исходной таблице не препятствует совершению таких изменений DDL, захват изменений данных помогает снизить влияние на потребителей, позволяя остающимся неизменными результирующим наборам, возвращаемым через API, даже если структура столбцов базовой исходной таблицы меняется. Структура с фиксированными столбцами также отражается в базовых таблицах изменений, к которым получают доступ функции запроса.

Чтобы разместить таблицу изменений в фиксированной структуре столбцов, процесс записи, ответственный за заполнение таблицы изменений, будет игнорировать любые новые столбцы, которые не определены для записи, когда исходная таблица была включена для отслеживания измененных данных. Если отслеживаемый столбец удален, значения NULL будут предоставлены для столбца в последующих записях изменений. Однако если существующий столбец подвергается изменению типа данных, изменение распространяется в таблицу изменений, чтобы гарантировать, что механизм отслеживания не приводит к потере данных для отслеживаемых столбцов. Процесс отслеживания также отправляет все изменения в структуре столбцов отслеживаемой таблицы в таблицу cdc.ddl_history. Потребители, желающие получить предупреждение о корректировке, которую, возможно, придется внести в нисходящие приложения, используют хранимую процедуру sys.sp_cdc_get_ddl_history.

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

Связь между заданием записи и журналом репликации транзакций

Логика процесса отслеживания измененных данных внедрена в хранимую процедуру sp_replcmds и во внутреннюю серверную функцию, которая является частью программы sqlservr.exe и также используется транзакционной репликацией для считывания изменений из журнала транзакций. Если запись измененных данных включена только для базы данных, вы создадите задание записи агента SQL Server в качестве транспортного средства для вызова sp_replcmds. При наличии репликации только средство чтения журнала транзакций используется для удовлетворения потребностей изменения данных для обоих этих потребителей. Данная стратегия значительно снижает конкуренцию за журналы, когда как система репликации, так и механизм отслеживания изменений данных активированы для одной и той же базы данных.

Переключение между этими двумя режимами работы для записи измененных данных происходит автоматически при изменении состояния репликации включенной базы данных отслеживания измененных данных.

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

Кроме того, процесс отслеживания используется также для ведения журнала DDL-изменений в отслеживаемых таблицах. Инструкции DDL, связанные с системой отслеживания измененных данных, создают записи в журнале транзакций базы данных каждый раз, когда удаляется база данных или таблица с активированной системой отслеживания измененных данных или добавляются, изменяются или удаляются столбцы такой таблицы. Эти записи журнала обрабатываются процессом отслеживания, который затем отправляет соответствующие DDL-события в таблицу cdc.ddl_history. Сведения о событиях DDL, влияющих на отслеживаемые таблицы, можно получить с помощью хранимой процедуры sys.sp_cdc_get_ddl_history.

Изменение заданий агента отслеживания данных

Два задания Агента SQL Server обычно связаны с базой данных с включенной функцией захвата изменений данных: одно, которое используется для заполнения таблиц изменений, и другое, которое отвечает за очистку этих таблиц. Оба задания состоят из одного шага, выполняющего команду Transact-SQL. Вызываемая команда Transact-SQL — это определенная хранимая процедура отслеживания измененных данных, реализующая логику задания. Оба задания создаются, когда система отслеживания измененных данных активируется в первой таблице базы данных. Задание очистки создается всегда. Задание отслеживания создается, только если для базы данных не определена публикация транзакций. Задание захвата также создается, когда для базы данных включены захват измененных данных и транзакционная репликация, а задание по чтению транзакционного журнала удаляется, поскольку база данных больше не имеет определенных публикаций.

И задание захвата, и задание очистки создаются с использованием параметров по умолчанию. Задание захвата запускается немедленно. Оно выполняется постоянно, обрабатывая до 1 000 транзакций за цикл просмотра с 5-секундной задержкой между циклами. Задание очистки выполняется ежедневно в 2 утра. Он сохраняет записи таблицы изменений в течение 4320 минут или 3 дней, удаляя не более 5000 записей с одной инструкцией удаления.

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

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

Администратор не может явно управлять применяемой по умолчанию конфигурацией заданий агента для системы отслеживания измененных данных. Для изменения параметров конфигурации по умолчанию используется хранимая процедура sys.sp_cdc_change_job . Кроме того, хранимая процедура sys.sp_cdc_help_jobs позволяет просматривать текущие параметры конфигурации. Задача захвата и задача очистки во время запуска извлекают параметры конфигурации из таблицы msdb.dbo.cdc_jobs. Любые изменения, внесенные в эти значения с помощью sys.sp_cdc_change_job , не будут входить в силу, пока задание не будет остановлено и перезапущено.

Предоставляются две дополнительные хранимые процедуры, позволяющие запускать и останавливать задания агента отслеживания измененных данных: sys.sp_cdc_start_job и sys.sp_cdc_stop_job.

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

Запись измененных данных не может работать должным образом, если служба ядра СУБД или служба агента SQL Server запущена в учетной записи NETWORK SERVICE. В этом случае может возникать ошибка 22832.

См. также

Отслеживание изменений данных (SQL Server)
Включение и отключение отслеживания измененных данных (SQL Server)
Работа с измененными данными (SQL Server)
Администрирование и мониторинг отслеживания измененных данных (SQL Server)