Отслеживание изменений в таблицах баз MS SQL Server с помощью Change Data Capture (CDC)

Появилась задача отследить изменения определенных таблиц. Включение Recovery model в режим Full и восстановление по каждой минуте из журналов транзакций является слишком долгим занятием, неудобным. Коллега предложил рассмотреть варианты:


1) Change tracking (CT)
2) SQL Server Audit
3) SQL Server Profiler
4) Change Data Capture (CDC)
Теперь кратко про каждый.
1) CT синхронен (триггер - это процедура, а процедура - это дополнительное время выполнения), поэтому может существенно повлиять на производительность. К тому же он хранит только измененные строки, а мне нужно хранить всю историю, т.е. и добавление, и удаление.
2) SQL Server Audit - мощное средство, но для моего конкретного случая слишком навороченное; мне нужно что-то простое, совсем простое. Собирать информацию в файл, затем считывать ее оттуда мне показалось не совсем удобно; к тому же нужно иметь достаточно места для хранения растущих файлов - увеличение размера требует тестирования, т.к. с местом на диске проблемы.
3)  SQL Server Profiler - эта штука больше подходит для анализа производительности. Для сбора данных не совсем удобно, к тому же сам Profiler нагружает сервер. И те же минусы с местом на диске.
4) CDC - асинхронная запись, и не из таблиц, а из лога транзакций - отлично; значит, на производительность влиять не должно. Запись происходит в новые таблицы. А как быстро эти новые таблицы разрастутся? Проведем небольшой тест.
Смотрим размер базы до начала действий:

-- Выбор базы
USE UT11Demo
-- Включение CDC в базе
EXECUTE SYS.sp_cdc_enable_db
--Включение CDC для таблиц
EXECUTE SYS.sp_cdc_enable_table
@SOURCE_SCHEMA = N'DBO'
,@SOURCE_NAME = N'_DocumentChngR15385' --Документ.ЗаказКлиента.Изменения
,@ROLE_NAME = N'cdc_Admin'
EXECUTE SYS.sp_cdc_enable_table
@SOURCE_SCHEMA = N'DBO'
,@SOURCE_NAME = N'_DocumentChngR16174' --Документ.ЗаказПоставщику.Изменения
,@ROLE_NAME = N'cdc_Admin'

Настройка дней хранения
DECLARE @DBNAME NVARCHAR(50) = 'UT11Demo' --Имя базы
--Смотрим задания
select t.*
from msdb.dbo.cdc_jobs t

DECLARE @DAYS INT = 30 -- Количество дней хранения
DECLARE @HOUR INT = 60*24
DECLARE @MINITES INT = @HOUR*@DAYS
DECLARE @JOBID NVARCHAR(50)  -- ID задания из cdc_jobs , пример 'A1E876DD-BBE8-436F-B239-A8842FDB2999'
SET @JOBID = (SELECT TOP 1 T.job_id FROM msdb.dbo.CDC_JOBS T WHERE T.DATABASE_ID = DB_ID(@DBNAME) AND T.JOB_TYPE = 'cleanup')
UPDATE MSDB.DBO.cdc_jobs
SET retention = @MINITES
WHERE job_id = @JOBID
--Смотрим задания
select t.*
from msdb.dbo.cdc_jobs t

Вносим правки в таблицы: добавляем, редактируем, удаляем. Было выполнено суммарно более 37000 изменений, т.е. это суммарно более 37000 записей в новых таблицах CDC.

Смотрим изменения за период; убеждаемся, что механизм работает.
DECLARE @BeginTime DATETIME = '2018-06-17T01:00:00.000'
DECLARE @EndTime DATETIME = '2018-06-17T19:00:00.000'
DECLARE @Begin_lsn binary(10)
DECLARE @End_lsn binary(10)
SELECT @Begin_lsn = SYS.fn_cdc_map_time_to_lsn('smallest greater than', @BeginTime)
SELECT @End_lsn = SYS.fn_cdc_map_time_to_lsn('largest less than or equal', @EndTime)

SELECT
TOP 1000
M.tran_begin_time
,M.tran_end_time
,CASE T.__$operation
WHEN 1 THEN 'Удаление'
WHEN 2 THEN 'Вставка'
WHEN 3 THEN 'До редактирования'
WHEN 4 THEN 'После редактирования'
END AS Operatsia
,T.*
FROM CDC.DBO__DocumentChngR26258_CT T
LEFT JOIN CDC.lsn_time_mapping M ON M.start_lsn = T.__$start_lsn
--WHERE T.__$start_lsn >= @Begin_lsn
ORDER BY M.tran_begin_time

--SELECT TOP 100 O.* FROM cdc.change_tables O
--WHERE O.capture_instance LIKE '%_DocumentChngR15385%'

Снова смотрим размер базы: размер не увеличился ни в одном из файлов. И хотя это неточный тест, так, "на глаз", количество строк мало, но все же... Это количество примерно сопоставимо с количеством модификаций за день в рабочей базе (в рабочей даже меньше), поэтому такой тест можно считать корректным.
Учитывая, что механизм CDC асинхронен и на данном тесте не потребовалось дополнительного дискового места при малом количестве строк, этот вариант наиболее подходящий.