CDC в SQL Server 2008 R2: инкрементальная загрузка вместо ночного дампа

До определённого момента схема «ночью грузим всё» работает нормально. Берём таблицы источника, делаем полный SELECT, кидаем в staging, трансформируем, грузим в хранилище. Просто, предсказуемо, и SSIS с этим справляется без вопросов. Проблема появляется, когда таблицы источника становятся большими, окно загрузки сужается, а бизнес начинает спрашивать «почему данные только к утру?».

У одного из наших клиентов всё это случилось одновременно. Операционная база на SQL Server 2008 R2 - несколько таблиц по несколько миллионов строк, активная запись в течение дня. Полный дамп занимал часа три, запускался в полночь, к семи утра хранилище было актуальным. Но продажи шли и в 22:00, и в 23:30 - и аналитики хотели видеть эти данные раньше, чем через восемь часов.

Почему не просто поднять частоту полного дампа

Очевидный вариант - запускать загрузку не раз в ночь, а каждые два часа. Проверили: при текущем объёме данных полный дамп каждые два часа создавал такую нагрузку на источник, что операционная система начинала тормозить. Компрометировать операционную базу ради хранилища - плохая идея. Нужна инкрементальная загрузка.

Первое, что приходит в голову - timestamp-колонка. Добавить в таблицу modified_at, индекс на неё, и грузить только строки, изменённые с последнего запуска. Это работает для INSERT и UPDATE, но не ловит DELETE. А у клиента были физические удаления строк в ряде таблиц - мягкое удаление там не использовалось.

Вот тут и вспомнили про Change Data Capture.

Что такое CDC в SQL Server 2008 R2

CDC появился в SQL Server 2008 и к версии 2008 R2 подрос до вполне рабочего состояния. Механика простая: SQL Server читает транзакционный лог и выкладывает изменения (INSERT, UPDATE, DELETE) в специальные системные таблицы-журналы. Для каждой отслеживаемой таблицы создаётся своя capture-таблица с теми же колонками плюс несколько служебных: __$operation (1-DELETE, 2-INSERT, 3-UPDATE before, 4-UPDATE after), __$start_lsn, __$end_lsn.

Включается CDC в два шага. Сначала на уровне базы:

USE OperationalDB;
EXEC sys.sp_cdc_enable_db;

Потом на уровне каждой таблицы:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Orders',
    @role_name     = NULL;

После этого SQL Server Agent запускает два job-а: cdc.OperationalDB_capture (читает лог и наполняет capture-таблицы) и cdc.OperationalDB_cleanup (чистит старые записи по retention). Оба создаются автоматически.

Важный момент: CDC требует SQL Server Agent. Если Agent не запущен - данные в capture-таблицах накапливаться не будут. Это не очевидно с первого раза, и мы поплатились двумя часами отладки, пока не вспомнили.

SSIS-пакет для инкрементальной загрузки

Логика SSIS-пакета, который загружает изменения, выглядит так:

1. Читаем из таблицы состояния последний обработанный LSN (Log Sequence Number).
2. Вызываем cdc.fn_cdc_get_all_changes_dbo_Orders с диапазоном от последнего LSN до текущего.
3. Разделяем поток: INSERT/UPDATE идут в MERGE на целевой таблице хранилища, DELETE - в отдельный обработчик.
4. Обновляем LSN в таблице состояния.

Для разделения потока по типу операции используем Conditional Split с условием [__$operation] == 1 для DELETE и [__$operation] == 2 || [__$operation] == 4 для INSERT/UPDATE. Строки с __$operation == 3 (before-image UPDATE) нас не интересуют - берём только after-image.

CDC в SSIS 2008 R2 поддерживается на уровне Data Flow: есть отдельный компонент CDC Source, который берёт на себя работу с LSN и функциями-журналами. Мы его использовали вместо того, чтобы писать SQL-запросы руками - он немного упрощает логику состояния.

Расписание и практика

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

Несколько вещей, которые выяснились в процессе:

Retention CDC по умолчанию - 3 дня. Если пакет по какой-то причине не запускался трое суток, при следующем запуске обнаружите, что часть записей из capture-таблиц уже почищена. В этом случае придётся делать полный перегруз за этот период. Retention настраивается через @retention при включении CDC или через sys.sp_cdc_change_job.

Транзакционный лог растёт. CDC держит лог от усечения до тех пор, пока capture job не прочитает все изменения. Если capture job застрял или отстал - лог будет расти. Мониторинг размера лога и состояния capture job обязателен.

Первичный ключ должен быть. CDC умеет работать без PK, но тогда отслеживаются только указанные колонки. Для полноценного MERGE на целевой таблице PK нужен.

Что получилось

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

Это не стриминг в строгом смысле - между захватом изменения и появлением его в хранилище проходит до 15 минут плюс время обработки пакета. Но для большинства аналитических задач этого достаточно.

Если смотрите в сторону построения хранилища данных на платформе SQL Server - CDC это работающий механизм, который не требует сторонних инструментов и ложится поверх существующей инфраструктуры. Главное - не забыть про мониторинг Agent и размер лога.