SQL Server - Cómo monitorear y auditar cambios de datos en tablas usando Change Data Capture (CDC)

SELECT [name], is_cdc_enabled 
FROM sys.databases

Ejemplo:

SELECT [name], is_tracked_by_cdc
FROM sys.tables

Ejemplo:

Ejemplo de uso:

USE [dirceuresende] 
GO

EXEC sys.sp_cdc_enable_db 
GO

Resultado:

Una vez que CDC se haya activado en la instancia, puede verificar que se creará un esquema “cdc” en la base de datos en cuestión:

Además, algunas tablas del sistema se crearon utilizando el esquema "cdc":

Las tablas creadas por CDC son:

• cdc.captured_columns: Esta tabla del sistema enumerará todas las columnas de las tablas que tienen CDC habilitado. Esta información también se puede consultar utilizando el SP del sistema sys.sp_cdc_get_source_columns.

  

  Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-captured-columns-transact-sql

• cdc.change_tables: Esta tabla del sistema enumerará todas las tablas que tienen CDC habilitado. Esta información también se puede consultar utilizando el SP del sistema sys.sp_cdc_help_change_data_capture.

  

  Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-change-tables-transact-sql

• cdc.ddl_historia: Esta tabla del sistema almacenará todos los cambios de DDL realizados en las tablas que tienen CDC habilitado. Esta información también se puede consultar utilizando el SP del sistema sys.sp_cdc_get_ddl_history.

  

  Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-ddl-history-transact-sql

• cdc.index_columns: Esta tabla almacena información sobre los índices asociados con tablas que tienen CDC habilitado. Esta información también se puede consultar utilizando el SP del sistema sys.sp_cdc_help_change_data_capture.

  

  Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-index-columns-transact-sql

• cdc.lsn_time_mapping: Devuelve una fila por cada transacción en la tabla con CDC habilitado. Esta tabla se utiliza para asignar los valores confirmados entre el número de secuencia de registro (LSN) y el momento en que se confirma la transacción. Esta información también se puede recuperar con las funciones sys.fn_cdc_map_lsn_to_time y sys.fn_cdc_map_time_to_lsn.

  

  Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-lsn-time-mapping-transact-sql

Ejemplo de uso:

USE [dirceuresende]
GO 

EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'Clientes', 
@role_name     = NULL 
GO

Resultado:

Después de ejecutar el SP anterior, se habilitó el monitoreo de captura de datos modificados (CDC) en la tabla deseada. Con esto se creará una nueva tabla, en el formato cdc.schema_tabela_CT, con las mismas columnas que la tabla original y algunas tablas de metadatos más para controlar el cambio.

Si desea monitorear los cambios en columnas específicas y no en todas las columnas de la tabla, puede usar esta sintaxis:

USE [dirceuresende]
GO 

EXEC sys.sp_cdc_enable_table 
@source_schema = N'dbo', 
@source_name   = N'Clientes', 
@role_name     = NULL,
@captured_column_list = '[Id], [Nome], [Teste]'
GO

Ahora, hagamos algunos cambios en la tabla para ver cómo se comporta CDC.

Entrada de datos

Actualización de datos

Eliminación de datos

Truncar datos de la tabla
Como puede ver en la captura de pantalla a continuación, una vez que CDC se activa en una tabla, no podrá truncar los datos en una tabla.

Cambiar la estructura de la tabla

Como puedes ver, la columna __$operación nos permite identificar el tipo de operación realizada sobre la mesa, en la cual los valores posibles son:

• 1: BORRAR
• 2: INSERTAR
• 3: Valor ANTES DE ACTUALIZAR
• 4: Valor DESPUÉS DE LA ACTUALIZACIÓN

Recuerde habilitar CDC en el nivel de la base de datos antes de intentar habilitar CDC en una tabla. Si no hace esto, encontrará este mensaje de error:

Y estos 2 trabajos tienen el siguiente propósito:

• cdc.dirceuresende_capture: Trabajo que se ejecuta cada vez que el Agente SQL Server inicia y ejecuta el SP del sistema sys.sp_MScdc_capture_job, que a su vez ejecuta el SP sys.sp_cdc_scan, comenzando a monitorear la mesa.

  Referencia: https://technet.microsoft.com/en-us/library/cc645591(v=sql.105).aspx

