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

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

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

Для помощи в определении соответствующих значений LSN имеется несколько функций, которые можно использовать в запросах с возвращающими табличное значение функциями. Функция 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_ ...

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

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 и возвращать более подробную информацию о сбое.

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

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

• Функция 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 возвращает одно изменение для каждой измененной строки исходной таблицы. Если в течение интервала запроса для строки было зарегистрировано несколько изменений, то значения столбцов будут отражать конечное содержимое строки. Чтобы правильно определить операцию, необходимую для обновления целевой среды, возвращающая табличное значение функция должна учитывать как начальную, так и конечную операции со строкой в течение интервала запроса. При указании параметра фильтра строк "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 необходимо убедиться, чтобы процесс отслеживания обработал все изменения, зафиксированные до заданной верхней границы. Это необходимо для того, чтобы гарантировать распространение всех соответствующих изменений в таблицу изменений. Одним из способов сделать это является структурирование цикла ожидания, который периодически проверяет, не превышено ли заданное время завершения интервала запроса текущим зафиксированным номером LSN, максимальным по всем таблицам изменений базы данных.

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

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

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

Время фиксации каждой транзакции с соответствующей записью в таблице изменений базы данных доступно в таблице 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 , которая формирует скрипты создания пользовательских функций-оболочек для функций запроса системы отслеживания измененных данных. Эти пользовательские оболочки позволяют выражать интервал запроса как пару даты и времени.

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

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

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

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

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

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

Если в качестве значения параметра @from_lsn или @to_lsn функции созданного запроса, возвращающей табличное значение, указано NULL, то она не завершается успешно, тогда как функции-оболочки datetime используют значение NULL, чтобы оболочки datetime могли возвратить все текущие изменения. То есть, если значение 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 в исходной таблице расширяют набор доступных для отслеживания столбцов. При переходе к новому экземпляру системы отслеживания одним из способов защиты приложения более высокого уровня от изменений в именах базовых функций запроса является использование функции-оболочки для упаковки базового вызова. Затем следует обеспечить, чтобы имя функции-оболочки оставалось неизменным. Когда должно произойти переключение, старая функция-оболочка должна быть удалена; при этом должна быть создана новая функция-оболочка с тем же именем, содержащая ссылку на новые функции запроса. При первом изменении созданного скрипта создания функции-оболочки с тем же именем можно сделать переключение на новый экземпляр системы отслеживания, не затрагивая приложение более высокого уровня.

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

• CDC с База данных SQL Azure
• Отслеживание изменений данных (SQL Server)
• Что такое запись измененных данных (CDC)?
• Включение и отключение отслеживания измененных данных
• Администрирование и мониторинг отслеживания измененных данных