Работа с измененными данными (SQL Server)

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

Некоторые функции предоставляются для определения соответствующих значений LSN для использования в запросе TVF. Функция sys.fn_cdc_get_min_lsn возвращает наименьший номер LSN, связанный с интервалом действия экземпляра записи. Интервал действия — это интервал времени, для которого данные об изменении в настоящее время доступны для его экземпляров отслеживания. Функция sys.fn_cdc_get_max_lsn возвращает наибольшее значение LSN в интервале действия. Функции sys.fn_cdc_map_time_to_lsn и sys.fn_cdc_map_lsn_to_time доступны для размещения значений LSN на обычной временной шкале. Так как запись измененных данных использует закрытые интервалы запросов, иногда необходимо создать следующее значение LSN в последовательности, чтобы убедиться, что изменения не дублируются в последовательных окнах запросов. Функции sys.fn_cdc_increment_lsn и sys.fn_cdc_decrement_lsn полезны, если требуется добавочная корректировка значения LSN.

Проверка границ LSN

Перед их использованием рекомендуется проверять границы LSN, которые будут использоваться в запросе TVF. Конечные точки или нулевые конечные точки, лежащие за пределами допустимого диапазона для экземпляра захвата, вызовут ошибку, которая будет возвращена TVF для захвата измененных данных.

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

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

Для запроса net changes возвращается следующая ошибка:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

Сбои авторизации возвращают ошибки при запросе на все изменения, как показано ниже.

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

То же самое верно при запросе на чистые изменения:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

См. шаблон "Перечисление сетевых изменений с помощью TRY CATCH" для демонстрации того, как перехватывать и интерпретировать известные ошибки TVF и возвращать более значимую информацию о неисправности.

Функции запросов

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

• Функция cdc.fn_cdc_get_all_changes_<capture_instance> возвращает все изменения, произошедшие для указанного интервала. Эта функция всегда создается. Записи всегда возвращаются отсортированными, сначала по LSN фиксации транзакционного изменения, а затем по значению, упорядочивающему изменения внутри её транзакции. В зависимости от выбранной опции фильтра строк, либо при обновлении возвращается окончательная строка (фильтр строк 'все'), либо возвращаются и новые, и старые значения (фильтр строк 'все обновления старый').

• Функция cdc.fn_cdc_get_net_changes_<capture_instance> создается, если параметр @supports_net_changes имеет значение 1 при включении исходной таблицы.

  Замечание

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

  Функция netchanges возвращает одно изменение для каждой измененной строки исходной таблицы. Если несколько изменений регистрируются для строки во время указанного интервала, значения столбцов будут отражать окончательное содержимое строки. Чтобы правильно определить операцию, необходимую для обновления целевой среды, TVF должен учитывать как начальную операцию в строке во время интервала, так и последнюю операцию в строке. Если указан параметр фильтра строк "all", операции, возвращаемые запросом net changes, будут включать вставку, удаление или обновление (новыми значениями). Этот параметр всегда возвращает маску обновления в виде null, так как вычисление агрегированной маски связано с затратами. Если требуется агрегатная маска, которая отражает все изменения строки, используйте параметр "все с маской". Если нижестоящей обработке не требуется различать вставки и обновления, используйте параметр "все с слиянием". В этом случае значение операции будет принимать только два значения: 1 для удаления и 5 для операции, которая может быть вставки или обновления. Этот параметр устраняет дополнительную обработку, необходимую для определения того, должна ли производная операция быть вставой или обновлением, и может повысить производительность запроса, если это различие не требуется.

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

Сценарии функций запросов

В следующих разделах описаны распространенные сценарии запросов данных захвата изменений с использованием функций запроса cdc.fn_cdc_get_all_changes_<capture_instance> и cdc.fn_cdc_get_net_changes_<capture_instance>.

Запрос всех изменений в интервале действия экземпляра захвата

