Change Data Capture (CDC) es una característica que
viene con el motor de base de datos en SQL Server 2008. Permite capturar las
inserciones, actualizaciones y borrados que se ejecutan en una o varias tablas
de una base de datos. Los cambios que se realizan en la tabla se almacenan en
otras tablas respetando una estructura relacional, lo que facilita el consumo
por las aplicaciones.
Las inserciones y eliminaciones, actualizan la tabla
de cambio refleja esas acciones con un nuevo registro que guarda cuando se
inserta o se elimina. En el caso de una actualización, la tabla de cambio
almacena dos filas: una para con los datos de antes de la actualización y otra
para los nuevos datos modificados.
Se puede utilizar cualquiera de los CDC para devolver
todos los cambios en una fila, o simplemente para devolver el último cambio.
Por ejemplo, si una columna, en una fila, se cambiaron dos veces, se puede ver
tanto los 2 cambios que se produjeron o sólo el contenido final de la fila.
Al habilitar el CDC para una tabla, las tablas nuevas
y las funciones se agregan a la base de datos para que se almacenen los cambios
que se hacen de los datos. Además, se crean dos trabajos en el SQL Agent. Uno
para rellenar las tablas donde se almacenan los cambios y otro para limpiar las
tablas de cambios. Estos trabajos son fundamentales para el proceso de CDC, por
lo que SQL Agent debe estar arrancado para que los CDC puedan ser activados.
El primer paso para habilitar Change Data Capture
(CDC), habilitar la base de datos, se realiza mediante la ejecución del
procedimiento sys.sp_cdc_enable_db que se encuentra dentro de la base de datos
a habilitar, por ejemplo:
USE TuBD
GO
EXEC
sys.sp_cdc_enable_db
GO
Ahora que la base de datos está habilitada para Change
Data Capture (CDC), el siguiente paso es que para cada tabla (origen) de la
cual queremos seguir los cambios que se hagan sobre ella. A yo solo pongo que
se haga para una sola, se puede fácilmente repetir este mismo proceso para cada
tabla que se deseé en una misma base de datos. Esto se hace utilizando el
procedimiento almacenado sys.sp_cdc_enable_table:
USE TuBD
GO
EXEC sys.sp_cdc_enable_table
@Source_schema =
N'dbo,
@Source_name = N'Tutabla,
@Role_name = N'CDCRole '
Los parámetros son los siguientes:
*
@Source_schema - es el nombre de esquema de la tabla de origen.
*
@Source_name @ - es el nombre de la tabla de origen está habilitando.
* @Role_name
- es la función de seguridad que se crea cuando los CDC está activado. Este rol
puede ser ignorado, o se puede utilizar para asignar permisos a usuarios
específicos, para que puedan acceder a los datos utilizando las funciones de
los CDC (http://technet.microsoft.com/es-es/library/bb510744.aspx), sin tener
que ser un miembro de la función db_owner.
La tabla Dónde se guardan las columnas cuyo cambio de
valor estamos capturando es: cdc_captured_columns,
(http://technet.microsoft.com/es-es/library/bb500243%28SQL.100%29.aspx), sería
suficiente con hacer:
Select * from cdc.captured_columns
Devuelve una fila para cada columna de la que se ha
realizado un seguimiento en una instancia de captura. De forma predeterminada,
se capturan todas las columnas de la tabla de origen. Sin embargo, se podrán
incluir o excluir columnas si la tabla de origen está habilitada para la
captura de datos de cambios especificando una lista de
columnas.(http://technet.microsoft.com/es-es/library/bb510701%28SQL.100%29.aspx)
Para Devolver sólo los registros que contienen los
valores antes y después de una modificación, se puede obtener de la forma:
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Tutabla');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Tutabla(@from_lsn, @to_lsn, 'all');
Lo que estamos haciendo es devolver mediante las
funciones fn_cdc_get_min_lsn() y fn_cdc_getmax_lsn() los registros mínimo y
máximo que se están guardando en la tabla cdc.lsn_time_mapping. Esta tabla, que
se crea en el momento que se activa el CDC, almacena los valores LSN (Log
Secuence Number) que se generan como consecuencia de cada transacción que
contiene filas de datos en una tabla de cambios, así como el instante de tiempo
en el que se produjo la transacción.
Más información os dejo el link: http://msdn.microsoft.com/en-us/library/bb522489.aspx
Hay un
blog muy interesante (pero en ingles) en donde construyen una solución desde 0, copio el enlace:
http://sqlblog.com/blogs/andy_leonard/archive/2008/02/09/introducing-change-data-capture-ssis-and-sql-server-2008-ctp5-nov-2007.aspx
En SQL
Server existen diferentes opciones pueden permitir tener un registro de los
cambios a la información de las tablas, dependiendo de tus necesidades puedes
evaluar alguna de las siguientes opciones:
- Auditorías de SQL (Nueva en SQL 2008)
- C2 Audit Mode (disponible desde SQL 2000)
- SQL Traces (Disponibles desde SQL 2000)
- Change Tracking (Nueva en SQL 2008)
- Change Data Capture (Nueva en SQL 2008)
- Código personalizado (triggers, columnas adicionales en las tablas, código
adicional en los sp’s, etc.)
- Herramientas de terceros
Fuentes:
Microsoft,
msdn.
Apunte
y recopilación por Norman M. Pardell
Puedes
consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea
posible.
