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

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

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

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

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

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

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

Важно!

Найти шаблоны в среде SQL Server Management Studioможно, открыв меню Вид, щелкнув пункт Обозреватель шаблонов, а затем выбрав Шаблоны SQL Server. Система отслеживания измененных данных — это вложенная папка. В этой папке можно найти все шаблоны, упоминаемые в данном разделе. Значок Обозреватель шаблонов также присутствует на панели инструментов среды SQL Server Management Studio .

-- ====  
-- 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.

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

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

Важно!

Чтобы найти шаблоны в среде SQL Server Management Studio, перейдите к элементу Просмотр, нажмите кнопку Обозреватель шаблонов, а затем нажмите кнопку Шаблоны SQL Server. Система отслеживания измененных данных — это подпапка, в которой можно найти все шаблоны, упомянутые в этом разделе. Значок Обозреватель шаблонов также присутствует на панели инструментов среды SQL Server Management Studio .

-- =======  
-- 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, чтобы указать файловую группу для таблицы изменений, с которой будет связан экземпляр системы отслеживания. Именованная файловая группа уже должна существовать. Обычно рекомендуется, чтобы таблицы изменений располагались не в той файловой группе, где содержатся исходные таблицы. Enable a Table Specifying Filegroup Option Пример использования параметра @filegroup_name см. в шаблоне.

-- =========  
-- 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.

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

Примечание

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

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

Члены предопределенной роли базы данных 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)