Change Data Capture. Histórico de cambios en SQL Server 2008 R2

SQL Server posee un mecanismo propio de traza de cambios DML y DDL que permite consultar versiones anteriores de una misma tabla, ver las modificaciones que ha sufrido durante un determinado espacio de tiempo, o un amplio espectro de modificaciones DDL de la propia tabla.

Para ello no es  necesario crear ningún mecanismo externo, ni implementar complejas soluciones basadas en triggers: basta con habilitar CDC: Change Data Capture.

  1. Habilitar CDC en la base de datos
  2. Habilitar CDC para la tabla correspondiente, puede habilitarse para todas las columnas o para columnas en particular
  3. Siempre que queramos utilizamos la consulta cdc.fn_cdc_get_all_changes_nombreTabla para obtener los cambio realizados entre dos LSN determinados.

Los pasos a seguir son muy sencillos, y pueden verse la demostración completa en el código de ejemplo. Se trata de la creación de una tabla de ejemplo con columnas IdProducto, Nombre, Precio, PrecioRebajado, donde despues de habilitar CDC se realizan tres inserciones, dos actualizaciones y un borrado. El resultado de consultar los cambios es una tabla como esta:

Las operaciones se ordenan por orden de ejecución, siendo la primera la más antigua. Podemos observar tres primeros INSERT y un DELETE. En estos casos la información que aparece es la insertada y la borrada de la tabla respectivamente.

En el caso de UPDATE, se insertan dos filas por actualización. La primera de ellas se corresponde con lo que había en la tabla antes del UPDATE y la segunda con lo que hay después. En esta operación también es interesante consultar la columna __$update_mask, que nos indica cuáles han sido las columnas afectadas por la actulización. Por ejemplo, 0x0A=0000 1010 indica que se han modificado la segunda y la cuarta, mientras que 0x02=0000 0010 indica que únicamente se ha visto afectada la segunda columna de la tabla.

4 comentarios sobre “Change Data Capture. Histórico de cambios en SQL Server 2008 R2”

  1. Pues sí, creo que se le puede sacar mucho partido a esta característica. Un apunte interesante, lsn_time_mapping para obtener fecha y hora de comienzo y fin de transacción a partir de su LSN:

    select *
    from
    cdc.fn_cdc_get_all_changes_dbo_Productos
    ( sys.fn_cdc_get_min_lsn(‘dbo_Productos’),
    sys.fn_cdc_get_max_lsn(),’all’) AllChanges
    inner join
    cdc.lsn_time_mapping TimeMapping On AllChanges.__$start_lsn=TimeMapping.start_lsn

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *