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 - красивая вещь не только до того, как начнешь ее использовать, но и после.



Возьмем за основу пример
http://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. Вспомним, что битовая маска соответствует полям в обратном порядке. Добавилось новое поле. Ему будет соответствовать старший бит. Но все исторические операции это поле не затрагивали, потому что его тогда еще не было. Следовательно, старший бит там везде будет равен нулю, то есть останется то же, что и было.


Skip to main content