Самый простой запрос на данные об изменениях — это такой, который возвращает все текущие данные об изменениях в интервале допустимости экземпляра захвата. Чтобы сделать этот запрос, сначала определите нижние и верхние границы LSN интервала действия. Затем используйте эти значения, чтобы определить параметры @from_lsn и @to_lsn, переданные в функцию запроса cdc.fn_cdc_get_all_changes_<capture_instance> или cdc.fn_cdc_get_net_changes_<capture_instance>. Используйте функцию sys.fn_cdc_get_min_lsn для получения нижней границы и sys.fn_cdc_get_max_lsn для получения верхней границы. Просмотрите шаблон Перечисление всех изменений в пределах допустимого диапазона в качестве примера кода для запроса всех текущих допустимых изменений с использованием функции cdc.fn_cdc_get_all_changes_<capture_instance>. Пример использования функции cdc.fn_cdc_get_net_changes_<capture_instance> см. в разделе "Перечисление чистых изменений" для допустимого диапазона.

Запрос всех новых изменений с момента последнего набора изменений

Для типичных приложений запрос к данным об изменениях будет непрерывным процессом, выполняя периодические запросы для всех изменений, произошедших с момента последнего запроса. Для таких запросов можно использовать функцию sys.fn_cdc_increment_lsn для получения нижней границы текущего запроса от верхней границы предыдущего запроса. Этот метод гарантирует, что строки не повторяются, так как интервал запроса всегда обрабатывается как закрытый интервал, в котором оба конечных точки включаются в интервал. Затем используйте функцию sys.fn_cdc_get_max_lsn для получения высокой конечной точки для нового интервала запроса. См. шаблон "Перечисление всех изменений с предыдущего запроса" для систематического перемещения окна запроса, чтобы получить все изменения с момента последнего запроса.

Запрос получения всех новых изменений на данный момент

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

После того как цикл задержки проверяет, что процесс записи уже обработал все соответствующие записи журнала, используйте функцию sys.fn_cdc_map_time_to_lsn для определения нового высокого конечного значения, выраженного как значение LSN. Чтобы убедиться, что все записи, зафиксированные до указанного времени, извлекаются, вызовите функцию sys.fn_cdc_map_time_to_lsn и используйте опцию "наибольшее меньше или равно".

В шаблоне "Перечислить все изменения до сих пор" демонстрируется, как использовать предыдущую стратегию для запроса данных об изменениях.

Добавление времени фиксации изменений в набор результатов всех изменений

Время фиксации каждой транзакции с связанной записью в таблице изменений базы данных доступно в cdc.lsn_time_mapping таблицы. Сопоставив значение __$start_lsn, возвращенное в результате запроса для всех изменений, со значением start_lsn записи в таблице cdc.lsn_time_mapping, вы можете вернуть tran_end_time вместе с данными об изменении, чтобы пометить изменение временем фиксации транзакции на источнике. Шаблон "Добавить время фиксации ко всем изменениям в наборе результатов" демонстрирует, как выполнить это соединение.

Присоединение данных об изменении с другими данными из той же транзакции

Иногда полезно присоединять данные об изменениях с другими сведениями, собранными о транзакции при фиксации в источнике. Столбец tran_begin_lsn в таблице cdc.lsn_time_mapping предоставляет сведения, необходимые для выполнения такого соединения. При обновлении источника значение database_transaction_begin_lsn из системного динамического представления sys.dm_tran_database_transactions необходимо сохранить вместе с другими сведениями, которые необходимо объединить с измененными данными. Используйте функцию fn_convertnumericlsntobinary для сравнения значений database_transaction_begin_lsn и tran_begin_lsn. Код для создания этой функции доступен в шаблоне Create Function fn_convertnumericlsntobinary. Шаблон "Возврат всех изменений с заданным tran_begin_lsn" демонстрирует, как осуществить объединение.

Запросы с использованием функций обёртки для временных данных

Типичный сценарий приложения для запроса данных об изменении — периодически запрашивать измененные данные с помощью скользящего окна, ограничивающего значения даты и времени. Для этого класса потребителей запись измененных данных предоставляет хранимую процедуру sys.sp_cdc_generate_wrapper_function , которая создает скрипты для создания пользовательских функций оболочки для функций запроса отслеживания измененных данных. Эти пользовательские оболочки позволяют выразить интервал запроса в виде пары datetime.

