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

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

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

-- ================================

USE MyDB
GO

EXEC sys.sp_cdc_enable_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

-- ===================================================

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, также должны быть участниками указанной роли.

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

-- ===================================================

-- Шаблон Enable a Table Without Using a Gating Role

-- ===================================================

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_<экземпляр_отслеживания> (Transact-SQL).

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

Пример создания экземпляра системы отслеживания с обеими функциями см. в шаблоне Enable a Table for All and Net Changes Queries.

=======================================================

-- Шаблон Enable a Table for All and Net Changes Queries

-- =======================================================

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