0
Shares
0
0
0

Hay veces en las que en las aplicaciones es necesario guardar los datos a modo de histórico o rastrear los cambios producidos en los datos a fin de que puedan ser examinados con detalle en cualquier momento como muestra de trazabilidad, como afirmación de que los datos no han sido manipulados fuera de una aplicación o en sistemas críticos que necesiten cachear los datos rápidamente y necesitan saber que ha cambiado, para ello desde la versión 2008 SQL Server incorpora Change Data Capture (CDC) y Change Tracking (CT).

¿Que son Change Data Capture y Change Tracking?

Change Data Capture (CDC) se encarga de capturar y almacenar los datos que cambian para una tabla de usuario, actúa como un histórico. Ejemplo, imaginemos la tabla facturas de una empresa, esta tabla tiene una columna IdCliente que es la primary key y un campo llamado FxFactura. Si se realizara  una modificación en el cliente con IdCliente=8 en el campo FxFactura CDC recogería estos cambios de manera que se pudiera ver el valor antiguo y el nuevo del campo FxFactura.

Change Tracking (CT) se encarga de capturar y almacenar los cambios que se producen en una tabla de usuario. No almacena el dato ni la sentencia DML (insert, update, delete), pero sí el registro, acción y columnas que cambiaron. Partiendo del ejemplo anterior almacenaría un registro para el Id 8 indicando que se ha producido una operación update ( U ) sobre el campo FxFactura.

Change Data Capture y Change Tracking no son features excluyentes ambas pueden trabajar en la misma instancia, base de datos, esquema y tabla.

Analogías y diferencias entre Change Data Capture y Change Tracking

Analogías:

  • Tecnología propia de Microsoft no exige a los desarrolladores que tengan que programar nada específicamente para ese fin.
  • Ambos proporcionan un sistema de limpieza a fin de que se libere espacio una vez que consideremos que ha datos antiguos que no nos sirven.
  • Hay que tener cuidado con ciertas operaciones que implican añadir, modificar o borrar columnas asociadas a una tabla con Change Data Capture o Change Tracking.

Diferencias:

  • CDC trabaja con el logreader (funcionalidad de replicación) mientras que CT trabaja con tecnología del motor relacional.
  • CDC se basa en el dato y CT al cambio.
  • Con CDC se pueden crear dos “capturas” para una misma tabla en un esquema, en CT solo una.
  • Change Tracking permite contextualizar un cambio de manera que permita identificarlo, Change Data Capture no.
  • CDC puede trabajar asíncronamente.
  • CDC requiere SQL Agent para poder trabajar.
  • Change Tracking genera menos sobrecarga que Change Data Capture ya que solo almacena el cambio.
  • A la hora de realizar restores existe la opción de restaurar con CDC activo si se cumplen ciertos requisitos.

Ventajas y Desventajas

Ventajas

  • Rendimiento, puesto que Change Data Capture y Change Tracking son soluciones que basan en el código nativo de SQL Server es muy díficil que sean más lentas que las funcionalidades desarrolladas por nosotros para el mismo fin. Pensemos que lo más común para aplicaciones de este tipo es bien añadir una columna timestamp para controlar cuando cambia un dato o bien triggers. Esto supone un gasto de recursos extra ya que cuando se realiza una operación esta se guarda en el log de transacciones siempre (aunque no tengamos CDC o CT) y estas tecnologías utilizan esta ventaja.
  • Tiempo de desarrollo. Al no tener que desarrollar nada evitamos tener que utilizar tiempo de los programadores y analistas para implementar una solución para este cometido.
  • No hay que desarrollar un proceso que elimine datos antiguos para liberar espacio.
  • No hay que crear tablas, columnas o triggers.
  • Se dispone de un conjunto de funciones y procedimientos para acceder a los datos (CDC) o cambios (CT) que han cambiado.
  • En el caso de Change Data Capture puede trabajar asíncronamente activándolo a nuestro criterio.

