Acerca de la captura de datos modificados (SQL Server)

La captura de cambios en los datos registra la actividad de insertar, actualizar y eliminar que se aplica a una tabla de SQL Server. Esto hace que los detalles de los cambios estén disponibles en un formato relacional fácilmente consumido. La información de columna y los metadatos necesarios para aplicar los cambios a un entorno de destino se capturan para las filas modificadas y se almacenan en las tablas de cambios que reflejan la estructura de columna de las tablas de origen de las que se realiza un seguimiento. Las funciones con valores de tabla se proporcionan para permitir el acceso sistemático a los datos modificados por los consumidores.

Un buen ejemplo de un consumidor de datos dirigido por esta tecnología es una aplicación de extracción, transformación y carga (ETL). Una aplicación ETL carga incrementalmente los datos modificados de las tablas de origen de SQL Server en un almacenamiento de datos o data mart. Aunque la representación de las tablas de origen dentro del almacenamiento de datos debe reflejar los cambios en las tablas de origen, una tecnología de un extremo a otro que actualice una réplica del origen no es adecuada. En su lugar, necesita un flujo de datos modificados confiable y estructurado de forma que los consumidores puedan aplicarlo a representaciones de destino dispares de los datos. La captura de datos modificados de SQL Server proporciona esta tecnología.

Flujo de datos de Change Data Capture

En la ilustración siguiente se muestra el flujo de datos principal para la captura de datos modificados.

El origen de datos modificados para la captura de datos modificados es el registro de transacciones de SQL Server. 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. Esto lee el registro y agrega información sobre los cambios en la tabla de cambios asociada de la tabla de 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.

Descripción de la captura de datos modificados y la instancia de captura

Antes de que se pueda realizar un seguimiento de los cambios en las tablas individuales de una base de datos, la captura de datos modificados debe habilitarse explícitamente para la base de datos. Esto se hace mediante el procedimiento almacenado sys.sp_cdc_enable_db. Cuando la base de datos está habilitada, las tablas de origen se pueden identificar como tablas de seguimiento mediante el procedimiento almacenado sys.sp_cdc_enable_table. Cuando se habilita una tabla para la captura de datos modificados, se crea una instancia de captura asociada para admitir la difusió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 tablas de metadatos de captura de datos modificados cdc.change_tables, cdc.index_columnsy cdc.captured_columns. Esta información se puede recuperar mediante el procedimiento almacenado sys.sp_cdc_help_change_data_capture.

Todos los objetos asociados a una instancia de captura se crean en el esquema de captura de datos modificados de la base de datos habilitada. Los requisitos para el nombre de la instancia de captura son que son un nombre de objeto válido y que son únicos en las instancias de captura de base de datos. De forma predeterminada, el nombre es <nombre del esquema_nombre de tabla> de la tabla de origen. Para denominar su tabla de cambios asociada, se anexa _CT al nombre de la instancia de captura. La función que se utiliza para consultar todos los cambios se nombra anteponiendo fn_cdc_get_all_changes_ al nombre de la instancia de captura. Si la instancia de captura está configurada para admitir net changes, la net_changes función de consulta también se crea y se denomina mediante fn_cdc_get_net_changes_ pendientes al nombre de la instancia de captura.

Cambiar tabla

Las cinco primeras columnas de una tabla de cambios de captura de datos modificados son columnas de metadatos. Proporcionan información adicional relevante para el cambio registrado. Las columnas restantes reflejan las columnas capturadas identificadas de la tabla de origen en nombre y, normalmente, en el tipo . Estas columnas contienen los datos de columna capturados que se recopilan de la tabla de origen.

Cada operación de inserción o eliminación que se aplica a una tabla de origen aparece como una sola fila dentro de la tabla de cambios. Las columnas de datos de la fila resultante de una operación de inserción contienen los valores de columna después de la inserción. Las columnas de datos de la fila resultante de una operación de eliminación contienen los valores de columna antes de la eliminación. Una operación de actualización requiere una entrada de fila para identificar los valores de columna antes de la actualización y una segunda entrada de fila para identificar los valores de columna después de la actualización.

