CDC

По материалам постов Чт Ноя 29, 2007 11:55, Пт ноя 21, 2008 18:00, Сб дек 06, 2008 02:00 на sqlclub.ru.

Дема показывалась на лонче SQL Server 2008 и на TechDays Fall'08. Издание третье, исправленное и дополненное.

Отслеживание изменений является классической задачей при погружении данных в хранилище. Объемы таковы, что позволить себе роскошь каждый вечер перезаливать все по-новой, не может никто. С инкрементным приращением таблиц фактов дело обстоит более-менее цивилизованно. В них всегда имеется поле типа даты совершения операции, по которому можно отсечь старые записи, которые уже имеются в хранилище. Хуже, когда бизнес-юзеры делают исправления задним числом, причем не сторнирующей проводкой, а напрямую. Справочники также имеют обыкновение не только прирастать, но и правиться, причем в операционной базе это, как правило, изменения in-place. Перетерли старое значение – и до свидания, а вы уже там в своем хранилище решайте, медленно меняющееся измерение какого рода оно у вас там будет. Раньше, как я писал в посте "Аудит в SQL Server. Триггер на логон", для отслеживания изменений использовались таймстэмпы, триггеры, профайлер, а также читалки лога, покупные или самописные, потому что глупо изобретать какой-то дополнительный механизм, если все изменения по определению лежат в логе, и все, что в данном случае требуется, это человеческий способ их там посмотреть. В 2008-м, наконец, такой способ появился под названием Change Data Capture (CDC). Поскольку хранилища – вещь серьезная, он входит в редакцию Enterprise (и, понятно, в Developer и Evaluation). Я буду иллюстрировать и по ходу пояснять.

Для начала проверяем, запущен ли SQL Agent, и, если нет, запускаем. Сейчас мы увидим, для чего он нам нужен.

if not exists(select 1 from sys.sysprocesses where program_name like 'SQLAgent%')

exec master..xp_servicecontrol 'start', 'SQLSERVERAGENT'

Скрипт 1

Как видите, я по привычке продолжаю пользовать sysprocesses несмотря на наличие таких замечательных DMV, как dm_exec_sessions, которое, по идее, было призвано заменить собой sysprocesses, как dm_exec_requests и т.д. Дело здесь не в моей заскорузлости, а вот в этой заметке - https://sqlblog.com/blogs/kalen_delaney/archive/2008/06/29/why-i-still-need-sysprocesses.aspx.

Далее нужно заенейблить базу для CDC. На системных базах CDC не работает, в том числе на tempdb; создадим свою для экспериментов, а в ней – табличку, изменения по которой будем отслеживать. Каждая запись может меняться неоднократно, соответственно, по ней могут отслеживаться все промежуточные изменения или выводиться только финальная картина, что с ней в итоге стало. Последний тип изменений называется net changes. Для его поддержки по таблице должен иметься уникальный индекс, очевидно, для того, чтобы CDC могла сделать group by всех промежуточных изменений в разрезе по записям, а для этого ей нужно однозначно идентифицировать каждую запись.

use tempdb

if exists (select 1 from sys.databases where name = 'cdc_test')

begin

alter database cdc_test set single_user with rollback immediate

drop database cdc_test

end

create database cdc_test

go

use cdc_test

create table Products (id int not null, name varchar(50), price smallmoney)

create unique index id_idx on Products(id)

Скрипт 2

И, натурально, включим на базе этот самый CDC. Из интерфейса SSMS эти операции недоступны, но в Т-SQL все делается максимально юзер-френдли:

exec sys.sp_cdc_enable_db

Скрипт 3

Над каждой таблицей, по которой мы хотим отслеживать изменения, CDC требуется включить отдельно. В особенности юзер-френдли выглядит включение CDC над таблицей:

exec sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'Products',

@role_name = N'cdc_Products',

@capture_instance = N'dbo_Products',

@supports_net_changes = 1,

@index_name = 'id_idx',

@captured_column_list = N'id, name, price',

@filegroup_name = null;

Скрипт 4

