Отслеживание изменений данных (SQL Server)

Статья
09/26/2023

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

SQL Server предоставляет две функции, которые отслеживают изменения данных в базе данных: отслеживание изменений и отслеживания изменений. Эти функции позволяют приложениям обнаруживать изменения DML (операции вставки, обновления и удаления), произведенные в пользовательских таблицах базы данных. Отслеживание измененных данных и отслеживание изменений можно включить для одной и той же базы данных без каких-либо дополнительных действий. Выпуски SQL Server, поддерживающие отслеживание изменений и отслеживания изменений, см . в выпусках и поддерживаемых функциях SQL Server 2022.

Преимущества использования отслеживания изменений или отслеживания изменений

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

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

Сокращается время разработки. Так как функциональные возможности доступны в SQL Server, вам не нужно разрабатывать пользовательское решение.
Изменения схемы не требуются. Вам не нужно добавлять столбцы, добавлять триггеры или создавать боковую таблицу, в которой можно отслеживать удаленные строки или хранить сведения об отслеживании изменений, если столбцы не могут быть добавлены в пользовательские таблицы.
Существует встроенный механизм очистки. Очистка для отслеживания изменений выполняется автоматически в режиме в сети. Настраиваемая очистка данных, хранящихся в боковой таблице, не требуется.
Для получения сведений об изменениях предоставляются функции.
Существует низкая нагрузка на операции DML. Синхронному отслеживанию изменений всегда сопутствуют некоторые затраты. Однако отслеживание изменений может помочь снизить затраты. Затраты часто оказываются меньше, чем при использовании альтернативных решений, особенно решений с использованием триггеров.
Отслеживание изменений основано на зафиксированных транзакциях. Порядок изменений основан на времени фиксации транзакции. Это позволяет получить надежные результаты при наличии долго выполняемых и перекрывающихся транзакций. Пользовательские решения, использующие значения метки времени, должны быть разработаны для обработки этих сценариев.
Доступны стандартные средства, которые вы можете использовать для настройки и управления. SQL Server предоставляет стандартные инструкции DDL, SQL Server Management Studio, представления каталога и разрешения безопасности.

Различия между отслеживанием изменений и отслеживанием изменений

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

Компонент	система отслеживания измененных данных	отслеживание изменений
Отслеживаемые изменения
DML-изменения	Да	Да
Отслеживаемые данные
Данные журнала	Да	No
Изменение столбца	Да	Да
Тип DML	Да	Да

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

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

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

Diagram showing the concept of change data capture.

Модель безопасности

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

Настройка и администрирование

Чтобы включить или отключить запись измененных данных для базы данных, вызывающий sys.sp_cdc_enable_db (Transact-SQL) или sys.sp_cdc_disable_db (Transact-SQL) должен быть членом предопределенной роли системного администратора сервера. Включение и отключение отслеживания измененных данных на уровне таблицы требует, чтобы вызывающий объект sys.sp_cdc_enable_table (Transact-SQL) и sys.sp_cdc_disable_table (Transact-SQL) был членом роли sysadmin или членом роли базы данных db_owner.

Использование хранимых процедур для администрирования заданий отслеживания измененных данных ограничено членами серверной роли sysadmin и членами роли database db_owner .

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

Чтобы получить доступ к измененным данным, связанным с экземпляром записи, пользователю необходимо предоставить доступ SELECT ко всем захваченным столбцам связанной исходной таблицы. Кроме того, если при создании экземпляра записи указана роль gating, вызывающий объект также должен быть членом указанной роли gating, а схема отслеживания измененных данных (cdc) должна иметь доступ SELECT к роли gating.

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

Операции DDL для изменения исходных таблиц с поддержкой записи данных

Если в таблице включена система отслеживания измененных данных, операции DDL могут применяться только членом предопределенной роли сервера sysadmin, членом роли database role db_ownerили database role db_ddladmin. Для пользователей, которым явно предоставлены права на выполнение DDL-операций в такой таблице, выводится сообщение об ошибке 22914, если они пытаются выполнить такие операции.

Тип столбца	Изменения, отслеживаемые в таблицах изменений	Ограничения
Разреженные столбцы	Да	Не поддерживает запись изменений при использовании набора столбцов.
Вычисляемые столбцы	Нет	Изменения вычисляемых столбцов не отслеживаются. Столбец отображается в таблице изменений с соответствующим типом, но имеет значение NULL.
XML	Да	Изменения отдельных XML-элементов не отслеживаются.
Метка времени	Да	Тип данных в таблице изменений будет преобразован в двоичный.
Типы данных BLOB	Да	Предыдущий снимок столбца BLOB будет сохранен только при изменении самого столбца.

Изменение записи данных и других функций SQL Server

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

Зеркальное отображение базы данных
Репликация транзакций
Восстановление или присоединение базы данных

Зеркальное отображение базы данных

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

Убедитесь, что агент SQL Server выполняется в зеркало.
Создайте задание отслеживания и задание очистки на зеркальном сервере после того, как был выполнен переход с основного сервера на зеркальный. Чтобы создать задания, используйте хранимую процедуру sys.sp_cdc_add_job (Transact-SQL).