Desventajas

  • Estas funcionalidades se centran en el que y no en el quien, es decir no van a almacenar “per se” información de quien realiza la acción a no ser que dentro de la tabla tengamos campos de usuario de creación o modificación creados por nosotros.

¿Para qué sirven? ¿En que entornos los podemos aplicar?

  • Tablas Históricas.
  • Procesos que necesitan los datos de una determinada hora.
  • Aplicaciones donde es necesaria una trazabilidad completa de sus datos.
  • Actualizaciones periódicas de datawarehouse.
  • Aplicaciones con almacenamiento en caché, para minimizar el trasiego de datos y mantener la caché actualizada conviene enviar solo lo que ha cambiad.

Change Data Capture (CDC)

A continuación vamos a ver como implementamos CDC:

1. Creamos la base de datos, una tabla y un índice tipo “unique”:

Code Snippet
  1. use master
  2. create database ChangeDataCap
  3. go
  4. use ChangeDataCap
  5. go
  6. create table [dbo].[tabla](
  7.     [id] [int] NOT NULL,
  8.     [inte] int not null,
  9.     [varc] [varchar](10) NULL,
  10. constraint pk_tabla primary key clustered
  11. (
  12.         [id] asc
  13. )
  14. ) on [primary]
  15. go
  16. create unique index ix_inte on tabla (inte)
  17. go

2. Activamos para la base de datos CDC y nos aseguramos que dicha información queda registrada en SQL Server consultando sobre sys.databases:

Code Snippet
  1. exec sys.sp_cdc_enable_db
  2. go
  3. select name, is_cdc_enabled from sys.databases where name = ‘ChangeDataCap’
  4. go

3. Añadimos la tabla que queremos trazar a CDC. Para que se cree correctamente es necesario que esté activado del Agente de SQL Server ya que creará dos jobs. El primero se encargará de leer el log de transacciones y el segundo se encargará de la limpieza de los datos.

Code Snippet
  1. exec sys.sp_cdc_enable_table
  2.     @source_schema = N’dbo’
  3.   , @source_name = N’tabla’
  4.   , @role_name = N’cdc_admin’
  5.   , @supports_net_changes = 0
  6.   , @index_name = N’ix_inte’
  7.   , @captured_column_list = N’inte,varc’
  8.   , @filegroup_name = N’PRIMARY’;
  9. go
  10. exec sys.sp_cdc_help_change_data_capture
  11.     @source_schema = N’dbo’,
  12.     @source_name = N’tabla’;
  13. go

Donde:

  • @source_schema, que es el esquema en el que está la tabla.
  • @source_name, contiene el nombre de la tabla.
  • @role_name, que es el rol de base de datos que se utilizará para leer los datos (si no existe se crea).
  • @capture_name, aunque este parámetro no lo he utilizado sería el nombre que se le da a la captura de datos, si no se informa crea el nombre a partir del esquema y la tabla, en nuestro caso sería “dbo_tabla”. Esto es útil ya que a veces es necesario crear dos “capturas” y como no se puede repetir nombre pues le proporcionamos el que queremos. El caso en el que es útil tener dos capturas es que nosotros estamos desarrollando y necesitamos añadir más columnas. De esta manera tendríamos funcionando la captura vieja sin los cambios y desarrollar con la nueva.
  • @supports_net_changes, si tiene valor 1 CDC crea una función que nos proporciona la última versión de un registro en un rango de lsns. Ej Si está informado a 0 se crea una funcion en el esquema cdc llamada cdc.fn_cdc_get_all_changes_dbo_tabla donde la parte en negro siempre es fija y la roja es el capture name. Esta función se utiliza para ver los cambios que ha sufrido esa captura (dbo_tabla). Imaginemos que tenemos un registro que ha sufrido varios cambios en un intervalo de lsns (lsn = log sequence number), en ese caso esta función nos devolvería todas versiones de ese registro con todos sus campos (lo veremos más adelante). Cuando tiene valor 1 además de crear la función anterior crea cdc.fn_cdc_get_net_changes_dbo_tabla. Para el caso anterior devolvería el último registro del intervalo.
  • @index_name, para que haya CDC sobre una tabla le tienes que indicar bien la primary key de la tabla o un índice “unique”, esto se debe a que necesita tener una manera de identificar los registros unívocamente.
  • @captured_column_list, en este parámetro señalas en que columnas quieres que CDC rastree los cambios, se separan por coma.
  • @filegroup_name, como su nombre indica será el filegroup donde CDC creará los objetos. Sería aconsejable que fuera un filegroup ajeno a las tablas de usuario para mejorar el rendimiento e influir lo mínimo en las operaciones de usuario.