Cada fila de una tabla de cambios también contiene metadatos adicionales para permitir la interpretación de la actividad de cambio. La columna __$start_lsn identifica el número de secuencia de registro de confirmación (LSN) asignado al cambio. El LSN de confirmación identifica los cambios confirmados en la misma transacción y ordena esas transacciones. La columna __$seqval se puede usar para ordenar más cambios que se producen en la misma transacción. La columna __$operation registra la operación que está asociada al cambio: 1 = eliminación, 2 = inserción, 3 = actualización (antes de la imagen) y 4 = actualización (después de la imagen). La columna __$update_mask es una máscara de bits variable con un bit definido para cada columna capturada. Para las entradas de inserción y eliminación, la máscara de actualización siempre tendrá todos los bits establecidos. Sin embargo, las filas de actualización solo tendrán esos bits establecidos que corresponden a columnas modificadas.

Intervalo de validez de captura de datos modificados para una base de datos

El intervalo de validez de la captura de datos modificados para una base de datos es el tiempo durante el que los datos modificados están disponibles para las instancias de captura. El intervalo de validez comienza cuando se crea la primera instancia de captura para una tabla de base de datos y continúa hasta la hora actual.

Los datos que se depositan en las tablas de cambios crecerán hasta llegar a ser incontrolables si no se reduce su número de forma periódica y sistemática. El proceso de limpieza de captura de datos modificados es responsable de aplicar la directiva de limpieza basada en retención. En primer lugar, mueve el extremo inferior del intervalo de validez de tiempo para satisfacer la restricción de tiempo. A continuación, quita las entradas de la tabla de cambios expiradas. De forma predeterminada, se conservan tres días de datos.

En el extremo superior, a medida que el proceso de captura confirma cada nuevo lote de datos modificados, se agregan nuevas entradas a cdc.lsn_time_mapping para cada transacción que tiene entradas de tabla de cambios. En la tabla de asignación, se conservan tanto un número de secuencia de registro de confirmación (LSN) como una hora de confirmación de transacción (columnas start_lsn y tran_end_time, respectivamente). El valor máximo de LSN que se encuentra en cdc.lsn_time_mapping representa el nivel máximo del intervalo de validez de la base de datos. Su tiempo de confirmación correspondiente se usa como base a partir de la cual la limpieza basada en retención calcula una nueva marca de agua baja.

Dado que el proceso de captura extrae datos modificados del registro de transacciones, hay una latencia integrada entre el momento en que se confirma un cambio en una tabla de origen y la hora en que el cambio aparece dentro de su tabla de cambios asociada. Aunque esta latencia suele ser pequeña, es importante recordar que los datos modificados no están disponibles hasta que el proceso de captura haya procesado las entradas de registro relacionadas.

Intervalo de validez de captura de cambios de datos para una instancia de captura

Aunque es habitual que el intervalo de validez de la base de datos y el intervalo de validez de la instancia de captura individual coincidan, esto no siempre es cierto. El intervalo de validez de la instancia de captura se inicia cuando el proceso de captura reconoce la instancia de captura y comienza a registrar los cambios asociados en su tabla de cambios. Como resultado, si las instancias de captura se crean en momentos diferentes, cada una tendrá inicialmente un punto de conexión bajo diferente. La columna start_lsn del conjunto de resultados devuelto por sys.sp_cdc_help_change_data_capture muestra el límite inferior actual para cada instancia de captura definida. Cuando el proceso de limpieza limpia las entradas de la tabla de cambios, ajusta los valores de start_lsn para todas las instancias de captura para reflejar el nuevo umbral mínimo para los datos de cambio disponibles. Solo se ajustan las instancias de captura que tienen valores de start_lsn que son actualmente inferiores a la nueva marca de agua baja. Con el tiempo, si no se crean nuevas instancias de captura, los intervalos de validez de todas las instancias individuales tienden a coincidir con el intervalo de validez de la base de datos.

El intervalo de validez es importante para los consumidores de datos modificados porque el intervalo de extracción de una solicitud debe estar totalmente cubierto por el intervalo de validez de captura de datos modificados actual para la instancia de captura. Si el punto de conexión bajo del intervalo de extracción está a la izquierda del punto de conexión bajo del intervalo de validez, podrían faltar datos de cambio debido a una limpieza agresiva. Si el extremo alto del intervalo de extracción está a la derecha del extremo alto del intervalo de validez, el proceso de captura aún no se ha procesado a través del período de tiempo representado por el intervalo de extracción y también podrían faltar datos modificados.

La función sys.fn_cdc_get_min_lsn se usa para recuperar el LSN mínimo actual para una instancia de captura, mientras que sys.fn_cdc_get_max_lsn se usa para recuperar el valor LSN máximo actual. Al consultar datos modificados, si el intervalo LSN especificado no se encuentra dentro de estos dos valores LSN, se producirá un error en las funciones de consulta de captura de datos modificados.

