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

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

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

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

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

• Сокращается время разработки. Так как функциональные возможности доступны в SQL Server 2014, вам не нужно разрабатывать пользовательское решение.

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

• Существует встроенный механизм очистки. Очистка для отслеживания изменений выполняется автоматически в фоновом режиме. Настраиваемая очистка данных, хранящихся в боковой таблице, не требуется.

• Функции предоставляются для получения сведений об изменениях.

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

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

• Доступны стандартные средства, которые вы можете использовать для настройки и управления. SQL Server 2014 предоставляет стандартные инструкции DDL, SQL Server Management Studio, представления каталога и разрешения безопасности.

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

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

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

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

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

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

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

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

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

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

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

Рекомендации по типу данных для отслеживания измененных данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Если база данных восстанавливается на том же сервере с таким же именем базы данных, то система отслеживания измененных данных останется активированной.

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

  Для сохранения системы отслеживания измененных данных в активированном состоянии при восстановлении базы данных следует использовать параметр KEEP_CDC. Дополнительные сведения об этом параметре см. в разделе RESTORE.

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

• Если база данных подключена или восстановлена с параметром для любого выпуска, отличного KEEP_CDC от Enterprise, операция блокируется, так как для отслеживания измененных данных требуется SQL Server 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 2014, так как легко настроить и обеспечить высокую производительность.

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

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

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

• Центр разработки Microsoft Sync Framework

  Предоставляет полную документацию по Sync Framework и Sync Services. В документации по службам синхронизации раздел "Практическое руководство. Использование отслеживания изменений SQL Server" содержит подробные сведения и примеры кода.

Связанные задачи (обязательно)

См. также

Функции отслеживания измененных данных (Transact-SQL)
Функции отслеживания изменений (Transact-SQL)
Хранимые процедуры фиксации изменений данных (Transact-SQL)
Таблицы отслеживания измененных данных (Transact-SQL)
Динамические управляемые представления, связанные с отслеживанием изменений данных (Transact-SQL)