С первыми двумя параметрами процедуры все понятно – это схема и имя таблицы, изменения над которой будем ослеживать. Третий параметр – имя роли, которой дозволяется эти изменения смотреть, причем предопределенные роли типа db_owner не допускаются, приходится придумывать. Радует, что если указанная роль не существовала, она при этом создается автоматически. Хотя, конечно, можно было роль создать заранее, напихать в нее членов и подсунуть процедуре. Четвертый параметр хитрый. CDC создает схему cdc и в ней кучу служебных таблиц, функций и процедур для своих надобностей. Обычно внутренние таблицы видны в sys.internal_tables, их имена заканчиваются на _<object_id объекта, который данная внутренняя таблица обслуживает>, и доступиться до них нельзя иначе, как из DACa. Мы уже сталкивались с внутренней таблицей кандидатов в покойники в посте "Конфигурирование FILESTREAM. FILESTREAM и сборка мусора (удаление старых версий файлов)". В данном случае служебные объекты доступны с обычного соединения, и хвосты к их стандартным именам можно задавать на свое усмотрение. Например, служебная функция cdc.fn_cdc_get_all_changes, показывающая все изменения, произошедшие над таблицей, в данном случае будет называться cdc.fn_cdc_get_all_changes_dbo_Products. Это сделано потому, что над одной таблицей можно создать, вообще говоря, два capture instances. Один будет отслеживать такие-то изменения по таким-то колонкам, другой по другим. Хвосты нужны, чтобы различить, к результатам какого экземпляра отслеживания мы хотим обратиться. Параметр @supports_net_changes я уже косвенно упоминал; он означает, поддерживается ли дополнительно вывод итоговой картины, если над одной записью произошло несколько изменений. В этом случае над таблицей должен присутствовать уникальный индекс, который и указывается в следующем параметре, @index_name. По умолчанию, он имеет значение NULL, что означает, что CDC будет искать первичный ключ над таблицей в качестве индекса и очень расстроится, если не найдет. В параметре @captured_column_list перечисляются названия полей, изменения по которым хотим отслеживать. Поля, образующие первичный ключ таблицы (или unique index), должны присутствовать обязательно. По умолчанию, параметр имеет значение NULL, что означает, что включаются все поля. Наконец, параметр @filegroup_name задает файл-группу, на которую будут складываться отслеженные изменения в исходной таблице. По умолчанию, он имеет значение NULL, что означает дефолтную файл-группу.

После выполнения Скрипта 4 в SQL Agente создалась пара джобов:

select j.name, j.enabled, j.description, c.name from msdb.dbo.sysjobs j join msdb.dbo.syscategories c on j.category_id = c.category_id

рис.1

из которых понятно, по какому принципу действует CDC. Это просто читалка лога, как и в случае репликации. Только репликация потом доставляет транзакции на подписчиков, а CDC Log Scan Job складывает в таблицу изменений, которую со временем чистит CDC Cleanup Job. В принципе, их можно создать руками при помощи хранимой процедуры sp_cdc_add_job. В ее параметрах можно указать, с какой периодичностью Log Scan читает лог (по умолчанию 5 сек., диапазон 0 - сутки) и сколько собранные транзакции хранятся в таблице изменений прежде, чем их выкинет оттуда Cleanup (по умолчанию 3 суток, максимально 100 лет). Посмотреть существующие CDCшные джобы можно в таблице msdb.dbo.cdc_jobs. Что какая колонка в ней означает, можно прочитать в документации. В мае перевод слегка поправили, так как раньше он напоминал типовую китайскую инструкцию. Поскольку минута смеха продлевает жизнь на сколько-то там, русский BOL просто рекомендуется читать для долголетия. Изменить параметры существующих джобов по многочисленным пожеланиям трудящихся позволяет процедура sys.sp_cdc_change_job. Вручную почистить таблицы изменений можно с помощью sys.sp_cdc_cleanup_change_table. Дальше посмотрим.

Имеется также куча новых системных таблиц в 2008 и колонок в существовавших, которые позволяют получить метаданные о процессе CDC. Например,

select name from sys.databases where is_cdc_enabled = 1 --Над какими базами включено

select * from cdc.change_tables --Над какими таблицами в базе

select name, is_tracked_by_cdc from sys.tables --аналогично

select * from cdc.captured_columns --Над какими колонками отслеживаются изменения       

select * from cdc.index_columns --Какие поля входят в РК / unique index по таблицам      

exec sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'Products' --показывает общую конфигурацию экземпляров отслеживания, ассоциированных с таблицей @source_name

Скрипт 5

Чтобы посмотреть, как это работает на практике, поизгаляемся немного над несчастной таблицей

insert Products values (1, 'aaa', 10)

update Products set name = 'bbb', price = 20 where id = 1

update Products set name = 'ссс', price = 30 where id = 1

delete Products where id = 1

Скрипт 6

и попробуем теперь получить сделанные изменения. Кусок лога, относящийся к данному экземпляру отслеживания (capture instance – см. одноименный параметр процедуры sp_cdc_enable_table, Скрипт 4) лежит в таблице по имени cdc.<экземпляр отслеживания>­_СТ (последние, видимо, от change table – таблица изменений). Ее можно спокойно просматривать:

select * from cdc.dbo_Products_CT

