Saltar al contenido
Categoría: SQL Server
2023-01-19

Change Data Capture en SQL Server

Change Data Capture en SQL Server

Hola a tod@s y bienvenidos a un nuevo post en el mejor blog de SQL Server. En la entrada de hoy vamos a hablar sobre Change Data Capture, qué es, cómo se configura, detalles importantes que debemos tener en cuenta a la hora de utilizarlo, y consultas útiles en el día a día. Así que… ¡empezamos!

¿Qué es Change Data Capture?

Change Data Capture o CDC es un recurso que permite registrar la actividad, dentro de una base datos, cuando se modifican tablas y filas. El CDC está disponible en Azure SQL Database, SQL Server y Azure SQL Managed Instance.

¿Cómo funciona Change Data Capture?

El Change Data Capture se ubica dentro de la tabla en nuestra BBDD y nos permite guardar la trazabilidad de los datos desde que los insertamos, todas las actualizaciones que sufren y hasta que finalmente son eliminados.

¿Para qué sirve el Change Data Capture a nivel práctico?

El CDC nos sirve a nivel de registro o histórico de todo el proceso por el que han pasado nuestros datos, desde que los introducimos hasta que se borran. Podríamos decir que es la historia de los datos. Os pongo un ejemplo con un posible efecto práctico:

Imaginemos que somos una compañía textil y tenemos guardada la dirección de nuestro socio. Este socio, fiel a la compañía, lleva mucho tiempo siendo asociado y siempre ha intentado tener sus datos actualizados. Si consultamos nuestra BBDD, veremos cuál es la dirección actual del usuario. Ahora vamos a comprobar qué sucede si analizamos este mismo dato a través del CDC:

  • Inserción –> Dirección 1ª: Alicante
  • 2 años después –> Actualización –> Dirección 2ª: Valencia
  • 2 años después –> Actualización –> Dirección 3ª Madrid
  • 15 años sin más modificaciones

¿Y ahora os preguntaréis, porque me interesa saber dónde vivió nuestro socio, si ya sé dónde vive ahora mismo?

Vale, vamos a pensar a lo grande, como si de un empresario se tratara. Vamos a procesar los mismos datos pero de todos los socios. ¿Cuál sería el resultado que obtendríamos?

En este caso veríamos cómo se desplaza la población asociada a nuestra compañía y como se distribuye sobre la geografía. Estos datos pueden resultar útiles a la hora de realizar una investigación de mercado para conocer cuál sería la mejor ubicación para una nueva tienda. Conociendo cuál es la distribución de nuestros asociados encontraremos la mejor localización para obtener el nivel de ventas deseado.

¿Cómo se configura Change Data Capture en SQL Server?

El CDC se habilita tanto a nivel de BBDD cómo a nivel de tabla. Por lo que, el primer paso será habilitarlo a nivel de BBDD, y a continuación, a nivel de tabla. Vamos con un ejemplo de habilitar el CDC para una tabla para una base de datos.

Use 'Database_name'
GO
EXEC sys.sp_cdc_enable_db
GO
  • En caso de que el entorno sea Amazon RDS la sentencia es la siguiente:
Use 'Database_name'
GO
EXEC msdb.dbo.rds_cdc_enable_db 'Database_name'
  • Habilitamos una por una las tablas que queremos poner en CDC.
exec sys.sp_cdc_enable_table
@source_schema = N'Schema_name',
@source_name =N'Table_name',
@role_name = NULL,
@supports_net_changes = 1

En el siguiente enlace podemos ver todos los parámetros que podemos ajustar a nuestras tablas a la hora de configurar el CDC para las mismas:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-ver16

Con esto, ya tendríamos configurada nuestra tabla y preparada para guardar todos los datos.

Peligros de utilizar Change Data Capture en SQL Server

Esta tecnología, requiere tener un gran control del archivo de log. ¿Porqué?

Para esto, primero tenemos que entender cómo trabaja SQL Server a la hora de ejecutar las transacciones. En SQL Server, todas las transacciones se escriben primero en el archivo de log, y luego lo escribe en el archivo de datos. Una vez todos los recursos han terminado con las transacciones en el archivo de log, se liberan o se marca para poderse liberar estas transacciones del archivo.

En SQL Server cuando alguien se conecta al CDC, este se encarga de retener los registros en el archivo de log, a la espera de que el usuario conectado los consulte, antes de liberarlos o marcarlos para liberar. Digamos que les añade un check, que tiene que marcar antes de poder liberarlos. Esto puede hacer que estos registros se acumulen, de manera que el archivo de log crezca, y nos generen problemas de espacio en el disco si no está bien dimensionado, o no lo tenemos correctamente monitorizado.