Параметры вызова хранимой процедуры позволяют создавать оболочки для всех экземпляров захвата данных, к которым вызывающий объект имеет доступ, или только для указанного экземпляра захвата. Поддерживаемые параметры также включают возможность указать, должна ли высокая конечная точка интервала отслеживания открываться или закрываться, какие из доступных захваченных столбцов должны быть включены в результирующий набор и какие из включенных столбцов должны иметь связанные флаги обновления. Процедура возвращает результирующий набор с двумя столбцами: имя созданной функции, которое является производным от имени экземпляра записи и инструкцией создания хранимой процедуры оболочки. Функция, оборачивающая запрос всех изменений, всегда создается. @supports_net_changes Если параметр был задан при создании экземпляра записи, функция для упаковки функции net changes также создается.

Конструктор приложений отвечает за вызов хранимой процедуры генерации скриптов для создания инструкций создания обёрточных хранимых процедур и за выполнение полученных скриптов создания для создания функций. Это не происходит автоматически при создании экземпляра записи.

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

Имя функции для оборачивания запроса всех изменений: fn_all_changes_ с добавлением имени экземпляра захвата. Префикс, используемый для оболочки сетевых изменений: fn_net_changes_. Обе функции принимают три аргумента, так же, как и связанные с ними табличные функции с захватом изменений данных. Однако интервал запроса для оболочки ограничивается двумя значениями даты и времени вместо двух значений LSN. Параметр @row_filter_option для обоих наборов функций одинаковы.

Созданные функции-оболочки поддерживают следующее соглашение для систематического перехода по временной шкале отслеживания измененных данных: ожидается, что @end_time параметр предыдущего интервала будет использоваться в качестве @start_time параметра последующего интервала. Функция-оболочка заботится о сопоставлении значений даты и времени с значениями LSN и гарантирует, что данные не будут потеряны или повторяются при соблюдении этого соглашения.

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

Хотя созданные запросы TVF приводят к ошибке, если одно из значений @from_lsn или @to_lsn равно NULL, функции-обертки datetime используют NULL, чтобы функции-обертки datetime могли возвращать все актуальные изменения. То есть, если значение NULL передается в качестве нижней конечной точки окна запроса в оболочку datetime, в базовой инструкции SELECT, применяемой к запросу TVF, используется нижняя конечная точка интервала допустимости экземпляра сбора данных. Аналогичным образом, если значение NULL передается в качестве конечной точки окна запроса, то при выборе из запроса TVF используется высокая конечная точка интервала действия экземпляра записи.

Результирующий набор, возвращаемый функцией-оболочкой, включает все запрошенные столбцы, за которыми следует столбец операции, перекодированный как один или два символа, чтобы определить операцию, связанную с строкой. Если флаги обновления были запрошены, они отображаются в виде битовых столбцов после кода операции в порядке, указанном в параметре @update_flag_list . Чтобы получить информацию о параметрах вызова для настройки созданных оболочек datetime, см. раздел sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Шаблон создания экземпляра оболочки TVF с флагом обновления показывает, как настроить созданную функцию-оболочку, чтобы добавить флаг обновления для указанного столбца в результирующий набор, возвращаемый запросом на чистые изменения. Шаблон "Экземпляры оберток CDC для схемы" демонстрирует, как создать обертки Datetime для запросных ТВФ для всех экземпляров захвата, созданных для исходных таблиц в данной схеме базы данных.

Пример использования оболочки datetime для запроса данных об изменениях см. в шаблоне Get Net Changes Using Wrapper With Update Flags. В этом шаблоне демонстрируется, как запрашивать изменения сети через функцию-оболочку, когда эта оболочка настроена для возврата флагов обновления. Обратите внимание, что параметр фильтра строк "все с маской" необходим для базовой функции запроса, чтобы вернуть маску обновления, отличную от null, при обновлении. Значения NULL передаются как для нижних, так и верхних границ интервала даты и времени, чтобы сигнализировать функции об использовании нижней конечной точки и высокой конечной точки интервала допустимости для экземпляра записи при выполнении базового запроса на основе LSN. Запрос возвращает одну строку для каждого изменения исходной строки, которое произошло в допустимом диапазоне для экземпляра захвата.

Использование функций оболочки Datetime для перехода между экземплярами записи

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

См. также

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