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 = 1En 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:
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.
- 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.databases2. 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 = 13. 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
- 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:
Data Engineer.