__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	id	name	price
0x00000035000001110006	NULL	0x00000035000001110002	2	0x07	1	aaa	10
0x00000035000001130006	NULL	0x00000035000001130002	3	0x06	1	aaa	10
0x00000035000001130006	NULL	0x00000035000001130002	4	0x06	1	bbb	20
0x00000035000001150006	NULL	0x00000035000001150002	3	0x06	1	bbb	20
0x00000035000001150006	NULL	0x00000035000001150002	4	0x06	1	ссс	30
0x00000035000001170006	NULL	0x00000035000001170004	1	0x07	1	ссс	30

Скрипт 7

Ее структура описана в BOL - https://msdn.microsoft.com/ru-ru/library/bb500305.aspx. Первые три колонки сугубо логовские. LSN – это log sequence number, своего рода IDшник, которым снабжаются все транзакции в логе. Seqval – номер операции внутри транзакции. Оperation – код операции: 1 = delete, 2 = insert, 3 = update (before image), 4 = update (after, т.е. redo). В конце таблицы идут колонки, соответствующие параметру @captured_column_list процедуры sp_cdc_enable_table (см. Скрипт 4). В них указываются значения соответствующих колонок исходной таблицы на момент операции. Update_mask – это битовая маска, в которой единички стоят в тех позициях, какие колонки обновились. Для простоты позиции колонок соответствуют не тому порядку, в котором они перечислены, а наоборот, т.е. справа налево. Например, в Скрипте 6 у нас апдейтились поля Name и Price. Читаем колонки в порядке, обратном тому, как они были заданы в @captured_column_list и как они показываются в Скрипте 7, то есть Price, Name, ID. Ставим единички проапдейченым полям. Получаем 110, или 0х06, что и стоит в колонке update_mask. Понятно, что для операций insert, delete все колонки считаются обновившимися, то есть единички стоят во всех разрядах.

Вместо непосредственного чтения таблицы изменения можно воспользоваться функцией fn_cdc_get_all_changes, на конце которой, естественно, должен стоять соответствующий экземпляр отслеживания. Она принимает LSNы, в диапазоне которых мы хотим получить изменения, и какие, собственно, изменения мы хотим получить. 'Аll update old' в качестве третьего параметра означает, что для каждой операции update будут выводиться две строки – со значениями, которые были до обновления, и которые стали в результате. Просто 'All' означает, что будут выводиться только новые значения.

declare @from_lsn binary(10), @to_lsn binary(10)

select @from_lsn = min(__$start_lsn) from cdc.dbo_Products_CT

select @to_lsn = max(__$start_lsn) from cdc.dbo_Products_CT

select * from cdc.fn_cdc_get_all_changes_dbo_products(@from_lsn, @to_lsn, 'all update old')

Скрипт 8

Выдача, которая будет получена в результате этого скрипта, совпадает со Скриптом 8.

Для ленивых, которым в лом написать select min/max() ... существуют функции

declare @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Products')

declare @to_lsn binary(10) = sys.fn_cdc_get_max_lsn()

Скрипт 9

Если в качестве параметра указать имя экземпляра отслеживания, минимальный/максимальный LSN будет получен для него, если в sys.fn_cdc_get_max_lsn ничего не указывать, будет максимальный для всех экземпляров отслеживания.

Поскольку в Скрипте 4 мы включили (@supports_net_changes) поддержку итоговых изменений, вместо fn_cdc_get_all_changes можно применить функцию

select * from cdc.fn_cdc_get_net_changes_dbo_products(sys.fn_cdc_get_min_lsn('dbo_Products'), sys.fn_cdc_get_max_lsn(), 'all')

Скрипт 10

В данном случае итоговые изменения пусты, так как (см. Скрипт 6), мы добавили запись, пару раз ее обновили и удалили, т.е. по существу в таблице в итоге ничего не поменялось.

Для совсем ленивых существуют функции

select sys.fn_cdc_get_column_ordinal ('dbo_Products' , 'price') -- порядковый номер колонки в captured_columns

select sys.fn_cdc_has_column_changed ('dbo_Products' , 'price' , 0x03) -- попадает ли колонка price в заданную маску

select sys.fn_cdc_is_bit_set(3 , 0x03) -- установлен ли бит для колонки на позиции 3 в заданной маске

Скрипт 11

Еще можно искать изменения в диапазоне не по LSN, а по времени. Для этого существует таблица соответствий LSN <-> момент времени под названием cdc.lsn_time_mapping:

select * from cdc.lsn_time_mapping

