Система отслеживания измененных данных (CDC)
Область применения:
SQL Server Управляемый экземпляр SQL Azure
В этой статье вы узнаете о системе отслеживания измененных данных (CDC), которая записывает действия в базе данных при изменении ее таблиц и строк.
В этой статье объясняется, как CDC работает с SQL Server и Управляемым экземпляром Azure SQL. Сведения о базе данных Azure SQL см. в разделе CDC с базой данных Azure SQL.
Обзор
Система фиксирования изменений данных использует агент 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 также создается и называется путем добавления 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 или управляемом экземпляре Azure SQL активирована только функция захвата изменений данных, создается задание агента SQL Server для захвата изменений, которое служит средством вызова хранимой процедуры sp_replcmds. При наличии репликации только средство чтения журналов транзакций используется для удовлетворения потребностей изменения данных для обоих этих потребителей. Данная стратегия значительно снижает конкуренцию за журналы, когда как система репликации, так и механизм отслеживания изменений данных активированы для одной и той же базы данных.
Переключение между этими двумя режимами работы для записи измененных данных происходит автоматически при изменении состояния репликации включенной базы данных отслеживания измененных данных.
Примечание.
В SQL Server и Управляемом экземпляре Azure SQL для выполнения процесса оба экземпляра логики захвата требуют, чтобы SQL Server Agent был запущен.
Основная задача процесса фиксации данных заключается в сканировании журнала и записи данных столбцов и информации, связанной с транзакциями, в таблицы фиксации изменений данных. Чтобы обеспечить транзакционно согласованную границу между всеми таблицами захвата данных об изменениях, процесс захвата открывает и фиксирует свои собственные транзакции в каждом цикле сканирования. Он обнаруживает вновь активированные таблицы системы отслеживания измененных данных и автоматически включает их в набор таблиц, в которых отслеживается изменение данных. Точно так же обнаруживается отключение системы отслеживания измененных данных, что приводит к удалению исходной таблицы из набора таблиц, в которых проводится наблюдение за изменением данных. Когда завершается обработка раздела журнала, процесс захвата сигнализирует логику усечения журнала на сервере, которая использует эту информацию для определения записей журнала, подлежащих усечению.
Внимание
Если в базе данных было включено отслеживание измененных данных, то даже в том случае, если в качестве модели восстановления базы данных было выбрано простое восстановление, то точка усечения журнала не будет передвинута далее, пока все отслеживаемые изменения не будут собраны процессом отслеживания. Если процесс захвата не выполняется и имеются изменения, которые нужно собрать, выполнение CHECKPOINT не приведет к усечению журнала.
Кроме того, процесс отслеживания используется также для ведения журнала DDL-изменений в отслеживаемых таблицах. Инструкции DDL, связанные с системой отслеживания измененных данных, создают записи в журнале транзакций базы данных каждый раз, когда удаляется база данных или таблица с активированной системой отслеживания измененных данных или добавляются, изменяются или удаляются столбцы такой таблицы. Эти записи журнала обрабатываются процессом отслеживания, который затем отправляет соответствующие DDL-события в таблицу cdc.ddl_history. Сведения о событиях DDL, влияющих на отслеживаемые таблицы, можно получить с помощью хранимой процедуры sys.sp_cdc_get_ddl_history.
Предупреждение
- MaxCmdsInTran не предназначен для включения всегда. Он существует для обхода случаев, когда кто-то случайно выполнял большое количество операций DML в одной транзакции (что приводит к задержке в распределении команд до тех пор, пока вся транзакция не находится в базе данных распространителя, блокировки удерживаются и т. д.). Если вы обычно попадаете в эту ситуацию, просмотрите логику приложения, чтобы найти способы уменьшения размера транзакции.
- MaxCmdsInTran не поддерживается, если в указанной базе данных публикации включены и CDC, и репликация. Использование MaxCmdsInTran в этой конфигурации может привести к потере данных в таблицах изменений CDC. Это также может привести к ошибкам PK, если параметр MaxCmdsInTran добавляется и удаляется при репликации большой транзакции.
Задания агентов
Два задания Агента 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. Ознакомьтесь с дополнительными сведениями о взаимодействии .
Известные проблемы
Сведения о известных проблемах и ошибках, связанных с записью измененных данных, см. в статье "Известные проблемы с CDC".