CDC и DDL
На почившем форуме sqlclub.ru в свое время задавался следующий вопрос.
CDC все красиво пока не начинаешь использовать...
mos Пт дек 05, 2008 11:16
Добрый день
Все знаем про новшество SQL Server 2008. CDC...все красиво пока не начинаешь использовать...
Так никто и не может дать мне ответа, что делать когда cdc (лог) таблица создана уже, записей уже не мало в ней и вот приходит момент что надо поменять структуру таблицы. Делаем изменения и изменений в cdc таблице уже неимеем...что делать? описаного и созданого механизма нету...
Одни гуру очень долго совещали и посоветовали...создать новую таблицу, и перекачивать данные...в ход можно пустить ddl тригеры, для автоматизма и т.д.
А что на этом форуме посоветуют????
Спасибо
Олег
Re: CDC все красиво пока не начинаешь использовать...
alexejs Сб дек 06, 2008 02:00
Я ответил приблизительно таким образом, что независимо от форума лучше думать самому, а не ходить в поисках ответа по гурам. Вот я, например, подумал и пришел к выводу, что CDC - красивая вещь не только до того, как начнешь ее использовать, но и после.
Возьмем за основу пример https://blogs.msdn.com/alexejs/archive/2009/08/07/cdc.aspx. Последовательно пройдем в нем до Скрипта 7.
Если сейчас поменять структуру таблицы, скажем, добавить новое поле неважно, в принципе, какое, то мы видим, что эта операция отразится в таблице cdc.ddl_history:
alter table Products add fld sql_variant
select * from cdc.ddl_history
source_object_id |
object_id |
required_column_update |
ddl_command |
ddl_lsn |
ddl_time |
309576141 |
869578136 |
0 |
alter table Products add fld sql_variant |
0x00000047000001190001 |
07/08/09 20:10:00.000 |
Скрипт 1
однако операции над новым полем, не отражаются в отловленных изменениях:
insert Products values (1, 'ddd', 40, 0x0)
update Products set fld = 0x1 where id = 1
select * from cdc.dbo_Products_CT
__$start_lsn |
__$end_lsn |
__$seqval |
__$operation |
__$update_mask |
id |
name |
price |
0x00000047000000D30006 |
NULL |
0x00000047000000D30002 |
2 |
0x07 |
1 |
aaa |
10 |
0x00000047000000D50006 |
NULL |
0x00000047000000D50002 |
3 |
0x06 |
1 |
aaa |
10 |
0x00000047000000D50006 |
NULL |
0x00000047000000D50002 |
4 |
0x06 |
1 |
bbb |
20 |
0x00000047000000D70006 |
NULL |
0x00000047000000D70002 |
3 |
0x06 |
1 |
bbb |
20 |
0x00000047000000D70006 |
NULL |
0x00000047000000D70002 |
4 |
0x06 |
1 |
ссс |
30 |
0x00000047000000D90006 |
NULL |
0x00000047000000D90004 |
1 |
0x07 |
1 |
ссс |
30 |
0x00000047000001740006 |
NULL |
0x00000047000001740002 |
2 |
0x07 |
1 |
ddd |
40 |
Скрипт 2
В изменениях отразился только insert (последняя строчка), да и то __$update_mask у него проставлена по старинке – 0х07, хотя полей уже не 3, а 4, и она должна быть 1111 – 0х0f. Последовавший за вставкой update поля fld здесь вообще отсутствует, как будто его и не было.
Оно понятно: в процедуре sys.sp_cdc_enable_table в параметре @captured_column_list нового поля не было. Даже если бы мы опустили этот параметр или указали его NULL, что означает, что должны отслеживаться изменения по всем полям, это бы не спасло, потому что поля fld в таблице на тот момент не было. Сказать повторно sp_cdc_enable_table, чтобы включить добавленную колонку, нельзя. Вначале требуется задисейблить существующий процесс отслеживания изменений – см. пост CDC\Скрипт 16. Но при этом вся предыдущая история изменений (Скрипт 2) будет потеряна. Это плохо.
Поэтому я бы, наверное, сделал так. Перед sys.sp_cdc_disable_table скидываем предыдущие изменения во временную таблицу:
if object_id('tempdb.dbo.#Products_CT', 'table') is not null drop table dbo.#Products_CT
select * into dbo.#Products_CT from cdc.dbo_Products_CT
Скрипт 3
Затем дисейблим CDC над таблицей и енейблим по-новой с подхватом изменений в ее структуре.
exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'Products', @capture_instance = 'dbo_Products'
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'
Скрипт 4
@captured_column_list не указан явно, т.е. подразумеваются все колонки. Убеждаемся (см. пост CDC\Скрипт 5)
select * from cdc.captured_columns
Скрипт 5
что поле fld теперь находится в списке отслеживаемых колонок. Переливаем dbo.#Products_CT в новую cdc.dbo_Products_CT
insert cdc.dbo_Products_CT (__$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, id, name, price)select * from dbo.#Products_CT
Скрипт 6
Поля нужно перечислять явно, потому что в новую cdc.dbo_Products_CT добавилось справа поле fld, которого не было в старой. Теперь делаем какое-нибудь DML-изменение над таблицей Products и смотрим, что там стало в изменениях:
update Products set fld = 0x2 where id = 1
waitfor delay '00:00:05'
select * from cdc.dbo_Products_CT
__$start_lsn |
__$end_lsn |
__$seqval |
__$operation |
__$update_mask |
id |
name |
price |
fld |
0x00000047000000D30006 |
NULL |
0x00000047000000D30002 |
2 |
0x07 |
1 |
aaa |
10 |
NULL |
0x00000047000000D50006 |
NULL |
0x00000047000000D50002 |
3 |
0x06 |
1 |
aaa |
10 |
NULL |
0x00000047000000D50006 |
NULL |
0x00000047000000D50002 |
4 |
0x06 |
1 |
bbb |
20 |
NULL |
0x00000047000000D70006 |
NULL |
0x00000047000000D70002 |
3 |
0x06 |
1 |
bbb |
20 |
NULL |
0x00000047000000D70006 |
NULL |
0x00000047000000D70002 |
4 |
0x06 |
1 |
ссс |
30 |
NULL |
0x00000047000000D90006 |
NULL |
0x00000047000000D90004 |
1 |
0x07 |
1 |
ссс |
30 |
NULL |
0x00000047000001740006 |
NULL |
0x00000047000001740002 |
2 |
0x07 |
1 |
ddd |
40 |
NULL |
0x0000004F000000560008 |
NULL |
0x0000004F000000560004 |
3 |
0x08 |
1 |
ddd |
40 |
0x01 |
0x0000004F000000560008 |
NULL |
0x0000004F000000560004 |
4 |
0x08 |
1 |
ddd |
40 |
0x02 |
Скрипт 7
Все нормально: изменения по свежедобавленному полю ловятся, и предыдущая история изменений сохранилась.
Waitfor нужен был для того, что процесс отлова изменений как-никак асинхронный, следовательно, ему нужно время, чтобы изменения отразились – см. пост CDC, разглагольствования после рис.1
Изменять нужно на новое значение. CDC – штука интеллектуальная, и, если в результате апдейта все значения полей остались прежними, она это за изменение не считает, чтобы попусту не раздувать объем таблицы изменений. Это не триггер.
Что я забыл упомянуть? Я забыл перекодировать __$update_mask в истории изменений. Список полей расширился, следовательно, битовая маска должна поменяться. У нас добавилось еще одно поле, значит, вместо 0х07 должно быть 0х0F для операций insert и delete. Надо было сделать update в dbo.#Products_CT. Предоставляется читателям в качестве самостоятельного упражнения. Битовая маска не изменится в истории для операций update. Вспомним, что битовая маска соответствует полям в обратном порядке. Добавилось новое поле. Ему будет соответствовать старший бит. Но все исторические операции это поле не затрагивали, потому что его тогда еще не было. Следовательно, старший бит там везде будет равен нулю, то есть останется то же, что и было.