• cdc.dirceuresende_cleanup: Trabajo que se ejecuta diariamente a las 02:00 y tiene como finalidad controlar el tamaño de las tablas de control CDC, para evitar que crezcan descontroladamente. Este trabajo ejecuta el sistema SP sys.sp_MScdc_cleanup_job,
  que a su vez ejecuta el SP sys.sp_cdc_cleanup_job_internal.

  Referencia: https://technet.microsoft.com/en-us/library/cc645885(v=sql.105).aspx

A esta pregunta, la respuesta es NO. De forma predeterminada, los datos crecerán hasta alcanzar el límite predeterminado de SQL Server (4320 minutos = 3 días), lo que podría ser mucho o poco dependiendo de sus necesidades.

Si desea cambiar el tiempo de retención, puede utilizar el procedimiento almacenado sys.sp_cdc_change_job:

EXEC sp_cdc_change_job 
    @job_type='cleanup', 
    @retention=10080 -- 7 dias (quantidade de minutos de retenção)

Referencia: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql

El valor máximo para este parámetro de retención es 52494800 minutos (100 años), pero si desea deshabilitar este límite, simplemente deshabilite el trabajo de limpieza (cdc.dirceuresende_cleanup)

Para ver los parámetros de retención actuales, utilice la siguiente consulta:

SELECT 
    [retention],
    ([retention]) / ((60 * 24)) AS RetentionInDays,
    *
FROM
    msdb.dbo.cdc_jobs;

Resultado:

Ejemplo:

USE [dirceuresende]
GO

EXEC sys.sp_cdc_disable_db
GO

Resultado:

Vale la pena recordar que cuando deshabilita CDC en el nivel de la base de datos, TODOS los monitoreos CDC activos en el nivel de la tabla también se deshabilitarán y todos los datos históricos también se perderán (y NO se le cambiará acerca de la existencia de estos monitoreos activos en el nivel de la tabla).

Vale la pena recordar que es posible deshabilitar CDC a nivel de base de datos, incluso si hay un monitoreo activo a nivel de tabla (y NO se le alertará sobre la existencia de este). Al final de este tema dejé algunas advertencias sobre lo que sucede cuando haces esto. ¡Lee hasta el final!

Identificación del nombre de la instancia de captura de CDC:

USE [dirceuresende]
GO

EXEC sys.sp_cdc_help_change_data_capture
GO

SELECT OBJECT_NAME([object_id]), OBJECT_NAME(source_object_id), capture_instance
FROM cdc.change_tables

Resultado:

Una vez que hemos identificado el nombre de la instancia (dbo_Clientes), ya podemos ejecutar el sys.sp_cdc_disable_table para desactivar efectivamente CDC en esta tabla:

USE [dirceuresende]
GO

EXEC sys.sp_cdc_disable_table
    @source_schema = 'dbo', -- sysname
    @source_name = 'Clientes', -- sysname
    @capture_instance = 'dbo_Clientes' -- sysname

Resultado:

Después de deshabilitar CDC en la tabla, puede ver que la tabla de monitoreo se eliminó automáticamente. TEN MUCHO CUIDADO con esto, para no perder los valores registrados y perder tu historial. Si desea deshabilitar CDC pero no tiene intención de perder el historial, copie los datos de la tabla de historial a otra tabla antes de deshabilitar CDC en la tabla.

Vale la pena recordar que al desactivar CDC a nivel de base de datos, TODOS los monitoreos de CDC activos a nivel de tabla también se desactivarán y también se perderán todos los datos históricos.

Restaurando la misma base de datos, en la misma instancia
En esta situación, la restauración se realizará normalmente y el CDC seguirá activo y funcionando después de restaurar la base. Nada cambia.

Restaurar la copia de seguridad en la misma instancia pero con un nombre de base de datos diferente o en otra instancia
En ambos casos, CDC se desactivará y la información de metadatos grabada se perderá, lo que sería muy malo. Para evitar que esto suceda, debe utilizar el parámetro keep_cdc en el comando de restauración.

Ejemplo:

RESTORE DATABASE 
    [dirceuresende]
FROM 
    DISK = 'C:\Backups\dirceuresende.bak' 
WITH 
    MOVE 'dirceuresende_dados' TO 'C:\Dados\dirceuresende_dados.mdf',
    MOVE 'dirceuresende_log' TO 'C:\Dados\dirceuresende_log.ldf', 
    KEEP_CDC

Después de la restauración, deberá ejecutar los siguientes comandos para recrear los trabajos de CDC:

USE [dirceuresende]
GO

exec sys.sp_cdc_add_job 'capture'
GO

exec sys.sp_cdc_add_job 'cleanup'
GO