El procedimiento sys.sp_cdc_help_change_data_capture nos ayuda a ver las instancias de CDC están asociadas a una tabla de un esquema, recordemos (máximo dos). Este es su resultado:

Change Data Capture Change Tracking

4. ¿Fácil no? ahora que tenemos todo montado vamos a ver como funciona realmente. Para ello, vamos a realizar las siguientes operaciones:

Code Snippet
  1. insert into tabla (id,inte,varc) values (1,1,‘1’)
  2. insert into tabla (id,inte,varc) values (2,2,‘2’)
  3. update tabla set id =6, inte=6, varc=‘3a’ where id=1
  4. update tabla set varc=‘3aa’ where id=6
  5. delete from tabla where id = 2

5. Ejecutando la siguiente query vemos los resultados:

Code Snippet
  1. declare @lsn_desde binary(10), @lsn_hasta binary(10)
  2. set @lsn_desde = sys.fn_cdc_get_min_lsn(‘dbo_tabla’)
  3. set @lsn_hasta   = sys.fn_cdc_get_max_lsn()
  5. select * from cdc.fn_cdc_get_all_changes_dbo_tabla(@lsn_desde, @lsn_hasta, N’all’)

Como veis es la función que comentaba anteriormente, hay que indicarle el rango de lsns y si queremos que muestre todos los registros “all” o “all update old”. Esta última opción muestra todos los registros y en el caso de los updates el valor antiguo.

Change Data Capture Results

  • El registro 1 se corresponde con la inserción (inte = 1, varc = 1)
  • El registro 2 se corresponde con la inserción (inte = 2, varc = 2)
  • El registro 3 y 4 se corresponde con el update (id=6, inte=6 y varc=3a). Fijaos que el start_lsn es el mismo para ambos por lo que es la misma operación. Como la columna para identificar unívocamente un registro es “inte” y esta cambia es equivalente a borrar el registro 1 y a insertar el registro 6
  • El registro 5 se corresponde con el update (varc=3aa)
  • El registro 6 se corresponde con el borrado (id=2)

Ejecutando la sentencia anterior con el parámetro “all update old” tenemos:

Change Data Capture Results 2

Todo es igual salvo que se incluye un registro más para la operación update(varc=3aa), en el registro 5 muestra el valor antiguo.

5. Para ubicar en el tiempo estos cambios disponemos de la tabla cls.lsn_time_mapping. Cuando ejecutamos la select obtenemos:

Code Snippet
  1. select * from cdc.lsn_time_mapping

lsn time mapping

Como podemos ver a partir de la columna start_lsn podemos unir ambos conjuntos de resultados para conocer la fecha y hora de la transacción.

6. Para deshabilitar CDC en una tabla utilizamos la siguiente sentencia:

Code Snippet
  1. exec sys.sp_cdc_disable_table
  2.     @source_schema = N’dbo’,
  3.     @source_name = N’tabla’,
  4.     @capture_instance = N’dbo_tabla’;
  5. go

Donde:

  • @source_schema contiene el esquema de la tabla
  • @source_name, nombre de la tabla
  • @capture_instance, recordemos que es un nombre que le damos a la captura ya que pueden existir dos

7. Para deshabilitar CDC en la BBDD utilizamos la siguiente sentencia:

Code Snippet
  1. exec sys.sp_cdc_disable_db

Change Tracking

Ahora vamos a ver como configuramos Change Tracking:

1. Creamos la base de datos y la activamos para CT

Code Snippet
  1. use master
  2. create database ChangeTracking
  3. go
  4. alter database ChangeTracking set change_tracking = on (change_retention = 2 days, auto_cleanup = on);
  5. go

Como podemos ver para configurar una base de datos con Change Tracking se establecen tres opciones:

  • Change_tracking, habilita o deshabilita CT con los valores ON/OFF
  • Change_retention, indica el periodo de tiempo que permaneceran los datos, además del valor hay que establecer la magnitud que puede ser días, horas o minutos
  • Auto_cleanup, si esta establecido con valor ON la información se borra automáticamente después del periodo de retención especificado. Si tiene valor OFF los datos retenidos no se borran

2. Creamos la tabla e insertamos un par de registros:

Code Snippet
  1. use ChangeTracking
  2. go
  3. create table [tabla](
  4.     [id] [int]  NOT NULL,
  5.     [varc] [varchar](10) NULL,
  6.     [inte] int null
  7.     constraint pk_tabla primary key clustered
  8.     (
  9.         [id] asc
  10.     )
  11. ) on [primary]
  12. go
  13. insert into tabla (id,inte,varc) values (1,1,‘1’)
  14. insert into tabla (id,inte,varc) values (2,2,‘2’)
  15. go

3. Activamos Change Tracking en la tabla, (es necesario que tenga primary key sino dará error):

Code Snippet
  1. alter table tabla enable change_tracking with (track_columns_updated = on)

Donde:

  • Enable change_tracking habilita CT para la tabla
  • Track_columns_updated indica que CT debe realizar el seguimiento de las columnas que se actualizaron

4. Realizamos unas cuantas operaciones y vemos sus resultados:

Code Snippet
  1. insert into tabla (id,inte,varc) values (5,5,‘5’);
  2. insert into tabla (id,inte,varc) values (7,7,‘7’)
  3. update tabla set varc=‘luis1’ where id=2
  5. declare @contexto varbinary(128);
  6. set @contexto = cast(‘Contexto Aplicacion’ as varbinary(128));
  8. with change_tracking_context (@contexto)
  9. update tabla set inte=8 where id=5
  11. delete from tabla where id = 1
  12. go
Code Snippet
  1. select
  2.     *,
  3.     change_tracking_is_column_in_mask
  4.     (
  5.        columnproperty(object_id(‘tabla’), ‘varc’, ‘columnid’),
  6.        ct.sys_change_columns
  7.     ) as [¿la columna varc ha cambiado?],
  8.     cast(ct.sys_change_context as varchar(128)) columna_contexto
  9. from changetable(changes tabla,0) as ct order by sys_change_version
  10. go
  11. select     tab.*, ct.*
  12.   from tabla as tab cross applychangetable(version tabla, (id), (tab.id)) as ct;
  13. go

Antes de comentar los resultados es vamos a explicar la función que nos proporciona los cambios, es changetable. Esta función tiene dos comportamientos en función de si utilizamos el parámetro changes o version.

Changes, devuelve todos los cambios en una tabla desde la versión especificada. En nuestro caso he puesto la cero. Existen varias funciones para gestionar las versiones, son:

  • CHANGE_TRACKING_CURRENT_VERSION, devuelve la versión de la última transacción
  • CHANGE_TRACKING_MIN_VALID_VERSION ( table_object_id ), devuelve la mínima versión de una tabla, pero puede devolver null en tres casos, el seguimiento de cambios no está habilitado para la base de datos, el id de objeto de la tabla no existe o no se tienen permisos suficientes (hay que ser sysadmin o db_owner)

La información que devuelve es:

  • sys_change_version, última versión para una fila
  • sys_change_creation_version, versión en la que se creó la fila
  • sys_change_operation, operación realizada, I = Insert, D= Delete, U = Update
  • sys_change_columns, almacena las columnas que han cambiado siempre y cuando no hayan cambiado todas, esté CT activado y no sea una operación de inserción o borrado.
  • sys_change_context, informa del contexto de la operación, como contexto podemos entender aquella información que nos sirve para caracterizar o identificar algo. Podemos configurarlo con lo que queramos, usuario, fecha, aplicación. Ejemplo si todas las operaciones de una aplicación generan un contexto con el nombre de la aplicación, si realizamos una operación directamente en la tabla este campo se quedará nulo e identificaremos que alguien ha modificado los datos desde fuera de la aplicación
  • Valor de la primary key, este dato identifica unívocamente cada fila.

Version, devuelve la última versión para una fila especificada. Hay que indicarle:

  • La tabla, en nuestro caso “tabla”
  • La columna primary key, (id)
  • El/los valores que queremos recuperar, en nuestro ejemplo hacemos “cross apply” con tabla y puesto que tiene un alias le indicamos que es (tab.id)

La información que devuelve es:

  • sys_change_version, última versión para una fila
  • sys_change_context, informa del contexto de la operación, como contexto podemos entender aquella información que nos sirve para caracterizar o identificar algo. Podemos configurarlo con lo que queramos, usuario, fecha, aplicación. Ejemplo si todas las operaciones de una aplicación generan un contexto con el nombre de la aplicación, si realizamos una operación directamente en la tabla este campo se quedará nulo e identificaremos que alguien ha modificado los datos desde fuera de la aplicación
  • Valor de la primary key, este dato identifica unívocamente cada fila.

Las columnas calculadas no aparecen nunca como cambiadas en ambos modos.

Revisemos los resultados de la query con el modo changes:

Change Tracking results 1

Los registros 1 y 2 fueron creados antes de activar Change Tracking por lo que la columna sys_change_creation_version tiene valor null

El registro 1 tiene valor “5” en sys_change_version porque el borrado fue la quinta operación, la columna sys_change_operation tiene valor “D”

El registro 2 tiene valor “3” en sys_change_version ya que la tercera operación fue un update, la columna sys_change_operation tiene valor “U”. Como no se modificaron todas las columnas sys_change_columns almacena que columna cambió. Para traducir este valor a algo comprensible hemos utilizado en la select la función change_tracking_is_column_in_mask:

Code Snippet
  1. change_tracking_is_column_in_mask
  2. (
  3.    columnproperty(object_id(‘tabla’), ‘varc’, ‘columnid’),
  4.    ct.sys_change_columns
  5. ) as [¿la columna varc ha cambiado?],

Esta función requiere dos parámetros:

  • El Id de la columna de la tabla
  • El valor de la máscara

Si la columna ha cambiado devolverá 1 en caso contrario 0.

El registro 3 (id=5) tiene valor “4” en sys_change_version ya que la última operación sobre el fue la cuarta. La columna sys_change_creation_version tiene valor 1 ya que el insert fue la primera operación desde que se activó Change Tracking. Ahora bien, ¿Qué pasa con la columna sys_change_operation? la cuarta operación fue un update, no un insert, por lo que debería mostrar una “U”. La columna sys_change_context, también está informada y esa información de contexto está en la operación 4 no en la 1.

Lo que ha pasado es que muestra la primera operación después del intervalo que fue un insert. Si cambiamos el valor 0 de la consulta por 3 veremos que muestra una “U”. Ver la siguiente imagen:

Respecto a la columna sys_change_context vemos que está informada y almacenada en binario, pues bien para cargarla es necesario utilizar la sentencia “with change_tracking_context” tal y como se muestra en las operaciones, esto es, declaramos una variable varbinary(128), le asignamos la cadena que deseamos como contexto y ponemos la sentencia “with change_tracking_context” antes de la instrucción dml. Si para convertir a binario la información hacemos un cast a varbinary, para convertirla a varchar hacemos el paso inverso.

Revisemos los resultados de la query con el modo version:

Como la fila con Id = 1 fue borrada con no aparece, respecto al resto de filas muestran en sys_change_version su última versión y el contexto si tuvieron.

5. Por último vamos a deshabilitar CT en la tabla y en la base de datos:

Code Snippet
  1. alter table dbo.tabla disable change_tracking
  2. alter database ChangeTracking set change_tracking = off;

Conclusión

A modo de resumen creo que debemos quedarnos con estas ideas acerca de CDC/CT:

Change Data Capture y Change Tracking son dos características disponibles desde SQL Server 2008  orientadas al seguimiento de los cambios de una tabla.

Change Data Capture almacena los datos que cambiaron y Change Tracking los cambios.

Nos pueden llegar a proporcionar mejor rendimiento que soluciones “ad hoc” (evidentemente esto habría que medirlo en cada entorno pero a priori debería ser así)

Change Data Capture y Change Tracking no están enfocadas para almacenar quien realiza el cambio, es algo que debemos solucionar nosotros añadiendo un par de campos si es que lo necesitamos.

Si os gustan nuestros posts, desde SolidQ os invitamos a visitar nuestro blog o a suscribiros a nuestra newsletter para recibir las últimas novedades del sector en vuestro correo 🙂

0 Shares:
Share 0
Tweet 0
Share 0

10 comments

  3. Muy bueno tu aporte, tengo una duda como puedo saber quien hizo el cambio sin necesidad de algún trigger.

    Responder

    1. Hola Luis, pues las opciones son pocas, la primera es que si tienes procedimientos, funciones etc grabe quien ha hecho la operacion.

      Como esta opción es posible que implique tocar mucho código lo normal es ver triggers.

      Responder

  4. Excelente aporte! Gracias!
    Tengo una duda, existe la posibilidad de generar CDC con Management Studio? Es decir, sin ejecutar las sentencias?
    Algo mas automático, ya que tengo bastante tablas.
    Gracias

    Responder

    1. Con SSMS no existe posibilidad, no hay pantalla que lo haga. Precisamente con TSQL es más flexible, puedes generar lo que tu quieras para lo que tu quieras y de una tacada con scripts dinámicos.

      Si en lugar de escribir “tabla:” escribes el nombre de sentencias de cdc lo haras mucho mas rápido que en lugar de ir una a una. Entiendo que crearlo de esta manera pueda ser más complejo y lleve más tiempo al principio, pero a la larga es lo mejor.

      SELECT N’tabla: ‘ + t.name + N’
      columna: ‘ + c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.object_id = c.object_id AND
      t.name IN (‘#A0086C1B’,’#A05284DE’)

      Responder

  6. Tengo una pregunta. Implemente cdc en mi base de datos, pero posteriormente cuando tuve que hacer un alter sobre algunas tablas ya no pude obtener la información. Las funciones habían desaparecido. Debí volver a activar el cdc para las tablas y perdí la información histórica. Hay algún procedimiento para alterar una tabla y garantizar que no se pierda la información histórica del cdc. Gracias

    Responder

  8. Lo implemente hace un par de años, pero se me presenta un incompatibilidad con la Tarea de respaldo, la tabla que tiene CDC activado da error al respaldarla mediante un Procedimiento Almacenado. Tuve que desactivar CDC, el cual me era muy util. A alguien mas le paso algo asi ? gracias

    Responder
Deja una respuesta

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

— Previous article

Máquina virtual con SQL Server 2012

Next article —

Hadoop HDFS. Almacenamiento y consulta [Parte 2]

You May Also Like
AArtículos de Data

Cuando haces BOT ya no hay stop

Hoy en día no es raro encontrarse con un Bot como medio para interactuar con una plataforma online. La creciente implantación de esta tecnología en el mercado, nos invita a conocer Azure Bot Framework. Repasaremos las herramientas disponibles, el proceso de creación, buenas practicas, casos de uso y muchos más. Acompáñanos ¡cuando hacer bot ya no hay stop!