Utilizando Change Tracking y Chage Data Capture en SQL Server

Change Tracking  es un método que provee SQL Server 2008,  para determinar qué cambios ha ocurrido en los datos y estructura de la base de datos.

Podemos realizar las siguientes funciones:

·         Funcionalidad con DML

·         Responde preguntas como:

o   Cuantas filas en la tabla han cambiado

o   Que Columnas ha cambiado

o   Que fila en particular ha sido actualizada

Configurando Change Tracking

Para habilitarlo lo podemos hacer vía SSMS o por ALTER DATABASE.

Se deberá configurar las siguientes opciones:

·         Change Tracking definirlo en True

·         Retention Period  Define la cantidad de tiempo que se le dara mantenimiento.  Por defecto es 2

·         Tipo de Unidad de Retention Period.   Dias, Horas, Minutos.  Por defecto es Dias

·         Auto CleanUp.   Lo pondremos en ON  para que automáticamente limpie la información

O bien definir la siguiente instrucción T-SQL

ALTER DATABASE AdventureWorksDW2008

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

Vemos lo que tenemos configurado:

SELECT  * FROM sys.change_tracking_databases ctd

SELECT OBJECT_NAME([object_id]), * FROM sys.change_tracking_tables ctt

Al realizar INSERT,  UPDATE  o DELETE   podremos verificar la información de indica que se realizado una modificación y en que numero de registro de una tabla

DECLARE @last_synchronization_version bigint

SET @last_synchronization_version =

CHANGE_TRACKING_Min_VALID_VERSION(2105058535);

SELECT  CT.* FROM CHANGETABLE(CHANGES NOMBRE_TABLA, @last_synchronization_version) AS CT

 

Habilitando el CDC (Change Data Capture)  

Para habilitarlo en la base de datos utilizar la siguiente instrucción T-SQL

EXECUTE sys.sp_cdc_enable_db;

Luego habilitarlo para una tabla específica,  utilizaremos el ejemplo de la tabla llamada “Director”

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'Director'

  , @role_name = N'cdc_admin'

  , @capture_instance = N'Director'

  , @supports_net_changes = 1

El nombre de instancia de captura,  es el nombre se utilizara para llamar posteriormente a esta tabla en los CDC.

DECLARE @begin_time datetime, @end_time datetime,

@from_lsn binary(10)  , @to_lsn binary(10);

SET @begin_time = GETDATE() -15;

 SET @end_time = GETDATE();

SET @from_lsn = sys.fn_cdc_map_time_to_lsn(

  'smallest greater than or equal', @begin_time);

  SET @to_lsn = sys.fn_cdc_map_time_to_lsn(

  'largest less than or equal', @end_time);

SELECT  * FROM cdc.fn_cdc_get_all_changes_Director(

       @from_lsn, @to_lsn, 'all');

O bien obtener todo lo que ha realizado en la tabla Director.  Podemos ver las tablas creadas en las tablas del sistema de la base datos.

SELECT * FROM cdc.Director_CT