Дополнительные сведения о зеркало базах данных см. в разделе "Зеркальное отображение базы данных" (SQL Server).

Репликация транзакций

Система отслеживания измененных данных и репликация транзакций могут сосуществовать в одной базе данных, но если обе эти функции были включены, то заполнение таблиц изменений будет выполняться другим способом. Для считывания изменений из журнала транзакций система отслеживания измененных данных и репликация транзакций всегда используют одну и ту же процедуру sp_replcmds. Если запись измененных данных включена самостоятельно, вызовы sp_replcmdsзаданий агент SQL Server. Если оба компонента включены в одной базе данных, агент чтения журналов вызывает sp_replcmds. Этот агент заполняет таблицы изменений и distribution таблицы базы данных. Дополнительные сведения см. в статье Replication Log Reader Agent.

Рассмотрим случай, когда для базы данных AdventureWorks2022 была включена система отслеживания измененных данных и две таблицы были включены для отслеживания. Для заполнения таблиц изменений задание отслеживания вызывает процедуру sp_replcmds. База данных активируется для репликации транзакций, после этого создается публикация. Для базы данных создается агент чтения журнала, задание отслеживания удаляется. Агент чтения журнала продолжает просматривать журнал, начиная с последнего регистрационного номера транзакции, зафиксированного в таблице изменений. Это обеспечивает согласованность данных в таблицах изменений. Если транзакционные реплика tion отключены в этой базе данных, агент чтения журналов удаляется, а задание записи создается повторно.

Примечание.

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

Восстановление или присоединение базы данных для отслеживания измененных данных

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

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

Для сохранения системы отслеживания измененных данных в активированном состоянии при восстановлении базы данных следует использовать параметр KEEP_CDC. Дополнительные сведения об этом параметре см. в разделе RESTORE.
Если база данных отсоединяется и присоединяется к тому же или другому серверу, то система отслеживания измененных данных остается активированной.
Если база данных подключена или восстановлена с KEEP_CDC параметром к любому выпуску, отличному от Standard или Enterprise, операция блокируется, так как для отслеживания измененных данных требуется выпуски SQL Server Standard или Enterprise. Отображается сообщение об ошибке 932.
```
SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.
```

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

отслеживание изменений

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

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

Diagram showing the concept of change tracking.

Отслеживание изменений и службы синхронизации для ADO.NET

Службы синхронизации для ADO.NET обеспечивают синхронизацию между базами данных, предоставляя интуитивно понятный и гибкий API, который позволяет создавать приложения, предназначенные для сценариев автономной работы и совместной работы. Службы синхронизации для ADO.NET предоставляют API для синхронизации изменений, но на самом деле не отслеживает изменения в базе данных сервера или одноранговой базы данных. Можно создать нестандартную систему отслеживания изменений, однако обычно это сопряжено со значительным усложнением среды и повышенными затратами ресурсов. Чтобы отслеживать изменения в сервере или одноранговой базе данных, рекомендуется использовать отслеживание изменений в SQL Server, так как легко настроить и обеспечить высокую производительность.

Дополнительные сведения об отслеживании изменений и службах синхронизации для ADO.NET см. по следующим ссылкам:

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

Описывает отслеживание изменений, предоставляет общий обзор работы отслеживания изменений и описывает взаимодействие отслеживания изменений с другими функциями SQL Server ядро СУБД.
Центр разработки Microsoft Sync Framework

Предоставляет полную документацию по Службам Sync Framework и Sync Services.

Следующие шаги

Задача	Статья
Содержит общие сведения о системе отслеживания измененных данных.	Отслеживание измененных данных (SQL Server)
Описывает, как включать и отключать отслеживание измененных данных для базы данных или таблицы.	Включение и отключение отслеживания измененных данных (SQL Server)
Описывает, как управлять мониторингом измененных данных и контролировать его.	Администратор и мониторинг отслеживания измененных данных (SQL Server)
Описывает способы работы с информацией об изменениях, доступные потребителям отслеживания информации об изменениях. В этой статье рассматриваются проверки границ LSN, функций запросов и сценариев функций запросов.	Работа с измененными данными (SQL Server)
Содержит общие сведения об отслеживании изменений.	Сведения о Отслеживание изменений (SQL Server)
Описывает, как включать и отключать отслеживание изменений для базы данных или таблицы.	Включение и отключение Отслеживание изменений (SQL Server)
Описывает, как управлять отслеживанием изменений, настраивать безопасность и определять влияние использования отслеживания изменений на производительность и требуемый объем хранилища.	Управление Отслеживание изменений (SQL Server)
Описывает, как приложения, использующие отслеживание изменений, могут получать отслеженные изменения, применять эти изменения к другому хранилищу данных и обновлять базу данных-источник. В этой статье также описывается отслеживание изменений ролей при отработки отказа, а база данных должна быть восстановлена из резервной копии.	Работа с Отслеживание изменений (SQL Server)