Change Data Capture y el modo de recuperación BULK LOGGED

En SQL Server 2008 aparecen entre otras muchas novedades, la posibilidad de realizar monitorización de cambios de datos en tablas (a nivel de columnas si queremos). Esta tecnología se denomina Change Data Capture (sus siglas CDC) y viene a mejorar y facilitar las labores relacionadas con la LOPD, ya que nos permite de una forma cómoda y sencilla realizar un seguimiento de cambios de nuestros datos de forma automática.

Hasta la llegada de SQL Server 2008, solo teníamos la opción de realizar esto de forma manual de la siguiente manera:

• Crear una tabla con el mismo esquema que la tabla a auditar (mismas columnas y mismos tipos de datos).
  
• Crear Triggers para inserción, modificación y borrado sobre la tabla a auditar, que se encargarían de replicar los cambios sobre la tabla LOPD.
  

Este proceso, aparte de ser obviamente costoso puesto que requería de un proceso de codificación y mantenimiento elevado (alguien lo tenía que acabar programando), tenía un gran impacto en el rendimiento de la BBDD puesto que implicaba que cada modificación de datos desencadenaría un trigger y un INSERT en alguna tabla.

Para solucionar esta problemática aparece entonces CDC en SQL Server 2008 ya que minimiza el impacto en el sistema utilizando la infraestructura del agente de lectura del log de transacciones existente de forma que los cambios auditados aparecen de forma asíncrona en las tablas de auditoría.

El título de este post es referente a una de las preguntas que se nos realizó durante el evento de SQL Server 2008 Jumpstart en Microsoft Ibérica hace unos días. Uno de los asistentes planteó la duda de si esta infraestructura funcionaría cuando estábamos utilizando un modo de recuperación BULK LOGGED. En ese momento no pudimos responder mediante un ejemplo práctico puesto que teníamos un tiempo limitado para realizar las demostraciones, pero algún asistente comentó la posibilidad de que no funcionaria en ese escenario ya que al fin y al cabo lo que se hace es leer del log de transacciones y "en una etapa BULK LOGGED lo que se realiza mediante carga masiva no queda registrado en el log de transacciones".

Por un lado hemos de apuntar que el comportamiento de modificaciones INSERT, UPDATE y DELETE evidentemente funcionara en cualquier tipo de configuración de modo de recuperación (FULL, SIMPLE o BULK LOGGED), puesto que en todos los modos se escribe todo absolutamente en el log de transacciones (otra cosa es que se hace con esos datos escritos). La duda estaba en el modo de recuperación BULK LOGGED ante cargas masivas (BULK INSERT) puesto que en la demo se mostró el funcionamiento de CDC en un entorno configurado con modo de recuperación SIMPLE.

Antes de nada, voy a explicar cómo nos podemos beneficiar del modo de recuperación BULK-LOGGED.

Como alguien comentó, el modo de recuperación BULK-LOGGED no registra en el log de transacciones las modificaciones realizadas mediante una carga masiva…pero esto no es del todo cierto siempre, puesto que solo ocurre en determinadas condiciones.

Las condiciones que han de cumplirse para logging mínimo son:

• La base de datos ha de estar obviamente con modo de recuperación BULK-LOGGED
  
• La tabla que va a sufrir la actualización masiva no ha de estar replicada
  
• Se utiliza TABLOCK (bloqueo de tabla) en la actualización masiva
  
• La tabla destino ha de cumplir con los requisitos de datos e indexación de la tabla siguiente:
  

  		Clustered Index		Nonclustered Index
  		Si	No	Si	No
  TABLA	Vacía	Minimo	Minimo	Minimo	Minimo
  	Con Datos	Completo	Minimo	Index	Minimo

  
  

Dicho esto, os comento ahora que internamente CDC utiliza un "log reader agent" , al igual que sucede en replicación transaccional. Dicho de otro modo…que aunque en los Books Online no diga nada al respecto porque todavía no están completos (cuando escribo esto estamos en CTP6 todavía), seguro que aparece un punto más, diciendo que la tabla que va a sufrir la modificación masiva, no puede estar siendo auditada mediante CDC para poder tener logging mínimo ante modificaciones masivas ;)

Dicho esto, me monté el entorno de pruebas pertinente y comprobé si es cierto según lo que acabo de comentar, que CDC sí que funciona ante cargas masivas aún cuando estamos en modo de recuperación BULK-LOGGED. No es el momento de que os exponga aquí los scripts con los que lo he probado, porque en un futuro post ya se subirán los scripts del Jumpstart donde aparecerá entre otros el de CDC; pero como a veces una imagen vale más que mil palabras, aquí tenéis el screenshot que demuestra que CDC funciona con una operación bulk insert sobre una tabla auditada y una base de datos en modo de recuperación BULK LOGGED.

Lo que veis en el screenshot son los cambios en los datos ocurridos en la tabla dbo.Employee desde el principio de los tiempos (solo le hice unas pocas modificaciones ;). Lo que veis marcado en rojo con operación = 2 (INSERT) con los números LSN acabados en 7A002, 7A003 y 7A004 es el resultado de una carga masiva de 3 filas en la tabla, precisamente con los datos que veis (employeeid, lastname y firstname)

Evidentemente, que funcione CDC ante una carga masiva teniendo el modo de recuperación Bulk-logged quiere decir que no estamos ahorrándonos escrituras en el log de transacciones, por lo que tendrás que tenerlo en cuenta si sigues una política de cambios a bulk-logged para mejorar el rendimiento de algunas cargas masivas cuando las realices sobre tablas auditadas mediante CDC.

También comento que hice la misma prueba con una importación de un millón de registros pero los resultados no los pongo aquí puesto que aparece una pequeña incongruencia propia de la versión beta que es, que se sale del ámbito del post…pero probado está ;).