Поделиться через

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

  • Статья

Область применения: 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-изменения Да Да
Отслеживаемые данные
Исторические данные Да Нет
Был ли изменён столбец Да Да
Тип DML Да Да

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

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

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

Схема, показывающая концепцию отслеживания измененных данных.

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

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

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

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

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

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

Учет типов данных для фиксации изменений данных

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

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

Интеграция функций SQL Server

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

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

Зеркалирование базы данных

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

  1. Убедитесь, что агент SQL Server работает на зеркале.

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

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

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

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

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

Примечание.

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

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

Схема, показывающая концепцию отслеживания изменений.

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

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

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

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