Control de cambios en tablas de origen

Adaptarse a los cambios de columna en las tablas de origen que se están rastreando es un problema difícil para los consumidores posteriores. Aunque habilitar la captura de datos modificados en una tabla de origen no impide que se produzcan cambios de DDL, la captura de datos modificados ayuda a mitigar el efecto en los consumidores al permitir que los conjuntos de resultados entregados que se devuelven a través de la API permanezcan sin cambios incluso cuando cambia la estructura de columna de la tabla de origen subyacente. Esta estructura de columna fija también se refleja en la tabla de cambios subyacente a la que acceden las funciones de consulta definidas.

Para dar cabida a una tabla de cambios de estructura de columna fija, el proceso de captura responsable de rellenar la tabla de cambios omitirá las nuevas columnas que no se identifican para la captura cuando se habilitó la tabla de origen para la captura de datos modificados. Si se quita una columna de seguimiento, se proporcionarán valores NULL para la columna en las entradas de cambio posteriores. Sin embargo, si una columna existente sufre un cambio en su tipo de datos, el cambio se propaga a la tabla de cambios para asegurarse de que el mecanismo de captura no introduce la pérdida de datos en las columnas de seguimiento. El proceso de captura también publica los cambios detectados en la estructura de columnas de las tablas con seguimiento en la tabla cdc.ddl_history. Los consumidores que deseen recibir alertas sobre los ajustes que podrían tener que realizarse en aplicaciones posteriores, utilicen el procedimiento almacenado sys.sp_cdc_get_ddl_history.

Normalmente, la instancia de captura actual seguirá conservando su forma cuando se apliquen cambios DDL a su tabla de origen asociada. Sin embargo, es posible crear una segunda instancia de captura para la tabla que refleje la nueva estructura de columnas. Esto permite que el proceso de captura realice cambios en la misma tabla de origen en dos tablas de cambios distintas que tengan dos estructuras de columna diferentes. Por lo tanto, aunque una tabla de cambios puede seguir alimentando programas operativos actuales, la segunda puede impulsar un entorno de desarrollo que intenta incorporar los nuevos datos de columna. Permitir que el mecanismo de captura rellene ambas tablas de cambios en conjunto significa que se puede realizar una transición de uno a otro sin pérdida de datos modificados. Esto puede ocurrir cada vez que las dos escalas de tiempo de captura de datos modificados se superponen. Cuando se aplica la transición, se puede quitar la instancia de captura obsoleta.

Relación entre el trabajo de captura y el lector de registros de replicación transaccional

La lógica del proceso de captura de datos modificados se inserta en el procedimiento almacenado sp_replcmds, una función de servidor interna creada como parte de sqlservr.exe y también se usa en la replicación transaccional para recopilar los cambios del registro de transacciones. Cuando la captura de datos modificados solo está habilitada para una base de datos, se crea el trabajo de captura de datos modificados del Agente SQL Server como vehículo para invocar sp_replcmds. Cuando la replicación también está presente, solo se usa el lector de registros transaccional para satisfacer las necesidades de datos modificados para ambos consumidores. Esta estrategia reduce significativamente la contención de registros cuando la replicación y la captura de datos modificados están habilitadas para la misma base de datos.

El cambio entre estos dos modos operativos para capturar datos modificados se produce automáticamente cada vez que se produce un cambio en el estado de replicación de una base de datos habilitada para la captura de datos modificados.

La tarea principal del proceso de captura consiste en examinar el registro y escribir datos de columna e información relacionada con transacciones en las tablas de captura de datos de cambios. Para garantizar un límite transaccionalmente coherente en todas las tablas de captura de cambios de datos que rellena, el proceso de captura abre y confirma su propia transacción en cada ciclo de escaneo. Detecta cuándo las tablas se habilitan recientemente para la captura de datos modificados y las incluye automáticamente en el conjunto de tablas que se supervisan activamente en busca de entradas de cambio en el registro. Del mismo modo, también se detectará la deshabilitación de la captura de datos modificados, lo que hará que la tabla de origen se quite del conjunto de tablas supervisada activamente para los datos modificados. Cuando finaliza el procesamiento de una sección del registro, el proceso de captura indica la lógica de truncamiento del registro del servidor, que usa esta información para identificar las entradas de registro aptas para truncarse.

