Работа с информацией об изменениях

применимо к:SQL ServerУправляемому экземпляру SQL Azure

Информация об изменениях предоставляется потребителям функции отслеживания измененных данных через табличнозначные функции (TVF). Для всех вызовов этих функций требуется два параметра для определения диапазона номеров последовательности в журнале (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, которые будут использоваться в запросе к функции, возвращающей табличное значение, перед их использованием. Граничные значения, равные NULL или лежащие вне интервала допустимости для экземпляра сбора данных, приводят к возврату ошибки табличной функцией отслеживания измененных данных.

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

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_ ...

Примечание.

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

Ошибки авторизации возвращают ошибку при запросе всех изменений, как показано ниже.

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'.

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

Подсказка

Чтобы найти шаблоны отслеживания измененных данных в SQL Server Management Studio, в меню "Вид " выберите обозреватель шаблонов, разверните шаблоны SQL Server и разверните папку "Запись измененных данных ".

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

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

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

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

    Примечание.

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

    Функция netchanges возвращает одно изменение для каждой измененной строки исходной таблицы. Если в течение интервала запроса для строки было зарегистрировано несколько изменений, то значения столбцов будут отражать конечное содержимое строки. Чтобы правильно определить операцию, необходимую для обновления целевой среды, табличнозначная функция (TVF) должна учитывать как начальную операцию над строкой в течение интервала, так и конечную операцию над строкой. При указании параметра фильтра строк "all" запрос netchanges будет возвращать операции вставки, удаления или обновления (новые значения). Обратите внимание, что этот параметр всегда возвращает значение маски обновления как значение NULL, потому что вычисление статистической маски требует значительных затрат. Если требуется статистическая маска, отражающая все изменения строки, используется параметр «all with mask». Если для последующей обработки не требуется разделения операций вставки и обновления, то используется параметр «all with merge». В этом случае будут присутствовать только два значения операции: 1— для операции удаления и 5 — для операций вставки и обновления. Этот параметр отключает дополнительные вычисления, позволяющие определить тип производной операции: операция вставки или обновления. Если в таком различении нет необходимости, то использование этого параметра может увеличить производительность.

Маска обновления, возвращаемая функцией запроса — это компактное представление всех изменений столбцов, связанных со строкой информации об изменениях. Обычно такие данные нужны только для небольшого подмножества отслеживаемых столбцов. Имеются функции, способные помочь при извлечении информации из маски в форме, которая напрямую может использоваться приложениями. Функция sys.fn_cdc_get_column_ordinal возвращает порядковую позицию указанного столбца для данного экземпляра перехвата, тогда как функция sys.fn_cdc_is_bit_set возвращает значение, указывающее, установлен ли бит в указанной маске, на основе порядкового номера, переданного в вызове функции. Вместе эти две функции позволяют эффективно извлекать информацию из маски обновления и возвращать ее вместе с запросом данных об изменениях. В SQL Server Management Studio ознакомьтесь с шаблоном Перечисление сетевых изменений с использованием 'Все с маской' для демонстрации использования этих функций.

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

В следующих разделах описываются распространенные сценарии для запроса данных захвата изменений с помощью функций запроса 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 . В среде SQL Server Management Studio обратите внимание на шаблон Перечисление всех изменений допустимого диапазона, чтобы увидеть пример кода для запроса всех текущих допустимых изменений, используя функцию запроса cdc.fn_cdc_get_all_changes_<capture_instance>. В SQL Server Management Studio см. шаблон Перечисление net changes для допустимого диапазона для аналогичного примера использования функции cdc.fn_cdc_get_net_changes_<capture_instance>.

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

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

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

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

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

Примечание.

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

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

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

Время фиксации каждой транзакции с соответствующей записью в таблице изменений базы данных доступно в таблице cdc.lsn_time_mapping. Объединив значение __$start_lsn, возвращенное в запросе всех изменений, со значением cdc.lsn_time_mapping start_lsn записи таблицы, вы можете вернуть 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. Код для создания этой функции доступен в функции создания fn_convertnumericlsntobinaryшаблона. Шаблон Демонстрация всех изменений при заданном tran_begin_lsn, показывающая, как это влияет на объединение.

Запрос с помощью функций оболочки DateTime

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

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

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

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

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

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

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

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

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

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

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

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

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

Связанный контент

  • Last updated on