Explorando Change Data Capture (CDC) en SQL Server

¡Saludos, apasionados del mundo de las bases de datos! Hoy, como parte de nuestro viaje por los recovecos más fascinantes del universo SQL, nos sumergiremos en las profundidades de Change Data Capture (CDC). ¿Estáis listos? 

¿Qué es CDC y por qué debería importarnos?

CDC es la abreviatura de Change Data Capture. Pero no os dejéis engañar por su nombre modesto. Detrás de esas tres letras se esconde una funcionalidad asombrosa que nos permite rastrear los movimientos de nuestras tablas como verdaderos detectives de datos.

Imaginad esto: cada vez que alguien inserta, actualiza o elimina filas en una tabla, CDC está ahí, tomando notas meticulosas. ¿Cómo lo hace? Pues bien, Change Data Capture utiliza el SQL Server Agent para registrar esos cambios en el registro de transacciones. Sí, ese mismo registro que almacena los secretos de todas las operaciones que ocurren en nuestra base de datos.

Habilitar y Deshabilitar CDC

Habilitar CDC para una base de datos

Antes de poder crear una captura para tablas individuales, debemos habilitar la captura de datos modificados para la base de datos. Aquí está el hechizo mágico: ejecuten el procedimiento almacenado sys.sp_cdc_enable_db en el contexto de la base de datos. Si deseamos verificar si una base de datos ya tiene Change Data Capture habilitada, podemos mirar la columna is_cdc_enabled en la vista sys.databases. Cuando una base de datos tiene la captura de datos modificados habilitada, se crean objetos como el esquema cdc, el usuario cdc, las tablas de metadatos y otros objetos de sistema. El esquema cdc contiene las tablas de metadatos de la captura de datos modificados y, una vez que las tablas de origen han sido habilitadas para esta captura, también contiene las tablas de cambios individuales que sirven como repositorio de los datos de cambios. Este esquema cdc también contiene las funciones de sistema asociadas que se usan para consultar los datos modificados. La captura de datos modificados requiere el uso exclusivo del esquema cdc y del usuario cdc. Si ya existe un esquema o usuario de base de datos llamado cdc, deberemos borrarlo o cambiarle el nombre antes de habilitar CDC.

Usaremos este script para habilitar Change Data Capture en una base de datos

USE MiBaseDeDatos
GO
EXEC sys.sp_cdc_enable_db
GO

Deshabilitar CDC para una base de datos

Para deshabilitar la captura de datos modificados para una base de datos, utilizaremos el procedimiento almacenado de sistema sys.sp_cdc_disable_db en el contexto de la base de datos. No es necesario deshabilitar Change Data Capture para tablas individuales antes de deshabilitarlo para la base de datos. Cuando se deshabilita Change Data Capture para la base de datos, se eliminan todos los metadatos asociados, incluidos el usuario cdc, el esquema y los trabajos de captura de datos modificados.

USE MiBaseDeDatos
GO
EXEC sys.sp_cdc_disable_db
GO

Los jobs de SQL de CDC

Cuando Change Data Capture se habilita, se crea un job del Agente SQL Server para la captura de datos y otro de limpieza. Estos jobs rellenan y mantienen las tablas de cambios de CDC por lo que son imprescindibles para su funcionamiento. Así que, no olvidéis controlar esos jobs del Agente SQL para aseguraros de que Change Data Capture funcione.

Anécdota personal: Una vez me pidió ayuda un compañero porque no era capaz de reducir el log de una base de datos. Había seguido todos los pasos, backup Log + Shrink y nada. No había transacciones en ejecución pero ese log no se reducía. Estaba a punto de llenar completamente el disco duro y detener la producción generando una incidencia crítica. Pues bien, resulta que con CDC habilitado pero sin los jobs de Change Data Capture en ejecución las transacciones se quedan en el log y pueden llegar a llenarlo como pudimos comprobar. Dicho así tiene sentido ¿verdad? Pues es algo que no sale en los libros de SQL ni en la documentación y que tenéis que tener muy en cuenta.

Consultando los Datos Capturados

Una vez que hemos habilitado Change Data Capture para nuestra base de datos, es hora de sumergirnos en los datos capturados. ¿Cómo podemos acceder a esa valiosa información? Aquí están los pasos:

Consultar las Tablas de Cambio:

Podemos consultar el esquema cdc. En él encontraremos las tablas de cambios que almacenan los datos modificados. Estas tablas tienen nombres como cdc.dbo_MiTabla_CT (donde dbo es el nombre del esquema origen y MiTabla es el nombre de la tabla original). Utilizaremos  consultas SQL para acceder a estas tablas y descubrir qué filas han sido insertadas, actualizadas o eliminadas. ¡Es como leer las páginas de un emocionante diario!

Funciones de Sistema CDC:

Change Data Capture nos proporciona funciones de sistema específicas para consultar los datos modificados. Algunas de las más útiles son:

• cdc.fn_cdc_get_all_changes_<capture_instance>: Devuelve todos los cambios desde el inicio de la captura.
• cdc.fn_cdc_get_net_changes_<capture_instance>: Devuelve solo los cambios netos (última versión de cada fila modificada).
• cdc.fn_cdc_get_ddl_history_<capture_instance>: Muestra los cambios en la estructura de la tabla.

Filtros y Consultas Personalizadas:

Combinando estos métodos tendremos a nuestra disposición toda la información de Change Data Capture y el historial de nuestros datos. Ahora solo nos hace falta aplicar filtros a las tablas de cambio para consultar solo los datos relevantes para nosotros. 

Requisitos y Consideraciones

Antes de sumergirse en el mundo de Change Data Capture hay algunas cosas que debemos tener en cuenta:

• Modo de Recuperación de la Base de Datos: Change Data Capture puede funcionar con cualquier nivel de recuperación de la base de datos. Esto no es ningún problema.
• Espacio en Disco: Las tablas de cambio pueden ocupar espacio significativo en disco, así que asegúrense de tener suficiente almacenamiento.
• Permisos: Para poder habilitar o deshabilitar Change Data Capture tendremos que tener permisos de sysadmin ya que, como hemos visto, el cambio afecta a varios apartados de SQL Server.
• SQL Server Agent: Como mencionamos anteriormente, el Agente SQL Server desempeña un papel crucial en el funcionamiento de CDC. Debemos asegurarnos de que el agente está funcionando y habilitado y de que los jobs estén funcionando correctamente.

En Resumen

En resumen, Change Data Capture es el mejor amigo de las aplicaciones ETL. Nos proporciona un flujo constante de datos modificados, listo para ser consumido por nuestras ETL. ¡Es como si CDC nos diera un pase VIP al backstage de la base de datos! Pero como DBAs tenemos que vigilarlo y tener mucho cuidado con el espacio que consume y con sus jobs.

Espero que este artículo te haya resultado útil e interesante. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.