01. CDC: Configuración y Mecanismos

CDC registra la actividad DML (Insert, Update, Delete) leyendo el log de transacciones. Es una solución de bajo impacto arquitectónico.

  • Activación DB/Tablas: Se activa en la DB y luego por tablas específicas. Esto genera automáticamente el esquema cdc y las tablas de cambio.
  • Automatización de Jobs: El proceso crea 2 Jobs en el SQL Agent para la DB: uno de captura (Capture) y otro de limpieza (Cleanup).

Proceso de Limpieza (Cleanup)

Retención: Configurable con un mínimo de 1 minuto y un máximo de 100 años.

Umbral de Borrado: Procesa un máximo de 5,000 filas por transacción. Esto evita saturar el log de transacciones.

02. Análisis Técnico de Scripts

Habilitación de CDC y Tabla con todas las columnas 
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table 
    @source_schema = N'dbo', 
    @source_name   = N'Clientes', 
    @role_name     = N'cdc_admin', 
    @supports_net_changes = 1;

Explicación Técnica:

sp_cdc_enable_db marca la base de datos en los metadatos de SQL Server. Al no definir @captured_column_list, SQL Server mapea todas las columnas. @supports_net_changes genera funciones adicionales para consultar el resultado neto.

Configuración de Columnas Específicas 
EXEC sys.sp_cdc_enable_table 
    @source_schema = N'dbo', 
    @source_name   = N'Ventas', 
    @captured_column_list = N'ID_Venta, Monto, Fecha',
    @role_name     = NULL;

Explicación Técnica:

Este script permite la optimización de almacenamiento. Al usar @captured_column_list, el Job de captura ignora cambios en columnas no listadas, reduciendo el I/O y el crecimiento del archivo de datos (.mdf).

03. Trigger de Captura de Usuario

CDC extrae datos del Log y no conoce al usuario aplicativo. Esta solución inyecta el usuario en la tabla origen para que CDC lo capture.

Script del Trigger 
ALTER TABLE Clientes ADD UsuarioAudit NVARCHAR(100);
GO

CREATE TRIGGER tr_Clientes_AuditUser
ON Clientes FOR INSERT, UPDATE, DELETE AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @User NVARCHAR(100) = 
        CAST(SESSION_CONTEXT(N'user_id') AS NVARCHAR(100));

    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        UPDATE c SET UsuarioAudit = @User
        FROM Clientes c INNER JOIN inserted i ON c.ID = i.ID;
    END
END;

El Desafío del DELETE

En un borrado, la fila desaparece. CDC captura el estado de la fila pre-borrado. Para que el usuario aparezca en el log de un DELETE, el usuario debió haber sido persistido previamente.

04. Consulta de Históricos

Para extraer los datos, SQL Server genera funciones TVF (Table-Valued Functions) automáticamente para cada tabla habilitada.

Consulta de Cambios por Rango 
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Clientes');
SET @to_lsn   = sys.fn_cdc_get_max_lsn();

SELECT 
    CASE __$operation 
        WHEN 1 THEN 'Eliminado' 
        WHEN 2 THEN 'Insertado'
        WHEN 3 THEN 'Antes de Update'
        WHEN 4 THEN 'Después de Update' 
    END AS TipoOperacion,
    * FROM cdc.fn_cdc_get_all_changes_dbo_Clientes(@from_lsn, @to_lsn, 'all');

Análisis de la Operación:

La columna __$operation identifica la acción DML. La función devuelve todas las filas de cambio capturadas entre los LSN especificados.

05. Mapeo LSN y Máscara de Update

Consulta por Tiempo

Para consultar por fechas humanas en lugar de LSN binarios.

Conversión Fecha a LSN 
DECLARE @begin_time datetime = '2023-10-01 08:00:00';
DECLARE @end_time datetime = GETDATE();
DECLARE @from_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
DECLARE @to_lsn binary(10) = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Clientes(@from_lsn, @to_lsn, 'all');

La Máscara de Cambio (__$update_mask)

Identifica qué columnas cambiaron mediante un mapa de bits.

  • Permite detectar cambios en columnas específicas sin comparaciones manuales.
  • Se decodifica con sys.fn_cdc_is_bit_set.

06. Ventajas y Desventajas

Ventajas

  • Rendimiento: Proceso asíncrono.
  • Mantenimiento: Automático.
  • Esquema: Robusto ante cambios.

Desventajas

  • Crecimiento: Impacto en el espacio .mdf.
  • LOBs: Trunca tipos varchar(max).
  • Seguridad: Datos sensibles.