Включение и отключение отслеживания измененных данных (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.

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

Столбцы исходной таблицы, которые необходимо отслеживать.

По умолчанию все столбцы исходной таблицы определяются как отслеживаемые. Если необходимо отслеживать только определенное подмножество столбцов (например, для конфиденциальности или для повышения производительности), то для указания этого подмножества следует использовать параметр @captured\_column\_list.

Файловая группа, в которой необходимо хранить таблицу изменений.

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

Роль для управления доступом к таблице изменений.

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

Если использовать шлюзовую роль нежелательно, необходимо явно присвоить параметру @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

Функция для запросов суммарных изменений.

В экземпляр системы отслеживания всегда будет включена возвращающая табличное значение функция, используемая для возвращения всех записей таблицы изменений, произошедших в течение определенного интервала. Имя этой функции образуется путем добавления имени экземпляра отслеживания к строке "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)