Change Tracking
Change Data Capture (см. посты CDC, CDC и DDL) появился, дай бог памяти, в СТР4 (по-моему, июль 2007), или даже в СТР3, а в СТР5 (октябрь) мы узрели в SQL Server 2008 еще один механизм отслеживания изменений под названием Change Tracking. Несмотря на то, что они решают, в принципе, одинаковые задачи, разница между ними состоит, в первую очередь, в принципах реализации. CDC, как мы знаем, основан на чтении журнала транзакций, следовательно, изначально асинхронен по своей природе. Change Tracking работает синхронно с изменениями: если проблема возникает в ходе change tracking, транзакция откатывается. Вывод: CDC более подходит для слабосвязанных распределенных моделей, где между происхождением изменения и его отражением допускается временной зазор, Change Tracking более пригоден для сильносвязанных сценариев по типу DTC и пр.двухфазной фиксации, хотя, понятно, ничто не мешает и то, и другое использовать по расписанию. Change Tracking предполагает создание за сценой дополнительной таблицы для каждой таблицы, на которую он включен, в которой хранит первичный ключ, вид операции (insert, update, delete), версию изменения и опциональную битовую маску проапдейченых колонок. Они поддерживаются в актуальном состоянии способом, аналогичным поддержке индексов. Change Tracking более легковесный по сравнению с CDC, т.к. накладные расходы на CDC мы видели, а здесь затраты сопоставимы всего лишь с созданием еще одного индекса на таблицу. В отличие от CDC, Change Tracking не хранит промежуточных изменений, только последнее. По этой причине можно встретить утверждение, что его нельзя использовать для задач аудита, только для синхронизации. Из этого подразумевается, что CDC для аудита использовать как будто можно. При желании можно все, только каждый инструмент предназначен для своих задач. Первичной задачей CDC является пополнение хранилища. По идее, при заливке в конце дня достаточно итоговых изменений, однако в зависимости от задачи может потребоваться вся дневная история. Аудит не есть главный фокус CDC. Несмотря на замечательную статью "Tuning the Performance of Change Data Capture in SQL Server 2008" (https://msdn.microsoft.com/en-us/library/dd266396.aspx), нельзя отрицать, что накладные расходы на CDC весьма существенны. На форуме MSDN когда-то еще давно задавался сугубо практический вопрос: "Что, если у меня 300-гиговая база из 1000 с небольшим таблиц, свыше 1000 пользователей, работающих по OLTP-сценарию, но также имеется довольно сложная отчетность, которую нельзя пустить по зеркалу (отчеты, в свою очередь, также могут вызывать обновления), и я хочу включить аудит по всем таблицам на все время с периодом сохранения не менее 5 лет (дисков, разумеется, достаточно). Не загнется ли в этом случае CDC и не скажет ли мне поддержка, когда я туда обращусь, что CDC никогда не предназначался для этого? (https://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/0938f14d-ae78-4ab3-b714-9a229430118c/). Для задач аудита имеется свой инструментарий, которого мы еще коснемся в одном из следующих постов. В отличие от CDC, который входит только в Enterprise Edition, Change Tracking доступен во всех версиях SQL Server 2008, включая бесплатный Express. На нем построен процесс синхронизации, в частности, в ADO.NET Sync Framework 2.0, где наряду с традиционными триггерами, таймстэмпами, могильными камнями и пр. геморроем, можно просто отметить галку Change Tracking в случае SQL Server 2008. Достаточно добавить в проект в Visual Studio компонент Local Database Cache, чтобы в этом убедиться. Можно почитать книжку "Pro Sync Framework" (https://books.google.ru/books?id=g2A6FiSXpHkC&pg=PA198&lpg=PA198#v=onepage&q=&f=false). CDC практически не налагает ограничений на DDL по таблицам, над которыми поднят, за исключением уникального индекса при net changes. В случае change tracking таблица обязана иметь первичный ключ, который нельзя менять (DDL), покуда change tracking над ней включен. Таблица может быть партиционирована, но переключение партиций не работает, если на одной из таблиц включен change tracking. Ну и, наконец, отличие в просмотре. Если хотим увидеть не только в каких записях произошли изменения, но и какие значения поменялись, то в CDC это выдается сразу, а в Change Tracking выдаются только РК изменившихся записей, которые нужно джойнить с оригинальной таблицей.
Как и в случае CDC, Change Tracking не включается над системными базами, в том числе tempdb. Cоздаем базу и в ней табличку для экспериментов. Как уже говорилось, для таблички, над которой будет включен Change Tracking, обязателен первичный ключ.
if exists(select 1 from sys.databases where name = 'ChangeTracking_Test') begin
alter database ChangeTracking_Test set SINGLE_USER with rollback immediate
drop database ChangeTracking_Test
end
create database ChangeTracking_Test
go
use ChangeTracking_Test
create table tbl (fld1 int primary key, fld2 varchar(10))
Скрипт 1
Включаем отслеживание изменений сначала в целом по базе, потом над конкретными таблицами.
alter database ChangeTracking_Test set change_tracking = on
(change_retention = 10 minutes, auto_cleanup = on)
Скрипт 2
alter table tbl enable change_tracking with (track_columns_updated = on)
Скрипт 3
Retention – это период, в течении которого во внутренних таблицах сохраняется история изменений. Задается в минутах, часах или днях. Минимально 1 минута, по умолчанию 2 дня, а сколько максимально, я не проверял. Судя по всему, максимум – это сколько дней укладываются в 4-байтное целое. Другой вопрос – насколько осмысленно она себя поведет, если поставить туда, например, миллион дней. Как правило, длительность change_retention выставляют чуть больше промежутка между периодическими синхронизациями, чтобы старые записи не залеживались. Auto_cleanup – означает очистку устаревших записей, происходит ли она автоматически. Часто спрашивают, в чем состоит смысл этой опции? Задание change_retention само по себе подразумевает, что устаревшие записи должны вычищаться. Это действительно так, и штатное положение auto_cleanup всегда on. Возможность поставить его в off предназначена для нештатных ситуаций, когда, допустим, очередная синхронизация по каким-то причинам не прошла. Если сейчас пойдет автоматическая очистка, изменения будут потеряны. Чтобы этого не произошло, auto_cleanup надо приостановить в off. Ручная чистка изменений наподобие sys.sp_cdc_cleanup_change_table в CDC (см. CDC\Скрипт 15) мне неизвестна. Теоретически можно предположить delete из внутренней таблицы изменений с DACа (см. ниже).
BOL любезно сообщает, что you can change the values (имеется в виду change_retention и auto_cleanup) at any time after change tracking is enabled, но забывает сказать, как именно. Если повторить Скрипт 2 с другими значениями параметров, будет ошибка, что change tracking по этой базе уже включен. Нужно просто сказать
alter database ChangeTracking_Test set change_tracking
(change_retention = 10 days, auto_cleanup = off)
Скрипт 4
Опция track_columns_updated в таблице предназначена для ситуаций, когда требуется отслеживать не только какие записи, но и какие поля в них изменились.
Метаданные Change Tracking. По аналогии с CDC\Скрипт 5 вы можете ожидать увидеть в sys.databases колонку наподобие is_cdc_enabled, а в sys.tables колонку наподобие is_tracked_by_cdc. Ан нет.
select name from sys.all_columns
where object_name(object_id) = 'databases' and object_schema_name(object_id) = 'sys'
order by name
Отдельную схему под свои объекты Change Tracking также не создает. Он создает собственные DMV в sys. Видимо, писала другая команда J
select db_name(database_id), * from sys.change_tracking_databases
select object_name(object_id), * from sys.change_tracking_tables
Скрипт 5
Проделаем какие-нибудь изменения над таблицей:
insert tbl values(1, 'aaa')
update tbl set fld2 = 'bbb' where fld1 = 1
update tbl set fld1 = 3, fld2 = 'ccc' where fld1 = 1
delete tbl
Скрипт 6
Для поддержки Change Tracking для каждой таблицы, над которой оно включено, создается таблица по имени sys.change_tracking_<object_id таблицы, которую она обслуживает>. Это аналог cdc.dbo_Products_CT (см. CDC\Скрипт 7), только она внутренняя. Как и всякую таблицу из sys.internal_tables, просто так ее посмотреть не удастся, поэтому привычным шагом отправляемся в DAC, где пишем:
use ChangeTracking_Test
declare @s varchar(10) = str(object_id('dbo.tbl'))
exec ('select * from sys.change_tracking_' + @s )
Скрипт 7
Рис.1
Здесь все очевидно. Первое поле sys_change_xdes_id - ID транзакции, вносившей изменения; второе sys_change_xdes_id_seq – какой-то внутренний identity, третье sys_change_operation – код операции. Сравниваем со Скриптом 6. Первая операция – insert (725), затем – первый update (728), затем (729) – второй update, который делается как delete и insert (3-я и 4-я строчки) из-за того, что в нем апдейтится поле первичного ключа. Последняя запись (730) – это delete. Поле sys_change_columns – какие поля обновились для операций update, если в Скрипте 3 было оговорено track_columns_updated = on. Она начинает его проставлять, если количество колонок в таблице > 1, не считая колонок primary key. В принципе, понятно. Замена primary key, как мы видели, это не обновление, а удаление и вставка новой записи, а если был действительно update и, кроме primary key, там всего одно поле, то и так понятно, что обновилось оно. sys_change_context – это устанавливаемый приложением контекст изменения, я его проиллюстрирую ниже. Дальше идут колонки первичного ключа, названные по принципу k_<имя поля>_<порядковый номер поля в ключе>.
Публичным аналогом этой таблицы является функция changetable(changes tbl, 0). Второй параметр – это номер версии, начиная с которого она выводит изменения. Номер версии начинается в момент включения Change Tracking с 0 и далее монотонно прирастает. Каждое изменение в таблице увеличивает его на 1. Время изменения не хранится, только версия. При желании его можно посмотреть в sys.syscommittab, она же sys.dm_tran_commit_table. Auto cleanup чистит старые версии. Посмотреть минимальный сохранившийся номер версии и текущую версию можно при помощи функций
select change_tracking_min_valid_version(object_id('dbo.tbl'))
select change_tracking_current_version()
Скрипт 8
Их также можно взять из sys.change_tracking_tables (Скрипт 5).
В нагруженных системах в промежутке между получением текущей версии и вызовом changetable() другие сессии могут успеть насовать в tbl дополнительных изменений. Может, например, некстати сработать autocleanup, который удалит устаревшие изменения и увеличит change_tracking_min_valid_version. В таких случаях рекомендуется использовать snapshot isolation, чтобы гарантировать целостность изменений.
Рис.2
Можно указать номер версии, меньший реально существующего минимального; ошибки не будет.
select * from changetable(changes tbl, 0) t_ch
Скрипт 9
Рис.3
Поля в выдаче changetable, в принципе, и так понятны, при желании можно почитать в документации - https://msdn.microsoft.com/ru-ru/library/bb934145.aspx.
По каждому РК получается не совокупная картина, как в случае net changes в CDC (см. CDC\Скрипт 14), а тупо последняя операция. За обозримый отрезок в таблице tbl побывали записи со значением РК 1 и 3, обе были удалены, что мы и видим на рис.3. Нужно сравнивать SYS_CHANGE_VERSION с SYS_CHANGE_CREATE_VERSION и смотреть, что если мы, допустим, синхронизуемся с какой-то копией версии 0, то эти записи вообще можно не принимать во внимание, потому что у них SYS_CHANGE_CREATE_VERSION > 0, то есть они были созданы позднее, и тут же удалены (SYS_CHANGE_OPERATION = D) в пределах периода между двумя синхронизациями.
Теперь по аналогии с постом CDC и DDL посмотрим, как в Change Tracking отображаются изменения в схеме.
alter table tbl add fld3 varchar(10)
Скрипт 10
Изменения в метаданных Change Tracking не ловит - Change tracking does not track schema changes. Результат Скрипта 9 после добавления в таблицу колонки остался, как на рис.3. Однако, в отличие от CDC нам не надо совершать никаких дополнительных телодвижений, чтобы отлавливать изменения в новой колонке, если в Скрипте 3 было сказано track_columns_updated = on. Делаем изменения:
insert tbl values(1, 'aaa', 'xxx')
update tbl set fld3 = 'yyy' where fld1 = 1
Скрипт 11
Повторяем Скрипт 9 и видим следующую картину:
Рис.4
Строка для РК = 1 изменила версию на 6, версию создания на 5 и операцию на I. То есть если где-то вдалеке существует копия таблицы tbl, то, чтобы поддержать ее в синхронном состоянии, в нее требуется вставить из оригинала запись с РК = 1. А как же update в Скрипте 11, спросите вы? Смотрите, второй параметр функции changetable в Скрипте 9 обозначает номер версии, по отношению к которой мы синхронизуемся. В данном случае он равен 0, то есть считаем, какие изменения нужно внести в копию от начала времен, когда записи с РК = 1 в таблице tbl еще не было. Разумеется, ее нужно вставить. Никакие апдейты этой записи в ее предыстории нас не волнуют, потому что мы просто берем и вставляем ее в том виде, в котором она есть на сейчас. Теперь предположим, что копия таблицы tbl синхронизирована с ней по версию 5, когда запись с РК = 1 уже появилась (см. SYS_CHANGE_CREATE_VERSION). То, что копия находится в версии 5, означает, что эта запись в нее добавлена. Какие изменения требуется произвести с копией, чтобы синхронизовать ее до текущего состояния от версии 5? Пишем:
select * from changetable(changes tbl, 5) t_ch
Скрипт 12
Рис.5
и видим, что для этого требуется произвести апдейт 3-го поля записи. Вон там, кстати, и SYS_CHANGE_COLUMNS появился. Это потому, что теперь в таблице tbl стало > 1 поля, кроме РК-полей. Раньше не имело смысла.
Как и в случае CDC, в Change Tracking предусмотрены функции для ленивых. Чтобы не разбирать руками вектор в SYS_CHANGE_COLUMNS, можно воспользоваться функцией change_tracking_is_column_in_mask(column_id, вектор), чтобы понять, изменилась ли интересующая колонка:
select SYS_CHANGE_VERSION, SYS_CHANGE_COLUMNS, fld1,
change_tracking_is_column_in_mask(columnproperty(object_id('tbl'), 'fld2', 'ColumnId'), SYS_CHANGE_COLUMNS),
change_tracking_is_column_in_mask(columnproperty(object_id('tbl'), 'fld3', 'ColumnId'), SYS_CHANGE_COLUMNS)
from changetable(changes tbl, 5) t_ch
Скрипт 13
Рис.6
Как упоминалось выше, одним из отличий Change Tracking от CDC является то, что Change Tracking не хранит значений изменений в своей таблице изменений. Только РК изменившейся записи, что с ней произошло и (если релевантно) какие поля поменялись. Чтобы донести изменения в синхронизуемую копию, надо делать джойн changetable по РК c оригинальной таблицей:
select * from tbl join changetable(changes tbl, 0) ct on tbl.fld1 = ct.fld1
Скрипт 14
До сих пор мы использовали функцию changetable() со словом changes. Вместо него там может стоять еще слово version. Тогда функция возвращает номер версии текущей записи. В первых скобочках нужно перечислить через запятую поля, составляющие таблицын РК, а во вторых – их значения, чтобы идентифицировать запись.
select * from changetable(version tbl, (fld1), (1)) ct
Скрипт 15
Рис.7
Вот запрос, который возвращает версии всех строк в таблице. Он может пригодиться перед начальным снэпшотом синхронизации, чтобы понять, от какой версии будет синхронизоваться каждая запись.
insert tbl (fld1) values (2)
insert tbl (fld1) values (3)
select * from tbl cross apply changetable(version tbl, (fld1), (tbl.fld1)) ct
Скрипт 16
Рис.8
В завершение – контекст. Конекст – это нечто сродни именованной транзакции или описания на таблицу, который может ассоциироваться с изменением в целях пояснения или улучшения читабельности.
declare @context AS varbinary(128);
set @context = cast('Неважно, что я здесь сейчас напишу, например, это какое-то важное изменение' as varbinary(128));
with change_tracking_context(@context) update tbl set fld2 = 'bbb' where fld1 = 2
select SYS_CHANGE_VERSION, SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION,
cast(SYS_CHANGE_CONTEXT as varchar(100)), fld1 from changetable(changes tbl, 0) t_ch
Скрипт 17
Рис.9
Из служебных таблиц Change Tracking имеется еще одна - sys.syscommittab, одна на базу. Она отличается тем, что на нее существует DMV sys.dm_tran_commit_table, которое можно смотреть из пользовательской сессии. Ее устройство описано здесь: https://msdn.microsoft.com/ru-ru/library/cc645959.aspx.
Рис.10
Отключение Change Tracking:
alter table tbl disable change_tracking
alter database ChangeTracking_Test set change_tracking = off
Скрипт 18
В отличие от CDC (см. CDC\Скрипт 16, 17) нельзя отключить Change Tracking в базе, пока он включен хотя бы у одной из ее таблиц.
Я забыл сказать, что еще одним отличием от CDC является возможность сконфигурить Change Tracking через SSMS. В Object Explorer'e у базы и у таблицы в Properties есть пункт Change Tracking.