На протяжении многих лет я занимался разработкой решений, которые помогают идентифицировать изменения в Data Manipulation Language (DML). Эти решения были столь же эффективными, сколь и громоздкими. SQL Server 2012 отменил необходимость в данном типе решений, что сильно упростило работу. В SQL Server 2012 предусмотрено два средства, которые помогут вам без труда контролировать изменения: change data capture или cdc (отслеживание измененных данных) и change tracking (отслеживание изменений).

По большому счету, инструменты для отслеживания измененных данных и отслеживания изменений взаимозаменяемы, за одним исключением. Оба средства используют механизм синхронного отслеживания, то есть перегрузка SQL Server сведена к минимуму. В таблице 1 показано, что именно может отслеживаться с помощью двух функций. Как видите, основное отличие между ними заключается в отслеживании данных журнала. Только система отслеживания измененных данных позволяет вам отслеживать фактически измененные данные.

 

Что отслеживает функция

Логистика

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

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

EXEC sys.sp_cdc_enable_db;

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

EXEC sys.sp_cdc_enable_table
[ @source_schema = ] 'source_schema',
[ @source_name = ] 'source_name',
[ @role_name = ] 'role_name'

— Определите шлюзовую роль, если это желательно

— или установите NULL, если нежелательно.

[,[ @capture_instance = ] 'capture_instance'

— изменения имени экземпляра

[,[ @supports_net_changes = ] supports_net_changes ]
[,[ @index_name = ] 'index_name' | NULL ]
[,[ @captured_column_list = ] 'captured_column_list' |
NULL ]

— Используйте NULL, если хотите, чтобы отслеживались все столбцы.

[,[ @filegroup_name = ] 'filegroup_name' ]
[,[ @allow_partition_switch = ] 'allow_partition_switch' ]

Вам необходимо предоставить такую информацию, как схема исходной таблицы и имя того, кому разрешен доступ к изменению данных, а также члена шлюзовой роли, если была желательна ее установка. Подробную информацию о синтаксисе можно найти в sys.sp_cdc_enable_table (Transact-SQL) (http://technet.microsoft.com/en-us/library/bb522475.aspx) в SQL Server 2012 Books Online (BOL).

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

Итак, начнем

Чтобы представить вам типы информации для ввода, я написал сценарий, который:

  • создает базу данных под названием Learn_CDC;
  • создает и заполняет таблицу под названием MyCDCPlay;
  • разрешает отслеживание измененных данных для базы данных Learn_CDC;
  • разрешает отслеживание измененных данных для таблицы MyCDCPlay;
  • добавляет данные в таблицу MyCDCPlay, которые будут проверяться с помощью системы отслеживания измененных данных.

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

 

Структура системных таблиц
Экран 1. Структура системных таблиц

Как получить доступ к информации об отслеживаемых изменениях

Как отмечалось выше, сценарий добавил данные к таблице MyCDCPlay после включения системы отслеживания измененных данных. Чтобы увидеть всю собранную таким образом информацию, вы можете сделать запрос к системным таблицам. Обратите внимание, что SQL Server 2012 BOL не рекомендует непосредственно запрашивать системные таблицы. Для этой цели лучше выполнить хранимые процедуры и функции, приведенные в таблице 2.

 

Рекомендуемые способы выполнения запроса cdc системных таблиц

Для начала вы можете сделать запрос к таблице cdc.captured_columns для получения списка отслеживаемых столбцов. На экране 2 показаны результаты запроса.

 

Результаты опроса системных таблиц
Экран 2. Результаты опроса системных таблиц

Запрос к таблице cdc.change_tables возвращает информацию о таблице. На экране 3 вы видите результаты запроса. Обратите внимание, что параметр capture_instance не был задан при выполнении процедуры разрешения таблицы с помощью sys.sp_cdc_enable_table, поэтому в таблице использованы и схема, и имя таблицы по умолчанию.

 

Получение информации о таблице
Экран 3. Получение информации о таблице

Запрос к таблице cdc.dbo_MyCDCPlay_CT предоставляет информацию об изменениях данных. Как показано на экране 4, это информация, определенная во время двух операций INSERT.

 

Информация об изменениях
Экран 4. Информация об изменениях

На этой стадии давайте обновим данные в таблице MyCDCPlay с помощью команды:

UPDATE MyCDCPlay SET cdccol2 = 'be'
WHERE cdccol1 = 'CDC4Play'

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

 

Информация об обновлении
Экран 5. Информация об обновлении

Запрос к таблице cdc.ddl_history дает вам информацию об изменениях схемы. Однако в данном случае сценарием не обусловлены никакие изменения, а это значит, что результаты не будут получены. Давайте внесем кое-какие изменения в схему и посмотрим, что получилось:

ALTER TABLE MyCDCPlay ADD cdccol5 varchar(30) Sparse NULL
ALTER TABLE MyCDCPlay ALTER COLUMN cdccol1 VARCHAR(50)
NOT NULL

Если вы запрашиваете таблицу cdc.ddl_history, то получаете результаты, как на экране 6. Как видите, изменения схемы зафиксированы, а также есть информация о том, когда произошли изменения.

 

Информация об изменениях схемы
Экран 6. Информация об изменениях схемы

Наконец, вы можете запросить следующие таблицы:

  • cdc.index_columns. Запрос к таблице cdc.index_columns позволяет получить информацию о столбцах, входящих в индекс, связанных с измененной таблицей.
  • cdc.lsn_time_mapping. Запрос к таблице cdc.lsn_time_mapping возвращает информацию о времени фиксации каждой транзакции.
  • dbo.systranschemas. Запрос к таблице dbo.systranschemas позволяет увидеть изменения в схеме и тип измененной схемы.

Обратная сторона

У инструментов для отслеживания измененных данных и отслеживания изменений есть и свои недостатки. Перечислим некоторые из них:

  • Такие средства обеспечивают решение «таблица за таблицей». Однако они могут показаться громоздкими, если ваше окружение исчисляется сотнями таблиц. Обычно это заставляет задуматься о переходе на инструментарий сторонних фирм.
  • Не фиксируется информация о пользователе, который внес изменения. Для этих целей Microsoft рекомендует использовать функции аудита SQL Server (http://technet.microsoft.com/en-us/library/cc280386.aspx).
  • Синхронные операции способны замедлить фиксацию транзакций.
  • Не отслеживаются изменения в вычисляемых столбцах. К тому же существуют ограничения при отслеживании изменений в столбцах, содержащих XML, Sparse, Timestamp и типы данных BLOB. Более подробную информацию об этих ограничениях можно найти в Track Data Changes (SQL Server) по адресу http://msdn.microsoft.com/en-us/library/bb933994.aspx в SQL Server 2012 BOL.

Изменения в документах и прочее

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

Листинг. Сценарий создания тестовой базы данных и таблицы

CREATE DATABASE Learn_CDC
GO
USE Learn_CDC
GO
CREATE TABLE MyCDCPlay (ID INT IDENTITY(1,1) PRIMARY KEY,
cdccol1 VARCHAR(10) NOT NULL,
cdccol2 CHAR(3) SPARSE NULL,
cdccol3 VARCHAR(20) SPARSE NULL);
GO
INSERT INTO MyCDCPlay (cdccol1,cdccol2, cdccol3)
VALUES ('CDC1Play', 'YES', 'Some cool stuff')
GO
INSERT INTO MyCDCPlay (cdccol1)
VALUES ('CDC2Play')
GO
— Убедитесь в наличии данных.
SELECT * FROM MyCDCPlay
— Разрешите CDC для базы данных.
USE Learn_CDC
GO
EXECUTE sys.sp_cdc_enable_db;GO
— Разрешите CDC для таблицы. Убедитесь, что
— SQL Server Agent запущен.
USE Learn_CDC;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'MyCDCPlay'
, @role_name = NULL
GO
— Вставьте больше отслеживаемых данных.
INSERT INTO MyCDCPlay (cdccol1,cdccol2, cdccol3)
VALUES ('CDC4Play', 'No', 'Some cool stuff')
GO
INSERT INTO MyCDCPlay (cdccol1)
VALUES ('CDC5Play')
GO
— Убедитесь в наличии данных.
SELECT * FROM MyCDCPlay;