1.- Introducción
La captura de datos modificados registra la actividad de inserción, actualización y eliminación que se aplica a las tablas de SQL Server. Esto hace que los detalles de estos cambios estén disponibles en un formato relacional de fácil uso. La información de las columnas y los metadatos que se necesitan para aplicar los cambios a un entorno de destino se capturan para las filas modificadas y se almacenan en tablas de cambios que reflejan la estructura de columnas de las tablas de origen sometidas a seguimiento. Se proporcionan funciones con valores de tabla para permitir el acceso sistemático a los datos modificados por los consumidores.
El origen de datos modificados para la captura de datos modificados es el registro de transacciones SQL. A medida que se aplican las inserciones, actualizaciones y eliminaciones a las tablas de origen sometidas a seguimiento, se agregan al registro las entradas que describen esos cambios. El registro actúa como entrada para el proceso de captura de datos modificados. Así, se lee el registro y se agrega información sobre un cambio a la tabla de cambios asociada de la tabla sometida a seguimiento. Se proporcionan funciones para enumerar los cambios que aparecen en las tablas de cambios sobre un intervalo especificado, que devuelven la información en forma de un conjunto de resultados filtrado. Un proceso de aplicación utiliza normalmente el conjunto de resultados filtrado para actualizar una representación del origen en algún entorno externo.
2.- Habilitar CDC en la Base de Datos
Al habilitar una base de datos para CDC se crea en esa base de datos un esquema denominado cdc y un usuario con el mismo nombre, así como las tablas de metadatos necesarios para el funcionamiento.
Usaremos el siguiente procedimiento almacenado:
sys.sp_cdc_enable_db
De esta forma:
USE AdventureWorks;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
A continuación, y tras haber comprobado que el servicio Sql Server Agent está iniciado, será el momento de habilitar las tablas que queremos auditar.
3.- Habilitar CDC en una tabla
Las tablas de origen se pueden identificar como tablas sometidas a seguimiento mediante el uso del procedimiento almacenado sys.sp_cdc_enable_table .
Cuando una tabla se habilita para la captura de datos modificados, se crea una instancia de captura asociada para admitir la diseminación de los datos modificados en la tabla de origen. La instancia de captura está compuesta de una tabla de cambios y de dos funciones de consulta, como máximo. Los metadatos que describen los detalles de configuración de la instancia de captura se conservan en las siguientes tablas de metadatos de captura de datos modificados: cdc.change_tables, cdc.index_columns y cdc.captured_columns.
USE pruebacdc;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’
, @source_name = N’tabla’
, @role_name = null;
GO
Se crean dos trabajos en el Agente, uno para la captura y otro para la limpieza de los datos
4.- Obtener información de los cambios.
Se crean dos funciones para obtener la información de los cambios
fn_cdc_get_all_changes_
Devuelve una fila para cada cambio aplicado a la tabla de origen dentro del intervalo del número de secuencia de registro (LSN) especificado. Si una fila de origen ha tenido muchos cambios durante el intervalo, cada cambio se representa en el conjunto de resultados devuelto. Además de devolver los datos del cambio, cuatro columnas de metadatos proporcionan la información que necesita aplicar los cambios a otro origen de datos. Las opciones de filtrado de filas rigen el contenido de las columnas de metadatos y de las filas devueltas en el conjunto de resultados. Cuando se especifica la opción de filtro de filas ‘all’, cada cambio tiene exactamente una fila para identificar el cambio. Cuando se especifica la opción ‘all update old’, las operaciones de actualización se representan como dos filas: una que contiene los valores de las columnas capturadas antes de la actualización y otra que contiene los valores de las columnas capturadas después de la actualización.
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x0000001C0000014C003E,0×00000021000001650004,’all’)
En el resultado obtenemos las modificaciones, especial atención a la columna operation
1 = eliminar
2 = insertar
3 = actualizar (valor antes de la operación de actualización). Este valor sólo se aplica cuando la opción de filtro de filas ‘all update’ se especifica.
4 = actualizar (valor después de la operación de actualización)
fn_cdc_get_net_changes_
Devuelve una fila de cambio de red para cada fila de origen cambiada dentro del intervalo de LSN especificado. Es decir, cuando una fila de origen tiene varios cambios durante el intervalo de LSN, la función devuelve una fila única que refleja el contenido final de la fila. Por ejemplo, si una transacción inserta una fila en la tabla de origen y una transacción subsiguiente dentro de intervalo de LSN actualiza una o más columnas en esa fila, la función devuelve solo una fila, que incluye los valores de columna actualizados.
Como estas dos funciones utilizan los lsn del archivo de log para marcar un rango de petición de datos, nos podemos ayudar de estas funciones
sys.fn_cdc_get_min_lsn
Devuelve el número de secuencia de registro mínimo (LSN) de la columna start_lsn en la tabla del sistema cdc.change_tables para la instancia de captura especificada. Puede utilizar esta función para devolver el extremo inferior de la escala de tiempo de captura de los datos del cambio para una instancia de captura.
select sys.fn_cdc_get_min_lsn(‘dbo_tabla’);
go
sys.fn_cdc_get_max_lsn
Devuelve el número de secuencia de registro máximo (LSN) de la columna start_lsn en la tabla del sistema de cdc.lsn_time_mapping. Puede utilizar esta función para devolver el extremo alto de la escala de tiempo de captura de los datos del cambio para cualquier instancia de captura.
select sys.fn_cdc_get_max_lsn();
go
sys.fn_cdc_map_time_to_lsn
Devuelve el valor del número de secuencia de registro (LSN) desde la columna start_lsn en la tabla del sistema cdc.lsn_time_mapping para la fecha y hora especificadas. Puede usar esta función para asignar sistemáticamente los intervalos de fecha y hora en el intervalo basado en LSN
sys.fn_cdc_map_time_to_lsn ( ‘<relational_operator>‘ , tracking_time )
<relational_operator> ::=
{ largest less than
| largest less than or equal
| smallest greater than
| smallest greater than or equal
}
select sys.fn_cdc_map_time_to_lsn(‘largest less than’,’2010-05-09 18:20:00.000′);
FORMATO: ‘año-dia-mes’
Obtener bases de datos con cdc habilitado
select name from sys.databases where is_cdc_enabled=1
Obtener las tablas con el seguimiento activado
use pruebacdc
go
select name from sys.tables where is_tracked_by_cdc=1
go
5.- Deshabilitar CDC
sys.sp_cdc_disable_table
Deshabilita la captura de datos modificados para la tabla de origen especificada y la instancia de captura en la base de datos actual. La captura de datos modificados sólo está disponible en las ediciones Enterprise, Developer y Evaluation de SQL Server 2008.
use pruebacdc
go
exec sys.sp_cdc_disable_table ‘dbo’,'tabla’,'all’
sys.sp_cdc_disable_db
Deshabilita la captura de datos modificados en la base de datos actual. La captura de datos modificados solo está disponible en las ediciones Enterprise, Developer y Evaluation de SQL Server 2008.
6. Ejemplo Práctico
–Creamos la base de datos de prueba
CREATE DATABASE [pruebacdc] ON PRIMARY
( NAME = N’pruebacdc’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pruebacdc.mdf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’pruebacdc_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\pruebacdc_log.ldf’ , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
– Creamos la tabla
USE [pruebacdc]
GO
/****** Object: Table [dbo].[tabla] Script Date: 09/05/2010 16:53:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tabla](
[id] [int] IDENTITY(1,1) NOT NULL,
[nombre] [nvarchar](50) NULL,
[apellidos] [nvarchar](50) NULL,
CONSTRAINT [PK_tabla] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
– Activamos CDC a nivel de base de datos
USE pruebacdc;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
– Activamos CDC para la tabla
USE pruebacdc;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’
, @source_name = N’tabla’
, @role_name = null;
GO
– Obtenemos información sobre los metadatos de captura
select * from cdc.change_tables
go
select * from cdc.captured_columns
go
select * from cdc.index_columns
go
–Obtenemos el lsn inicial para tabla
select sys.fn_cdc_get_min_lsn(‘dbo_tabla’);
go
– obtenemos el ultimo lsn de captura
select sys.fn_cdc_get_max_lsn();
go
– lsn de todas las confirmaciones
select * from cdc.lsn_time_mapping
– obtenemos todos los cambios (estará vacio)
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x000000190000034A003A,0x000000190000034A003A,’all’)
– insertamos 2 registros
insert into tabla (nombre,apellidos) VALUES(‘nombre1′,’apellido1′)
insert into tabla (nombre,apellidos) VALUES(‘nombre2′,’apellido2′)
insert into tabla (nombre,apellidos) VALUES(‘nombre3′,’apellido3′)
–Obtenemos el lsn inicial para tabla
select sys.fn_cdc_get_min_lsn(‘dbo_tabla’);
go
– obtenemos el ultimo lsn de captura
select sys.fn_cdc_get_max_lsn();
go
– lsn de todas las confirmaciones
select * from cdc.lsn_time_mapping
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x000000190000034A003A,0x0000001B000003060004,’all’)
– Hacemos actualización
update tabla set nombre=’nombre11′ where id=1;
–Obtenemos el lsn inicial para tabla
select sys.fn_cdc_get_min_lsn(‘dbo_tabla’);
go
– obtenemos el ultimo lsn de captura
select sys.fn_cdc_get_max_lsn();
go
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x000000190000034A003A,0x0000001B000003200004,’all update old’)
go
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x000000190000034A003A,0x0000001B000003200004,’all’)
go
– Miramos la hora. (18:20)
– Esperamos un min. Hacemos un cambio
update tabla set nombre=’nombre22′ where id=2;
– (18:21)
–Obtenemos el lsn inicial para tabla
select sys.fn_cdc_get_min_lsn(‘dbo_tabla’);
go
– obtenemos el ultimo lsn de captura
select sys.fn_cdc_get_max_lsn();
go
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x000000190000034A003A,0x0000001B000003780004,’all’)
go
– Obtenemos el lsn correspondiente a las 18:20
select sys.fn_cdc_map_time_to_lsn(‘largest less than’,’2010-05-09 18:20:00.000′);
– buscamos desde el lsn de las 18:20 hasta el final
select * from cdc.fn_cdc_get_all_changes_dbo_tabla(0x0000001B000003600001,0x0000001B000003780004,’all’)
go
– obtenemos información de las bases de datos con cdc habilitado
select name from sys.databases where is_cdc_enabled=1
– obtenemos info de las tablas con cdc habilitado
use pruebacdc
go
select name from sys.tables where is_tracked_by_cdc=1
go
– deshabilitar cdc en una tabla
use pruebacdc
go
exec sys.sp_cdc_disable_table ‘dbo’,'tabla’,'all’
go
– deshabilitar cdc en la base de datos
use pruebacdc
go
exec sys.sp_cdc_disable_db
go
septiembre 9th, 2010 
admin 
Posted in 