start_lsn	tran_begin_time	tran_end_time	tran_id	tran_begin_lsn
0x00000035000000BB0001	07/08/09 17:15:38.213	07/08/09 17:15:38.213	0x00	0x00000000000000000000
0x00000035000001110006	07/08/09 17:19:18.033	07/08/09 17:19:18.033	0x000000000E1B	0x00000035000001110001
0x00000035000001130006	07/08/09 17:19:18.033	07/08/09 17:19:18.033	0x000000000E1C	0x00000035000001130001
0x00000035000001150006	07/08/09 17:19:18.050	07/08/09 17:19:18.050	0x000000000E1D	0x00000035000001150001
0x00000035000001170006	07/08/09 17:19:18.050	07/08/09 17:19:18.050	0x000000000E1E	0x00000035000001170001
0x000000350000015B0001	07/08/09 17:24:23.410	07/08/09 17:24:23.410	0x00	0x00000000000000000000
0x00000035000001A50001	07/08/09 17:29:25.340	07/08/09 17:29:25.340	0x00	0x00000000000000000000
0x00000035000001E00001	07/08/09 17:34:26.940	07/08/09 17:34:26.940	0x00	0x00000000000000000000
0x00000035000002540001	07/08/09 17:39:28.540	07/08/09 17:39:28.540	0x00	0x00000000000000000000
0x000000360000001F0001	07/08/09 17:44:30.327	07/08/09 17:44:30.327	0x00	0x00000000000000000000
0x000000360000005A0001	07/08/09 17:49:32.420	07/08/09 17:49:32.420	0x00	0x00000000000000000000

Скрипт 12

так что получить LSN на заданный момент времени или позже по ней не составляет труда: select min(start_lsn) from cdc.lsn_time_mapping where tran_begin_time >= '2009-08-07 17:19:18.050'. Однако и тут это уже все сделано за нас в виде функции

declare @from_lsn varbinary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @start_time)

declare @to_lsn varbinary(10)= sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time)

Скрипт 13

где первый параметр может принимать значения largest less than, largest less than or equal, smallest greater than, smallest greater than or equal, что вообще избавляет от необходимости думать. Предусмотрена и обратная функция sys.fn_cdc_map_lsn_to_time. У нее, понятно, первый параметр отсутствует, т.к. каждый LSN случился в какой-нибудь момент времени, но обратное, вообще говоря, не верно: не факт, что в каждый момент времени случился какой-нибудь LSN. Наши 4 операции из Скрипта 6 – это, очевидно, те, что произошли в 17:19:18. Если мы хотим, допустим, отсечь по времени из диапазона изменений последнее удаление, это можно изобразить приблизительно так: sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2009-08-07 17:19:18.050'). В момент 07/08/09 17:19:18.050 (см. Скрипт 7) произошли две операции: второе обновление и удаление. Функция вернет минимальный LSN за этот момент, т.е. 0x00000035000001150006. Получаем

select * from cdc.fn_cdc_get_net_changes_dbo_products(sys.fn_cdc_get_min_lsn('dbo_Products'), sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', '2009-08-07 17:19:18.050'), 'all')

__$start_lsn	__$operation	__$update_mask	id	name	price
0x00000035000001150006	2	NULL	1	ссс	30

Скрипт 14

Обратите внимание, что она правильно подытожила результаты: значения полей записи указаны по итогам последнего update, но __$operation = 2, т.е. вставка. Раньше этой записи не было, следовательно, ее полагается с этими значениями полей вставить.

Ручная очистка старых изменений выполняется при помощи процедуры sys.sp_cdc_cleanup_change_table. Первый параметр указывает на экземпляр отслеживания, таблицу изменений которого чистим, второй – на LSN, до которого из прошлого чистим. Например, если мы хотим очистить первые три изменения из таблицы Скрипт 7, это можно сделать так:

declare @lsn varbinary(10)

select top 5 @lsn = __$start_lsn from cdc.dbo_Products_CT order by __$start_lsn

select @lsn

exec sys.sp_cdc_cleanup_change_table @capture_instance = 'dbo_Products', @low_water_mark = @lsn

select * from cdc.dbo_Products_CT

__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	id	name	price
0x00000035000001150006	NULL	0x00000035000001150002	3	0x06	1	bbb	20
0x00000035000001150006	NULL	0x00000035000001150002	4	0x06	1	ссс	30
0x00000035000001170006	NULL	0x00000035000001170004	1	0x07	1	ссс	30

Скрипт 15

select top 5 возвращает 0x00000035000001150006. Следовательно, все строки с LSNом меньше будут удалены, что мы и наблюдаем. Если бы мы захотели очистить все изменения для данного экземпляра отслеживания, надо было присвоить declare @lsn varbinary(10) = sys.fn_cdc_get_max_lsn().

Отмена Change Data Capture над таблицей:

exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'Products', @capture_instance = 'dbo_Products'

Скрипт 16

И над всей базой:

exec sys.sp_cdc_disable_db

Скрипт 17

Удаляются все экземпляры отслеживания, схема cdc, объекты в ней и джобы. Роль cdc_Products, даже если она была создана процедурой sys.sp_cdc_enable_table, остается.