Querys útiles para trabajar con el CDC.

  1. Para revisar el estado del Log de las bases de datos con el CDC activado.
select name, log_reuse_wait_desc, is_cdc_enabled from sys.databases

2. Sobre las bases de datos que tengamos el CDC activo, podemos consultar que tablas están incluidas en el CDC.

select name,type,type_desc,is_tracked_by_cdc from sys.tables where is_tracked_by_cdc = 1

3. Para revisar el estado de configuración del CDC.

EXECUTE sys.sp_cdc_help_jobs

4. Cambiar la configuración del CDC.

EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 30;

5. Para revisar el funcionamiento de nuestro CDC por base datos y los errores podemos revisar las siguientes vistas de sistema.

 select * from sys.dm_cdc_log_scan_sessions
  1. Aquí veremos todos las sesiones que han ocurrido, y en la primera fila el agregado desde que se arrancó el CDC.
select * from sys.dm_cdc_errors
  • Desactivar el CDC por completo.
Use 'Database_name'
GO
EXEC sys.sp_cdc_disable_db
GO
  • En caso de que el entorno sea Amazon RDS la sentencia es la siguiente:
Use 'Database_name'
GO
EXEC msdb.dbo.rds_cdc_disable_db 'Database_name'
  • Arrancar y parar los jobs asociados al CDC.
exec sp_cdc_start_job

exec sp_cdc_stop_job
  • Quitar una tabla del CDC en una base de datos.
EXEC sys.sp_cdc_disable_table
@source_schema = N'Schema_name',
@source_name = N'Table_name',
@capture_instance = 'all';

Ilustración gráfica del ejemplo anterior

En primer lugar, vemos cómo el cliente no existía y que se registra en Alicante:

Tras insertar el primer registro, dentro de la tabla interna del CDC se ha implementado esta cambio. Ahora vamos a probar a actualizar este valor de Alicante a Valencia.

Vemos cómo se ha registrado el cambio, guardando el estado anterior (_$operation =3) y posterior. (_$operation =4).

Ahora volveremos a cambiar la provincia, cómo pone en el ejemplo y a continuación, eliminaremos el registro a modo de ejemplo, aunque esto no forma parte del ejemplo anterior.

Vemos cómo ha ocurrido al igual que el ejemplo anterior, pero con Madrid, y a continuación hemos borrado el registro (_$operation =1).

Recursos externos que utilizan Change Data Capture

AWS DMS. Vemos que Amazon Web Service (AWS), tiene un servicio llamado Database Migration Service DMS que se apoya en el CDC para poder extraer los datos y migrarlos.

https://aws.amazon.com/es/dms/

Hasta aquí el post de hoy. Espero que te haya servido de ayuda para mejorar las tareas en SQL Server.

Visita nuestras últimas entradas:

Evita problemas en tu migración SQL Server con Data Migration Assistant

Conoce las estadísticas de espera o Waits para SQL Server

Aprendiendo a organizar SQL Server Management Studio para distintos entornos

Si necesitas mejorar el rendimiento de tu base de datos SQL Server, contacta con nosotros:

Complete este formulario para recibir la guía de Windows Server en Azure
*Obligatorio
Complete este formulario para recibir la guía de Windows Server en Azure
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
*Obligatorio
Complete este formulario para recibir 4 best practices to implement a comprehensive Zero Trust security approach
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud Migration Essentials
*Obligatorio
Complete este formulario para recibir Cloud Migration Essentials
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
*Obligatorio
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete este formulario para recibir Prevent data leaks with Microsoft 365 Business Premium
Gracias por rellenar el formulario [name]. ¡Aquí tienes tu eBook Gratis!
Complete this form to recieve the guide of Windows Server on Azure
*Required
Complete this form to recieve the guide of Windows Server on Azure
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
*Required
Complete this form to recieve 4 best practices to implement a comprehensive Zero Trust security approach
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Essentials
*Required
Complete this form to recieve Cloud Migration Essentials
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud security Advice for Nonprofit Leaders
*Required
Complete este formulario para recibir Cloud security Advice for Nonprofit Leaders
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!
Complete this form to recieve Cloud Migration Simplified Ebook.
*Obligatorio
Complete this form to recieve Prevent data leaks with Microsoft 365 Business Premium
Thank you for filling out the form [name]. Here's your Free eBook!