Включение и отключение отслеживания измененных данных (SQL Server)

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

Включение отслеживания измененных данных для базы данных

Перед созданием экземпляра записи для отдельных таблиц член sysadmin предопределенной роли сервера должен сначала включить базу данных для отслеживания измененных данных. Для этого выполните хранимую процедуру sys.sp_cdc_enable_db (Transact-SQL) в контексте базы данных. Чтобы определить, включена ли база данных, выполните запрос к столбцу is_cdc_enabled в представлении каталога sys.databases.

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

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

Пример включения базы данных для отслеживания измененных данных см. в разделе "Включение базы данных для отслеживания измененных данных".

-- ====  
-- Enable Database for CDC template   
-- ====  
USE MyDB  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

Отключение отслеживания измененных данных для базы данных

Член предопределенной sysadmin роли сервера может запустить хранимую процедуру sys.sp_cdc_disable_db (Transact-SQL) в контексте базы данных, чтобы отключить запись измененных данных для базы данных. Перед отключением базы данных не требуется отключить отдельные таблицы. При отключении базы данных удаляются все связанные метаданные отслеживания измененных данных, включая cdc пользователя и схему и задания отслеживания измененных данных. Однако управляющие роли, созданные с помощью отслеживания измененных данных, не удаляются автоматически и их необходимо удалять явно. Чтобы определить, включена ли база данных, выполните запрос к столбцу is_cdc_enabled в каталоге представлений sys.databases.

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

Пример отключения базы данных для отслеживания измененных данных см. в шаблоне "Отключить базу данных для отслеживания измененных данных".

-- =======  
-- Disable Database for Change Data Capture template   
-- =======  
USE MyDB  
GO  
EXEC sys.sp_cdc_disable_db  
GO  

Включение отслеживания измененных данных для таблицы

После включения отслеживания измененных данных члены db_owner предопределенной роли базы данных могут создать экземпляр записи для отдельных исходных таблиц с помощью хранимой процедуры sys.sp_cdc_enable_table. Чтобы определить, была ли исходная таблица уже включена для отслеживания изменений данных, проверьте значение столбца is_tracked_by_cdc в представлении каталога sys.tables.

При создании экземпляра захвата можно указать следующие параметры.

Columns in the source table to be captured.

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

A filegroup to contain the change table.

По умолчанию таблица изменений расположена в файловой группе по умолчанию для базы данных. Если владельцы базы данных хотят управлять расположением отдельных таблиц изменений, то они могут использовать параметр @filegroup_name, чтобы указать файловую группу для таблицы изменений, с которой будет связан экземпляр системы отслеживания. Именованная файловая группа уже должна существовать. Как правило, рекомендуется поместить таблицы изменений в файловую группу отдельно от исходных таблиц. Пример использования параметра @filegroup_name см. в Enable a Table Specifying Filegroup Option шаблоне.

-- =========  
-- Enable a Table Specifying Filegroup Option Template  
-- =========  
USE MyDB  
GO  
  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = N'MyRole',  
@filegroup_name = N'MyDB_CT',  
@supports_net_changes = 1  
GO  

A role for controlling access to a change table.

Именованные роли используются для управления доступом к информации об изменениях. Указана может быть существующая предопределенная роль сервера или роль базы данных. Если указанная роль еще не существует, роль базы данных этого имени создается автоматически. Члены любой sysadmindb_owner роли имеют полный доступ к данным в таблицах изменений. Все остальные пользователи должны иметь разрешение SELECT на все захваченные столбцы исходной таблицы. Кроме того, при указании роли пользователи, которые не являются членами роли sysadmin или db_owner, также должны быть членами указанной роли.

Если вы не хотите использовать роль gating, явно задайте для параметра @role_name значение NULL. Пример активации таблицы без роли, ограничивающей доступ, см. в Enable a Table Without Using a Gating Role шаблоне.

-- =========  
-- Enable a Table Without Using a Gating Role template   
-- =========  
USE MyDB  
GO  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO  
  

A function to query for net changes.

Экземпляр захвата всегда будет включать функцию, возвращающую значения таблицы, для возвращения всех записей таблицы изменений, произошедших в течение заданного интервала. Эта функция называется путем объединения имени экземпляра захвата с "cdc.fn_cdc_get_all_changes_". Дополнительные сведения см. в статье cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Если параметр @supports_net_changes имеет значение 1, то для экземпляра захвата также будет создаваться функция суммарных изменений. Эта функция возвращает только одно изменение для каждой отдельной строки, измененной в течение интервала, указанного в вызове. Дополнительные сведения см. в статье cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

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

См. Enable a Table for All and Net Changes Queries шаблон для примера, демонстрирующего создание экземпляра захвата с использованием обеих функций запроса.

-- =============  
-- Enable a Table for All and Net Changes Queries template   
-- =============  
USE MyDB  
GO  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@role_name     = N'MyRole',  
@supports_net_changes = 1  
GO  

Отключение отслеживания измененных данных для таблицы

db_owner Члены фиксированной роли базы данных могут удалить экземпляр захвата для отдельных исходных таблиц с помощью хранимой процедуры sys.sp_cdc_disable_table. Чтобы определить, включена ли в настоящее время для исходной таблицы система отслеживания измененных данных, рассмотрите столбец is_tracked_by_cdc в представлении каталога sys.tables. Если после того, как имело место отключение, отсутствуют какие-либо таблицы, включенные для базы данных, также происходит удаление заданий отслеживания измененных данных.

Если таблица с включенной функцией отслеживания изменений данных удаляется, то метаданные отслеживания изменений, связанные с этой таблицей, удаляются автоматически.

Пример отключения таблицы см. в шаблоне отключения экземпляра системы отслеживания для таблицы.

-- =====  
-- Disable a Capture Instance for a Table template   
-- =====  
USE MyDB  
GO  
EXEC sys.sp_cdc_disable_table  
@source_schema = N'dbo',  
@source_name   = N'MyTable',  
@capture_instance = N'dbo_MyTable'  
GO  

См. также

Отслеживание изменений данных (SQL Server)
Отслеживание измененных данных (SQL Server)
Работа с измененными данными (SQL Server)
Администрирование и мониторинг отслеживания измененных данных (SQL Server)