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

Отслеживание измененных данных (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-изменения Да Да
Отслеживаемые данные
Данные журнала Да нет
Изменение столбца Да Да
Тип 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.

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

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

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

Концептуальная иллюстрация отслеживания изменений

Отслеживание изменений и службы Sync Services для ADO.NET

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

Дополнительные сведения об отслеживании изменений и Sync Services for ADO.NETсм. по следующим ссылкам.

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

    Содержит описание отслеживания изменений данных, общие сведения о механизме отслеживания изменений и описывает взаимодействие отслеживания изменений с другими функциями компонента Компонент SQL Server Database Engine .

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

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

См. также

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