Introducción

Change Data Capture (CDC) en SQL Server es una potente función diseñada para rastrear y capturar los cambios realizados en los datos de una base de datos. Proporciona una forma fiable y eficiente de identificar las alteraciones en las tablas, lo que permite extraer información valiosa sobre las modificaciones de los datos a lo largo del tiempo. Al habilitar CDC con Azure Data Factory, SQL Server permite un enfoque sistemático y automatizado para supervisar y capturar los cambios, lo que facilita una mejor gestión de datos, auditoría y análisis dentro del entorno de base de datos.

Casos de uso más comunes: CDC con Azure Data Factory

Los escenarios comunes en los que el CDC con Azure Data Factory resulta beneficioso incluyen:

Pista de auditoría y análisis: Supervisión de las alteraciones de datos para pistas de auditoría y realización de evaluaciones analíticas de los datos de cambio.

Propagación descendente: Propagación eficiente de los cambios a los suscriptores descendentes para la actualización sincronizada de los datos.

Operaciones ETL: Facilitar las operaciones de extracción, transformación y carga (ETL) para transferir sin problemas los cambios de datos desde el sistema de procesamiento de transacciones en línea (OLTP) a un lago de datos o almacén de datos. Para ello pueden emplearse herramientas como Azure Data Factory.

Programación basada en eventos: Permite la programación basada en eventos para obtener respuestas instantáneas provocadas por cambios en los datos, lo que mejora las interacciones del sistema en tiempo real.

Utilización: Algunas consultas

A continuación se presentan consultas y comandos SQL para gestionar la Captura de Datos de Cambios (CDC) en SQL Server:

• Comprueba si CDC está activado para la base de datos:

Selecciona nombre, is_cdc_enabled from sys.databases;

• Compruebe qué tablas tienen CDC enabled::

Selecciona nombres, is_tracked_by_cdc from sys.tables;

• En primer lugar, hay que habilitar la base de datos:
  • EXEC sys.sp_cdc_enable_db
• A continuación, habilita todas las tablas que deben auditarse:

EXECUTE sys.sp_cdc_enable_table

        @source_schema = N’dbo’,

        @source_name = N’PslMaterials’,

        @role_name     = NULL;

• Para desactivar la base de datos:
  • EXEC sys.sp_cdc_disable_db
• Para desactivar una tabla:
  • EXEC sys.sp_cdc_disable_table
  •     @source_schema = N’dbo’,
  •     @source_name   = N’MyTable’,
  •     @capture_instance = N’dbo_MyTable’

Cuando se activa CDC para una base de datos, se establece un esquema dedicado denominado CDC. Dentro de este esquema, se crean varias tablas esenciales para gestionar y almacenar datos de cambios. Es fundamental tener en cuenta que la desactivación de CDC para una tabla o para toda la base de datos puede provocar la eliminación de estas tablas, con la consiguiente pérdida de los cambios históricos. Para conservar estos datos históricos, es necesario copiar los cambios en otra tabla o archivo.

Esquema CDC

Las tablas clave dentro del esquema CDC incluyen:

• cdc.change_tables: la lista de tablas con cdc activado
• cdc.captured_columns : la lista de columnas capturadas para cada tabla
• cdc.ddl_history: Documenta las sentencias del Lenguaje de Definición de Datos (DDL) que modifican las tablas fuente. Estos cambios no se aplican inmediatamente a las tablas CDC; es necesario reiniciar la instancia CDC para que los cambios surtan efecto.

• cdc.index_columns: Define la clave primaria de las tablas CDC.
• cdc.lsn_time_mapping: Gestiona el mapeo temporal del número de secuencia de bloque largo.

Además, cuando se habilita una tabla para CDC, se crean dos tablas más:

• cdc.cdc_jobs: Gestiona los trabajos relacionados con los CDC.
• cdc.SchemaName_TableName_CT: Representa la tabla de cambios para un esquema y una tabla específicos, por ejemplo, dbo_PslVendors_CT.

Refleja todos los campos de la tabla original con algunas columnas adicionales necesarias para CDC:

• __$start_lsn: Código binario que lleva la cuenta de cuándo se confirmaron los cambios, ayudando a mantener el orden en que se produjeron.
• __$seqval: Otro código binario utilizado para organizar los cambios de una fila dentro de una transacción.
• __$operation: Un número que indica el tipo de cambio realizado en los datos. 1 representa un borrado, 2 es para inserción, y 3 y 4 son para actualizaciones (capturando los valores de las columnas antes y después de la actualización).
• __$update_mask: Una serie de bits que indican qué columnas se han modificado durante una actualización.
• : Las columnas restantes representan los datos específicos capturados durante la creación de la instancia de captura. Si no se ha especificado ninguna columna, se incluirán todas las columnas de la tabla de origen.

Detalles de la implementación de los CDC:

• Cada tabla de origen habilitada para el CDC tiene su tabla CDC dedicada.
• Garantizar espacio suficiente en la base de datos para acomodar las tablas adicionales generadas, evitando posibles carencias de espacio.
• El trabajo de captura del Agente de SQL Server recupera los cambios del registro de transacciones y los incorpora a las tablas de cambios correspondientes.
• Las tareas de limpieza gestionan las tablas de cambios, siguiendo una política de retención para eliminar los datos obsoletos.
• Las funciones de consulta proporcionan un medio para acceder y utilizar los datos de cambios de las tablas de cambios de CDC.
• En las bases de datos Azure SQL, donde SQL Server Agent no está disponible, el programador CDC asume el papel de capturar y limpiar los datos.

Consideraciones sobre el rendimiento: Factores que influyen en el rendimiento

• Número de tablas habilitadas para CDC
  • Cuantas más tablas se habiliten para CDC, mayor será la sobrecarga de procesamiento. Evalúe la necesidad frente al impacto en el rendimiento.
• Frecuencia de cambios en las tablas de seguimiento
  • Las tablas que sufren cambios frecuentes aumentan el volumen de datos capturados. Los datos que cambian con frecuencia pueden afectar al rendimiento.
• Disponibilidad de espacio en la base de datos de origen
  • CDC captura los cambios y los almacena. Garantice un espacio adecuado en la base de datos de origen para alojar las tablas de cambios sin correr el riesgo de que falte espacio.

CDC con Azure Data Factory

En la nube de Azure, Data Factory es una potente herramienta para diversas necesidades, y ahora incluye una vista previa para Change Data Capture (CDC), que simplifica el proceso, ofreciendo la potencia sin fisuras de CDC. Exploremos los pasos para aprovechar esta característica:

Paso para crear CDC en Data factory:

1. Vamos a crear un CDC: El CDC puede ejecutarse como un recurso independiente, eliminando la necesidad de un pipeline, ya que es necesario por ejemplo para ejecutar flujos de Datos.

2. Asigna un nombre al recurso (debe ser alfanumérico): Elige el tipo de fuente, desde varios tipos de bases de datos hasta archivos. En el caso de la base de datos Azure SQL, seleccione las tablas. Las tablas habilitadas para CDC se detectan automáticamente; de lo contrario, especifique un campo que defina las modificaciones de las filas (normalmente un campo de fecha de modificación).

3. Elige el destino: En este caso, los mismos que los tipos de origen: bases de datos y también algún almacenamiento donde guardar los ficheros con los cambios.

4. Define el destino: La tabla de destino se creará automáticamente con la opción Mapa automático seleccionada. Elige una clave para la tabla de destino.

5. Define una latencia entre las opciones dadas: Tiempo real, 15 minutos, 30 minutos, 1 hora, 2 horas. Inicia el proceso y el agente leerá los datos a los intervalos definidos.

6. Monitorea: Los puntos verdes significan las instancias en las que se ejecutó CDC, lo que ocurre cada 15 minutos en este ejemplo. Los puntos azules representan los cambios capturados durante cada ejecución, proporcionando una interfaz de monitorización clara.

Conclusión

CDC destaca como una herramienta robusta e influyente, que ofrece valiosas capacidades para el seguimiento y la gestión de los cambios en las bases de datos. Con la llegada de CDC con Azure Data Factory, este poder se aprovecha a la perfección de una manera práctica y fácil de usar. La combinación de CDC y Data Factory presenta una solución eficiente y accesible para implementar Change Data Capture con la máxima satisfacción.