Использование Change Data Capture (CDC) in SQL Server 2008.

Пару дней назад на работе возникала необходимость в одном из проектов отслеживания изменений в базе данных. Задача такая: есть насколько таблиц, которые нужно мониторить на предмет операций Insert, update и delete. Сначала, решили исползовать аудит, но он не давал нам необходимой информации, такой как с какого на какое значение быль выполнен update или что именно было удалено и т.п. Поискав решения вспомнил о SQL Server СDC (Change Data Capture). Никогда раньше не использовал эту службу и вот что нарыл:

use SRPV
go

Активация CDC-функционала для конкретной  базы данных

exec sys.sp_cdc_enable_db

Ппроверяем что значение поля is_cdc_enabled в sys.databases изменилось для БД SRPV. Это признак того что для указанной БД активирована служба CDC.

select name, is_cdc_enabled from sys.databases

Активируем CDC для конкретной таблицы (в данном примере это таблица WaggonsSend). Будет созданно 2 job-а. cdc.test_new_capture и cdc.test_new_cleanup
Для добавления таблицы указываем ее имя в @source_name.

exec sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'WaggonsSend' ,
    @role_name = 'CDC_Admins',
    @supports_net_changes = 1

Проверяем  таблица установлена источником СDC
 
select name, type, type_desc, is_tracked_by_cdc, * from sys.tables
where name in ('dbo_WaggonsSend')

Просмотр всех объектов созданных для обеспечения функционирования CDC.

select o.name, o.type, o.type_desc, s.name from sys.objects o
join sys.schemas  s on s.schema_id = o.schema_id
where s.name = 'cdc'

Просмотр изменений в таблице можно просмотреть так:

use SRPV
DECLARE @begin_lsn binary(10), @end_lsn binary(10)
SET @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_WaggonsSend')
SET @end_lsn   = sys.fn_cdc_get_max_lsn()
select tm.tran_begin_time,
        tm.tran_end_time,
        tm.tran_id,
    CASE __$operation
     when 1 then  'delete'
      when 2 then  'insert'
     when 3 then  'update (before the update)'
     when 4 then  'update (after the update)'
    end operation,
    ch.*
from cdc.fn_cdc_get_all_changes_dbo_WaggonsSend(@begin_lsn, @end_lsn, 'all update old') ch
left join cdc.lsn_time_mapping tm on ch.__$start_lsn=tm.start_lsn

Просмотр джобов отвечающих за работу CDC (здесь можно просмотреть такие параметры как
pllinginterval - с какой периодичностью в сек. движок будет сканировать транзакшн лог и заполнять хранилище информации
retention - количество минут сколько будет храниться инфа в хранилище. Отчисткой занимается джоб cdc.test_new_cleanup, запускаемый 1 разв неделю)

use msdb
select * from dbo.cdc_jobs

Редактирование этих параметроов

EXEC [sys].[sp_cdc_change_job]
    @job_type = N'capture',
    @pollinginterval = 5  --раз в пять секунд движок CDC будет сканировать транзакшн лог и пополнять хранилище информацией. Делается это джобом cdc.test_new_capture, который запущен всегда, т.е. один раз запущен и находится в статусе Running постоянно.

EXEC [sys].[sp_cdc_change_job]
    @job_type = N'cleanup',
    @retention = 10080 --  10080 минут - это 7 дней. Именно столько я установил для хранения истории в хранилище.


Просмотр всех таблиц на которых сконфигурированна CDC в текущей БД
EXECUTE sys.sp_cdc_help_change_data_capture;


Хранилище это системные таблицы, созданные в процессе конфигурирования CDC. Информацию из можно селектить как с помощью хп cdc.fn_cdc_get_all_changes_dbo_WaggonsSend (синтаксис я указывал выше) так и напрямую

 select * from cdc.dbo_WaggonsSend_CT
where bla-bla-bla

А здесь подробнейшая информация на эту тему =))