El proceso de captura también se usa para mantener el historial de los cambios de DDL en las tablas con seguimiento. Las instrucciones DDL asociadas a la captura de datos modificados realizan entradas en el registro de transacciones de la base de datos cada vez que se quita una base de datos habilitada para captura de datos modificados o se quitan columnas de una tabla habilitada para captura de datos modificados. Estas entradas de registro se procesan mediante el proceso de captura, que luego envía los eventos DDL asociados a la tabla cdc.ddl_history. Puede obtener información sobre los eventos DDL que afectan a las tablas con seguimiento mediante el procedimiento almacenado sys.sp_cdc_get_ddl_history.

Agente de Captura de Cambios en los Datos - Tareas

Normalmente, dos trabajos del Agente SQL Server están asociados a una base de datos habilitada para la captura de datos modificados: una que se usa para rellenar las tablas de cambios de la base de datos y otra que es responsable de la limpieza de tablas de cambios. Ambos trabajos constan de un solo paso que ejecuta un comando Transact-SQL. El comando Transact-SQL que se invoca es un procedimiento almacenado definido de captura de datos modificados que implementa la lógica del trabajo. Los trabajos se crean cuando la primera tabla de la base de datos está habilitada para la captura de datos modificados. El trabajo de limpieza siempre se crea. El trabajo de captura solo se creará si no hay ninguna publicación transaccional definida para la base de datos. El trabajo de captura también se crea cuando la captura de datos modificados y la replicación transaccional están habilitadas para una base de datos, y se quita el trabajo de lectura de registros transaccional porque la base de datos ya no tiene publicaciones definidas.

Los trabajos de captura y limpieza se crean mediante parámetros predeterminados. El trabajo de captura se inicia inmediatamente. Se ejecuta continuamente, procesando un máximo de 1000 transacciones por ciclo de examen con una espera de 5 segundos entre ciclos. El trabajo de limpieza se ejecuta diariamente a las 2:00 a. m. Conserva las entradas de tabla de cambios durante 4320 minutos o 3 días, quitando un máximo de 5000 entradas con una sola instrucción delete.

Los trabajos del agente de captura de cambios de datos se eliminan cuando la captura de cambios de datos está deshabilitada para una base de datos. El trabajo de captura también se puede quitar cuando se agrega la primera publicación a una base de datos y se habilitan la captura de datos modificados y la replicación transaccional.

Internamente, los trabajos del agente de captura de datos modificados se crean y quitan mediante los procedimientos almacenados sys.sp_cdc_add_job y sys.sp_cdc_drop_job, respectivamente. Estos procedimientos almacenados también se exponen para que los administradores puedan controlar la creación y eliminación de estos trabajos.

Un administrador no tiene ningún control explícito sobre la configuración predeterminada de las tareas del agente de captura de cambios de datos. El procedimiento almacenado sys.sp_cdc_change_job se proporciona para permitir que se modifiquen los parámetros de configuración predeterminados. Además, el procedimiento almacenado sys.sp_cdc_help_jobs permite ver los parámetros de configuración actuales. Tanto el trabajo de captura como el trabajo de limpieza extraen los parámetros de configuración de la tabla msdb.dbo.cdc_jobs al iniciarse. Los cambios realizados en estos valores mediante sys.sp_cdc_change_job no surtirán efecto hasta que se detenga y reinicie el trabajo.

Se proporcionan dos procedimientos almacenados adicionales para permitir que los trabajos del agente de captura de datos modificados se inicien y detengan: sys.sp_cdc_start_job y sys.sp_cdc_stop_job.

Ambos trabajos del Agente SQL Server se diseñaron para ser lo suficientemente flexibles y lo suficientemente configurables para satisfacer las necesidades básicas de los entornos de captura de datos modificados. Sin embargo, en ambos casos, se han expuesto los procedimientos almacenados subyacentes que proporcionan la funcionalidad básica para que sea posible realizar más personalizaciones.

La captura de datos modificados no puede funcionar correctamente cuando el servicio del motor de base de datos o el servicio del agente de SQL Server se ejecuta bajo la cuenta de SERVICIO DE RED. Esto puede dar lugar al error 22832.

Véase también

Seguimiento de cambios de datos (SQL Server)
Habilitar y deshabilitar la captura de datos modificados (SQL Server)
Trabajar con datos modificados (SQL Server)
Administrar y supervisar la captura de datos modificados (SQL Server)