Cómo usar Change Data Capture en SQL Server — paso a paso

Change Data Capture en SQL Server permite registrar cambios en una tabla (insert/update/delete) de forma eficiente, útil para cargas incrementales y auditoría. Esta guía muestra cómo activar CDC, leer los cambios con las funciones fn_cdc y gestionar la retención y la desactivación.

Requisitos previos

• SQL Server 2016 SP1 o superior (CDC disponible en esta y versiones más recientes).
• Permiso de sysadmin o db_owner en la base de datos destino.
• SQL Server Agent en ejecución (CDC utiliza jobs del Agent).
• Una tabla con clave primaria (el ejemplo usa dbo.Products).

Paso 1: Activar CDC en la base de datos

Primero, activar CDC a nivel de base de datos. Esto crea las tablas de control y permite que las instancias de captura queden registradas.

USE YourDatabaseName;
GO
EXEC sys.sp_cdc_enable_db;
GO

Paso 2: Crear tabla de ejemplo (si es necesario) y activar CDC en la tabla

La tabla necesita una clave primaria. Si ya tiene la tabla, salte a la activación. Después, registre la tabla para la captura.

-- Ejemplo de creación de tabla
CREATE TABLE dbo.Products (
  ProductID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(100),
  Price DECIMAL(10,2)
);
GO

-- Activar CDC en la tabla
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Products',
  @role_name     = NULL; -- NULL permite lectura a cualquier usuario con permisos
GO

Paso 3: Generar cambios en la tabla (insert / update / delete)

Inserte algunas filas y realice updates/deletes para que existan cambios que deban ser capturados.

INSERT INTO dbo.Products (Name, Price) VALUES ('Caneta', 1.20);
INSERT INTO dbo.Products (Name, Price) VALUES ('Caderno', 3.45);

UPDATE dbo.Products SET Price = 1.50 WHERE Name = 'Caneta';
DELETE FROM dbo.Products WHERE Name = 'Caderno';
GO

Paso 4: Consultar los cambios con fn_cdc_get_all_changes

Para leer cambios utilice las funciones del sistema de CDC. Obtenga las LSNs mínima y máxima y consulte cdc.fn_cdc_get_all_changes_. La capture_instance por defecto es schema_tablename (ej.: dbo_Products).

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Products');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT 
  __$start_lsn,
  __$seqval,
  CASE __$operation
    WHEN 1 THEN 'delete'
    WHEN 2 THEN 'insert'
    WHEN 3 THEN 'update_before'
    WHEN 4 THEN 'update_after'
  END AS operation,
  ProductID, Name, Price
FROM cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn, 'all');
GO

Interpretación rápida: __$operation = 1 (delete), 2 (insert), 3 (update antes), 4 (update después). Use la fila _before_ y _after_ para detectar cambios en los valores.

Paso 5: Gestionar la retención y los jobs de CDC

CDC crea jobs del SQL Server Agent para captura y limpieza. La retención por defecto es 3 días (en minutos: 4320). Puede ajustar el job de cleanup con sp_cdc_change_job.

-- Cambiar retención a 7 días (10080 minutos)
EXEC sys.sp_cdc_change_job
  @job_type = N'cleanup',
  @retention = 10080;
GO

Compruebe los jobs asociados con sp_help_job o en el SQL Server Agent. Para desactivar la captura en una tabla o en la base de datos use:

-- Desactivar CDC en la tabla
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'Products', @capture_instance = N'dbo_Products';
GO

-- Desactivar CDC en la base de datos
EXEC sys.sp_cdc_disable_db;
GO

Verificar el resultado

Confirme que las change tables existen y que hay registros:

-- Ver change tables activas en la base de datos
SELECT * FROM cdc.change_tables;

-- Leer datos capturados (ejemplo ya dado en el Paso 4)
-- Si obtiene filas con __$operation puede verificar los cambios realizados.

Si no aparecen cambios verifique: SQL Server Agent en ejecución, permisos (sysadmin/db_owner), y que la tabla tiene clave primaria. Error común: "The specified table does not have a primary key" — añada PK a la tabla antes de activar CDC.

Conclusión

Después de activar y probar Change Data Capture en SQL Server, puede usar los datos para cargas incrementales en ETL, auditoría o replicación personalizada. Siguientes pasos sugeridos: integrar la lectura CDC en un proceso SSIS/ADF, o usar la función fn_cdc_get_net_changes para obtener solo el estado final. Consejo: compruebe y ajuste la retención según el volumen de cambios para evitar un crecimiento excesivo de las change tables — ¿quiere probar con un volumen de prueba mayor?