Como parte de un proyecto de Datos Masivos, frecuentemente se nos pide encontrar la mejor manera de rastrear los cambios aplicados a las tabas de la base de datos, de tal manera que ningún requerimiento es creado para cargar todas las enormes tablas a la base de datos de almacén de datos al final del día, si no fueron cambiados todos los datos.
La primera opción disponible en SQL Server para rastrear los cambios son los desencadenadores After Insert, After Update y After Delete, que requieren esfuerzos de codificación de su parte para manejar estos cambios o añadir datos, en adición al impacto en el desempeño de los desencadenadores en el sistema.
Cambiar la Captura de Datos
Otra solución de rastreo y captura introducida en SQL Server 2008 Enterprise, que rastrea cualquier operación Insert, Update o Delete aplicadas a las tablas de usuario, con Qué, Cuándo y Dónde se aplicaron estos cambios, sin ningún esfuerzo de codificación extra y manteniéndolo en las tablas del sistema de manera que sea de fácil acceso usando consultas normales. Esta nueva característica se llama SQL Server Change Data Capture, o CDC.
Cuando Change Data Capture es habilitado en una tabla de usuario, una nueva tabla de sistema será creada con la misma estructura de la tabla fuente, con columnas extra para incluir los cambios de meta datos.
SQL Server Change Data Capture usa el registro de transacciones de SQL Server como la fuente de los datos cambiados usando un mecanismo de captura asíncrono. Cualquier cambio DML aplicado a la tabla rastreada será escrito al registro de transacciones. El proceso de captura CDC lee estos registros y los copian a la tabla de captura y finamente añade la información de cambios asociados como metadatos de los cambios a la misma tabla.
A continuación, tendremos una pequeña demostración mostrando cómo configurar el CDC en una de las tablas de SQLShackDemo. Como recomienda Microsoft, crearemos un grupo de archivos separados y un archivo de base de datos para hospedar las tablas de cambios de Change Data Capture:
Primero crearemos un nuevo Grupo de Archivos usando la sentencia ALTER DATABASE ADD FILEGROUP:
|
1 2 3 4 |
USE [master] GO ALTER DATABASE [SQLShackDemo] ADD FILEGROUP [CDC] GO |
Una vez que el Grupo de Archivos es creado, un nuevo archivo de base de datos será creado en este Grupo de Archivos usando la sentencia ALTER DATABASE ADD FILE SQL:
|
1 2 |
ALTER DATABASE [SQLShackDemo] ADD FILE ( NAME = N'SQLShackDemo_cdc', FILENAME = N'D:\CDC File\SQLShackDemo_cdc.ndf' , SIZE = 2048MB , FILEGROWTH = 128MB ) TO FILEGROUP [CDC] GO |
Ya que la tabla de sistema cdc.lsn_time_mapping crecerá a un tamaño significativo y tendrá muchas operaciones I/O debido a los cambios en la tabla, es también recomendado cambiar el Grupo de Archivos por defecto para la base de datos antes de ejecutar sys.sp_cdc_enble_db contra el Grupo de Archivos CDC creado previamente, y cambiarlo de vuelta al Grupo de Archivos Primario una vez que las tablas de metadatos son creadas.
Para establecer el Grupo de Archivos CDC como el Grupo de Archivos por defecto, usaremos la sentencia MODIFY FILEGROUP:
|
1 2 3 4 |
USE [SQLShackDemo] GO ALTER DATABASE [SQLShackDemo] MODIFY FILEGROUP [CDC] DEFAULT GO |
Para habilitar Change Data Capture en las tablas de las que necesita rastrear y capturar los cambios DML, usted necesita primero habilitarlo al nivel de la base de datos. Esto puede ser hecho ejecutando el procedimiento almacenado de sistema sys.sp_cdc_enable_db como sigue:
|
1 2 3 4 |
USE [SQLShackDemo] GO EXEC sys.sp_cdc_enable_db GO |
Podemos cambiar de vuelta el Grupo de Archivos por defecto ahora al grupo de archivos PRIMARY como sigue:
|
1 2 3 4 |
USE [SQLShackDemo] GO ALTER DATABASE [SQLShackDemo] MODIFY FILEGROUP [PRIMARY] DEFAULT GO |
Para asegurarnos de que CDC está habilitado en la base de datos SQLShackDemo, consultaremos la tabla sys.databases como sigue:
|
1 2 3 4 5 6 |
USE master GO SELECT [name], database_id, is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1 GO |
Una vez que CDC está habilitado a nivel de la base de datos, un nuevo esquema será creado en esa base de datos con el nombre “CDC”:
También, nuevas tablas de sistema serán creadas debajo del esquema CDC:
El rol de estas tablas es como indica cada nombre de tabla. La tabla cdc.captured_columns contiene la lista de columnas capturadas, cdc.change_tables contiene una lista de tablas de bases de datos con CDC habilitado. La tabla cdc.ddl_history contiene el historial de los cambios DDL aplicados en la tabla rastreada. La tabla cdc.index_columns contiene los índices de las tablas rastreadas, y cdc.lsn_time_mapping , que mapea el número LSN.
Ahora habilitaremos Change Data Capture al nivel de la tabla. Dado que CDC es una característica a nivel de tabla, usted necesita habilitarlo en cada tabla que necesita rastrear y de la cual capturará cambios DML.
Para habilitar CDC en la tabla AWBuildVersion desde la base de datos SQLShackDemo, ejecutaremos el procedimiento almacenado de sistema sys.sp_cdc_enable_table:
|
1 2 3 4 5 6 7 |
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AWBuildVersion', @role_name = NULL, @filegroup_name = N'CDC', @supports_net_changes = 0 GO |
Es mejor limitar el número de columnas a ser capturadas por CDC a sólo las que realmente necesita rastrear. Usted puede usar el parámetro @captured_column_list del procedimiento almacenado sys.sp_cdc_enable_table system para especificar la lista de columnas que serán incluidas en la tabla de cambios para ser rastreada.
Una vez que Change Data Capture está habilitado a nivel de tabla, una nueva instancia de captura asociada para las tablas fuente es creada para soportar la propagación de los cambios de la tabla fuente. Esta instancia de captura contiene la tabla de cambios. Por defecto, el nombre de esta tabla de cambios es SchemaName_SourceTableName_CT.
Para asegurarnos de que CDC está habilitado en nuestra tabla exitosamente, consultaremos sys.tables para encontrar la propiedad is_tracket_by_cdc:
|
1 2 3 4 |
SELECT [name], is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc=1 GO |
También usted puede revisar las tablas rastreadas consultando [cdc].[change_tables]
La tabla CDC con el mismo resultado como sigue:
Y la lista de columnas capturadas en la tabla puede ser vista consultando la tabla CDC [cdc].[captured_columns]. El resultado será como sigue:
Usted debería asegurarse de que SQL Server Agent Service está habilitado antes de habilitar CDC al nivel de la tabla, ya que CDC creará dos nuevos trabajos de SQL Server para cada base de datos con CDC habilitado:
El trabajo de captura correrá el procedimiento almacenado de sistema sys.sp_MScdc_capture_job para capturar los cambios, y el trabajo de limpieza llamará al procedimiento almacenado sys.sp_MScdc_cleanup_job para limpiar la tabla de cambios.
El periodo de retención por defecto es tres días, esto significa que los datos serán mantenidos en la tabla de cambios por tres días antes de removerlos. Usted puede sobrescribir este valor ejecutando el procedimiento almacenado de sistema sys.sp_cdc_change_job especificando un nuevo valor de retención en minutos:
|
1 2 3 |
EXECUTE sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 2880; |
Ahora hemos alcanzado el punto donde Change Data Capture está habilitado en la tabla AWBuildVersion de la base de datos SQLShackDemo. Esto significa que cualquier cambio que sea aplicado a esa tabla será capturado y escrito en la tabla de captura CDC. Si usted realiza la operación INSERT, entonces el nuevo valor después de la operación INSERT será escrito en la tabla de capturas como un registro. SI usted realiza una operación DELETE, el valor antes de la operación DELETE será escrito en la tabla de capturas como un registro. Para cualquier operación UPDATE realizada en la tabla rastreada, dos registros será escritos en la tabla de capturas, uno para el valor antes de UPDATE y uno para el valor después de UPDATE.
La columna _$operation de la tabla de cambios CDC contiene el tipo de operación DML, donde 1 indica una operación DELETE, 2 indica una operación INSERT, 3 indica el valor antes del proceso de actualización y 4 el valor después del proceso de actualización.
Por ejemplo, si aplicamos la siguiente sentencia de inserción en la base de datos SQLShackDemo, e intentamos consultar la tabla de cambios dbo_AWBuildVersion_CT, encontraremos un nuevo registro escrito mostrando el nuevo valor insertado:
|
1 2 3 4 5 6 7 8 9 |
USE [SQLShackDemo] GO INSERT INTO [dbo].[AWBuildVersion] ([Database Version] ,[VersionDate] ,[ModifiedDate]) VALUES ('11.0.2100.60','2012-03-14 00:00:00.000','2016-03-02 00:00:00.000') GO |
Por otro lado, si aplicamos la siguiente consulta de actualización en la misma tabla, dos nuevos registros serán escritos en la tabla de capturas mostrando el valor antes de la actualización con _$operation =3 y el valor después de la actualización con _$operation =4:
|
1 2 3 |
UPDATE [SQLShackDemo].[dbo].[AWBuildVersion] SET [Database Version] ='11.0.2100.80' WHERE [SystemInformationID]=5 |
Es bueno saber que habilitar Change Data Capture en su tabla de la base de datos SQL Server no evitará que usted aplique cualquier cambio DDL en esa tabla. Pero, ¿será este nuevo cambio reflejado a la tabla de cambios CDC? La respuesta depende del tipo de cambio; si usted cambia el tipo de dato de una columna de tabla con CDC habilitado, el nuevo tipo de datos será reflejado a la tabla de cambios y el proceso de rastreo y captura no será afectado.
Si usted elimina una tabla con CDC habilitado, valores NULL serán insertados para esa columna para cada entrada en la tabla de cambios. Pero si usted añade una nueva columna a la tabla con CDC habilitado, este cambio no será reflejado a la tabla de cambios y ningún cambio en esta columna será capturado.
Añadamos una nueva columna a nuestra tabla AWBuildVersion que tiene CDC habilitado:
|
1 2 3 |
ALTER TABLE dbo.AWBuildVersion ADD TS datetime NULL GO |
Para verificar que este cambio sea reflejado en la tabla de cambios CDC, consultaremos la tabla de sistema cdc.captured_columns, que desafortunadamente muestra que el cambio no es reflejado a la tabla de cambios CDC:
Y si intentamos insertar un nuevo valor a nuestra tabla usando la sentencia de inserción siguiente, la nueva columna no será mostrada:
|
1 2 3 4 5 6 7 8 9 10 |
USE [SQLShackDemo] GO INSERT INTO [dbo].[AWBuildVersion] ([Database Version] ,[VersionDate] ,[ModifiedDate] ,[TS]) VALUES ('11.0.2100.80','2016-01-01 00:00:00.000','2016-03-02 00:00:00.000',Getdate()) GO |
Como puede ver, CDC continuará rastreando los cambios aplicados en la tabla pero ignorará la nueva columna añadida.
Para superar este problema sin perder los datos capturados antiguos, podemos crear una nueva instancia de captura para la misma tabla fuente, donde una nueva tabla de cambios será creada, asociada con la nueva instancia de captura, luego copiar los datos de cambios desde la tabla de cambios antigua a la nueva y finalmente deshabilitar la instancia de captura antigua. Usted puede crear hasta 2 instancias de captura asociadas con la misma tabla fuente al mismo tiempo.
Para deshabilitar CDC al nivel de la base de datos, usted debería deshabilitarlo en todas las tablas con CDC, luego deshabilitarlo al nivel de la base de datos como sigue:
|
1 2 3 4 5 6 7 8 9 10 |
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'AWBuildVersion', @capture_instance = N'dbo_AWBuildVersion' GO USE SQLShackDemo GO EXEC sys.sp_cdc_disable_db GO |
Rastreo de Cambios
Otra solución de rastreo liviana introducida en SQL Server 2008 es Change Tracking o CT. Lo que lo hace liviano es que sólo captura la fila que es cambiada en la tabla, sin capturar los datos que son cambiados o manteniendo los datos históricos para el valor antes del cambio, con una carga de trabajo mínima. CT funciona en todas las ediciones de SQL Server como Express, Workgroup, Web, Standard, Enterprise y DataCenter.
Change Tracking usa un mecanismo de rastreo síncrono para rastrear los cambios de tablas. La única información provista por CT acerca de la tabla rastreada es la clave primaria de los registros cambiados. Para obtener los nuevos datos después del cambio, se requiere codificar del lado de la aplicación para unir la tabla fuente con la tabla de rastreo usando el valor de clave primaria.
Igual que con CDC, para habilitar Change Tracking al nivel de la tabla, usted debería habilitarla primero a nivel de la base de datos:
|
1 2 3 |
ALTER DATABASE SQLShackDemo SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) |
La información de rastreo de cambios que es más antigua que el valor de Retention Period especificado previamente será removida automáticamente. La opción AUTO_CLEANUP es usada para habilitar o deshabilitar la tarea de limpieza que elimina la antigua información de CT.
Para asegurarse que CT está habilitado al nivel de la base de datos, usted puede navegar la opción Change Tracking en la ventana de propiedades de la base de datos. Usted también puede habilitar CT desde aquí:
De nuevo, CT es una característica al nivel de tablas, usted tiene que habilitarlo en cada tabla donde necesite rastrear y capturar sus cambios DML. Para habilitar CT a nivel de tabla, la tabla debería tener la restricción de la clave primaria definida previamente. Si usted trata de habilitar CT en una tabla sin clave primaria, usted obtendrá el siguiente error:
Cannot enable change tracking on table ‘XXX’. Change tracking requires a primary key on the table. Create a primary key on the table before enabling change tracking.
Habilitar CT a nivel de tabla se logra corriendo ALTER TABLE ENABLE CHANGE_TRACKING.
|
1 2 3 4 5 |
USE SQLShackDemo GO ALTER TABLE CountryInfo ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) |
O usando SQL Server Management Studio, desde la ventana Table Properties:
Por ejemplo, si aplicamos la siguiente sentencia de inserción a nuestra tabla, nos encontraremos con que el valor de la versión actual de rastreo es cambiado de 0 a 1:
|
1 2 3 4 5 6 7 8 |
USE [SQLShackDemo] GO INSERT INTO [dbo].[CountryInfo] ([CountyCode]) VALUES ('AMM') GO |
Para obtener el valor que es cambiado, consultaremos la tabla de sistema CHANGETABLE que retornará la clave primaria para el valor insertado:
|
1 2 |
SELECT * FROM CHANGETABLE (CHANGES CountryInfo,0) as CT ORDER BY SYS_CHANGE_VERSION |
Para obtener el registro cambiado complete, es fácil unir CHANGETABLE con la tabla fuente:
|
1 2 3 4 |
SELECT CI.* FROM CHANGETABLE (CHANGES CountryInfo,0) as CT JOIN CountryInfo CI on CT.CountyCode=CI.CountyCode |
De manera opuesta a CDC, usted puede aplicar cualquier cambio DDL en la tabla fuente sin afectar a CT excepto por los cambios en la clave primaria que fallarán a menos que desactive CT en esa tabla.
Si usted trata de cambiar la clave primaria de la tabla CountryInfo con CT habilitado en ella, usted obtendrá el siguiente error:
The primary key constraint ‘PK_CountryInfo_1’ on table ‘CountryInfo’ cannot be dropped because change tracking is enabled on the table. Change tracking requires a primary key constraint on the table. Disable change tracking before dropping the constraint.
Para deshabilitar CT a nivel de la base de datos, usted debería deshabilitarlo en todas las tablas en que habilite CT, luego deshabilitarlo a nivel de la base de datos como sigue:
|
1 2 3 4 5 6 |
ALTER TABLE [dbo].CountryInfo DISABLE CHANGE_TRACKING GO ALTER DATABASE SQLShackDemo SET CHANGE_TRACKING = OFF GO |
Conclusión
Como puede ver, hay muchas opciones disponibles para rastrear y capturar los cambios realizados en su base de datos. La elección de una opción apropiada depende de sus requerimientos. Usted debería comprometerse entre el desempeño del sistema, la carga IO y el espacio de almacenamiento disponible para decidir cuál de los métodos mencionados usará. Es mejor probar estos métodos en su ambiente DEV aplicando una gran carga, de manera que decida si se aplica a su situación o no.
Enlaces Útiles
- sys.sp_cdc_enable_table
- Tuning the Performance of Change Data Capture in SQL Server 2008
- Enable and Disable Change Data Capture
- About Change Tracking
- Comparing Change Data Capture and Change Tracking
- Enable and Disable Change Tracking
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
- Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
- Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019