Seguimiento de cambios de datos (SQL Server)

Artículo
11/19/2013

SQL Server 2012 proporciona dos características que realizan el seguimiento de los cambios en los datos de una base de datos: captura de datos modificados y seguimiento de cambios. Estas características permiten a las aplicaciones determinar los cambios de DML (operaciones de inserción, actualización y eliminación) que se realizaron en las tablas de usuario de una base de datos. La captura de datos modificados y el seguimiento de cambios pueden habilitarse en la misma base de datos; no se requiere ninguna consideración especial. Para obtener las ediciones de SQL Server que admiten la captura de datos modificados admiten y el seguimiento de cambios, vea Características compatibles con las ediciones de SQL Server 2012.

Ventajas de utilizar la captura de datos modificados y el seguimiento de cambios

La capacidad para consultar los datos que han cambiado en una base de datos es un requisito importante para conseguir que algunas aplicaciones sean eficaces. Normalmente, para determinar los datos modificados, los desarrolladores de aplicaciones deben implementar un método de seguimiento personalizado en sus aplicaciones utilizando una combinación de desencadenadores, columnas de marca de tiempo y tablas adicionales. La creación de estas aplicaciones implica normalmente mucho trabajo de implementación, provoca actualizaciones del esquema y, a menudo, supone una gran sobrecarga en el rendimiento.

El uso de la captura de datos modificados o el seguimiento de cambios en las aplicaciones para realizar el seguimiento de los cambios efectuados en una base de datos, en lugar de desarrollar una solución personalizada, tiene las ventajas siguientes:

Se reduce el tiempo de desarrollo. Dado que la funcionalidad está disponible en SQL Server 2012, no es necesario desarrollar una solución personalizada.
No se requieren cambios en el esquema. No es necesario agregar columnas, agregar desencadenadores o crear una tabla auxiliar en la que realizar el seguimiento de las filas eliminadas o almacenar la información de seguimiento de cambios si las columnas no se pueden agregar a las tablas de usuario.
Hay un mecanismo de limpieza integrado. La limpieza del seguimiento de cambios se realiza automáticamente en segundo plano. No es necesario realizar la limpieza personalizada de los datos que se almacenan en una tabla auxiliar.
Se proporcionan funciones para obtener información de los cambios.
Se reduce la sobrecarga en las operaciones DML. El seguimiento de cambios sincrónico siempre tendrá cierta sobrecarga. Sin embargo, su uso puede ayudar a reducirla. A menudo, la sobrecarga será menor que si se usan soluciones alternativas, sobre todo si se trata de soluciones que requieren el uso de desencadenadores.
El seguimiento de cambios se basa en transacciones confirmadas. El orden de los cambios se basa en la hora de confirmación de la transacción. Esto permite obtener resultados confiables cuando hay transacciones de ejecución prolongada que se solapan. Las soluciones personalizadas que utilizan valores timestamp deben diseñarse específicamente para administrar estos escenarios.
Hay herramientas estándar disponibles que se pueden utilizar para configurar y administrar. SQL Server 2012 proporciona instrucciones DDL estándar, SQL Server Management Studio, vistas de catálogo y permisos de seguridad.

Diferencias de características entre la captura de datos modificados y el seguimiento de cambios

La tabla siguiente enumera las diferencias de características entre la captura de datos modificados y el seguimiento de cambios. El mecanismo de seguimiento de la captura de datos modificados implica una captura asincrónica de los cambios del registro de transacciones para que estén disponibles después de la operación DML. En el seguimiento de cambios, el mecanismo de seguimiento implica el seguimiento sincrónico de los cambios a la vez que las operaciones DML de modo que la información de los cambios esté disponible inmediatamente.

Característica	Captura de datos modificados	Seguimiento de cambios
Cambios sometidos a seguimiento
Cambios de DML	Sí	Sí
Información sometida a seguimiento
Datos históricos	Sí	No
Si la columna cambió	Sí	Sí
Tipo de DML	Sí	Sí

Captura de datos modificados

La captura de datos modificados proporciona información de los cambios históricos para una tabla de usuario captando tanto el hecho de que se realizaran cambios de DML como los datos reales que se cambiaron. Los cambios se capturan mediante un proceso asincrónico que lee el registro de transacciones y tiene poca repercusión en el sistema.

Como se muestra en la ilustración siguiente, los cambios que se realizaron en las tablas de usuario se capturan en las tablas de cambios correspondientes. Estas tablas de cambios proporcionan una vista histórica de los cambios a lo largo del tiempo. Las funciones de captura de datos modificados que SQL Server proporciona permiten utilizar los datos modificados con facilidad y de forma sistemática.

Ilustración conceptual de la captura de datos modificados

Modelo de seguridad

Esta sección describe el modelo de seguridad de la captura de datos modificados.

Configuración y administración
Para habilitar o deshabilitar la captura de datos modificados en una base de datos, el autor de las llamadas de sys.sp_cdc_enable_db (Transact-SQL) o sys.sp_cdc_disable_db (Transact-SQL) debe ser miembro del rol fijo de servidor sysadmin. Para habilitar y deshabilitar la captura de datos modificados en el nivel de tabla, se requiere que el autor de las llamadas de sys.sp_cdc_enable_table (Transact-SQL) y sys.sp_cdc_disable_table (Transact-SQL) sea miembro del rol sysadmin o del rol de base de datos database db_owner.

El uso de los procedimientos almacenados para admitir la administración de trabajos de captura de datos modificados se restringe a los miembros del rol de servidor sysadmin y a los miembros del rol de base de datos database db_owner.
Enumeración de cambios y consultas de los metadatos
Para obtener acceso a los datos modificados que están asociados a una instancia de captura, se debe conceder al usuario acceso exclusivo a todas las columnas capturadas de la tabla de origen asociada. Además, si se especifica un rol de acceso cuando se crea la instancia de captura, el autor de las llamadas también debe ser miembro del rol de acceso especificado. Otras funciones de captura de datos modificados generales para tener acceso a los metadatos serán accesibles para todos los usuarios de la base de datos a través del rol public, aunque el acceso a los metadatos devueltos también se conseguirá normalmente mediante un acceso exclusivo a las tablas de origen subyacentes y por pertenencia a cualquier rol de acceso definido.
Operaciones DDL para las tablas de origen habilitadas para la captura de datos modificados
Cuando una tabla está habilitada para la captura de datos modificados, solo un miembro del rol fijo de servidor sysadmin o de los roles de base de datos db_owner o db_ddladmin puede aplicarle las operaciones DDL. Los usuarios a los que se les haya permitido explícitamente realizar operaciones DDL en la tabla recibirán el error 22914 si intentan esta operación.

Consideraciones sobre los tipos de datos para la captura de datos modificados

La captura de datos modificados admite todos los tipos de columna base. En la tabla siguiente, se especifican el comportamiento y las limitaciones para varios tipos de columna.

Tipo de columna	Cambios capturados en tablas de cambios	Limitaciones
Columnas dispersas	Sí	No admite la captura de cambios cuando se usa un conjunto de columnas.
Columnas calculadas	No	No se hace un seguimiento de los cambios realizados en columnas calculadas. La columna aparecerá en la tabla de cambios con el tipo adecuado, pero tendrá un valor NULL.
XML	Sí	No se hace un seguimiento de los cambios realizados en elementos XML individuales.
Marca de tiempo	Sí	El tipo de datos de la tabla de cambios se convierte a binario.
Tipos de datos BLOB	Sí	La imagen anterior de la columna BLOB solo se almacena si se cambia la propia columna.

Captura de datos modificados y otras características de SQL Server

En esta sección se describe cómo interactúan las características siguientes con la captura de datos modificados:

Creación de reflejo de la base de datos
Replicación transaccional
Restauración o asociación de base de datos

Creación de reflejo de base de datos

Se puede reflejar una base de datos que está habilitada para la captura de datos modificados. Para asegurarse de que la captura y la limpieza se producen automáticamente en el reflejo, siga estos pasos:

Asegúrese de que el Agente SQL Server se está ejecutando en el reflejo.
Cree el trabajo de captura y el trabajo de limpieza en el reflejo después de que la entidad de seguridad haya conmutado por error al reflejo. Para crear los trabajos, use el procedimiento almacenado sys.sp_cdc_add_job (Transact-SQL).

Para obtener más información acerca de la creación de reflejo de la base de datos, vea Creación de reflejo de base de datos (SQL Server).

Replicación transaccional

La captura de datos modificados y la replicación transaccional pueden coexistir en la misma base de datos, pero el rellenado de las tablas de cambios se trata de forma diferente cuando se habilitan ambas características. La captura de datos modificados y la replicación transaccional siempre utilizan el mismo procedimiento, sp_replcmds, para leer los cambios del registro de transacciones. Cuando la captura de datos modificados se habilita sola, un trabajo del Agente SQL Server llama a sp_replcmds. Cuando ambas características están habilitadas en la misma base de datos, el Agente de registro del LOG llama a sp_replcmds. Este agente rellena las tablas de cambios y las tablas de base de datos de distribución. Para obtener más información, vea Agente de registro del LOG de replicación.

Considere un escenario en el que la captura de datos modificados está habilitada en la base de datos AdventureWorks2012 y se habilitan dos tablas para la captura. Para rellenar las tablas de cambios, el trabajo de captura llama a sp_replcmds. La base de datos se habilita para la replicación transaccional y se crea una publicación. Ahora, el Agente de registro del LOG se crea para la base de datos y se elimina el trabajo de captura. El Agente de registro del LOG continúa examinando registro desde el último número de secuencia de registro que se confirmó en la tabla de cambios. De esta forma se asegura de la coherencia de los datos en las tablas de cambios. Si la replicación transaccional está deshabilitada en esta base de datos, se quita el Agente de registro del LOG y vuelve a recrear el trabajo de captura.

[!NOTA]

Cuando el Agente de registro del LOG se utiliza para la captura de datos modificados y la replicación transaccional, los cambios replicados se escriben primero en la base de datos de distribución. A continuación, los cambios capturados se escriben en las tablas de cambios. Se confirman conjuntamente ambas operaciones. Si hay alguna latencia al escribir en la base de datos de distribución, habrá una latencia correspondiente antes de que los cambios aparezcan en las tablas de cambios.

Restaurar o asociar una base de datos habilitada para la captura de datos modificados

SQL Server utiliza la lógica siguiente para determinar si la captura de datos modificados permanece habilitada una vez restaurada o asociada una base de datos:

Si una base de datos se restaura en el mismo servidor con el mismo nombre, la captura de datos modificados sigue habilitada.
Si una base de datos se restaura en otro servidor, de forma predeterminada la captura de datos modificados está deshabilitada y se eliminan todos los metadatos relacionados.

Para conservar la captura de datos modificados, utilice la opción KEEP_CDC al restaurar la base de datos. Para obtener más información acerca de esta opción, vea RESTORE.
Si una base de datos se desasocia y asocia en el mismo servidor o en otro servidor, la captura de datos modificados sigue estando habilitada.
Si una base de datos se adjunta o restaura con la opción KEEP_CDC en cualquier edición distinta de Enterprise, la operación se bloquea porque la captura de datos modificados requiere SQL Server Enterprise. Se muestra el mensaje de error 932:

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Puede usar sys.sp_cdc_disable_db para quitar la captura de datos modificados desde una base de datos restaurada o asociada.

Icono de flecha usado con el vínculo Volver al principio [Arriba]

Seguimiento de cambios

El seguimiento de cambios captura el hecho de que las filas de una tabla cambiaran, pero no los datos que se modificaron. Esto permite a las aplicaciones determinar las filas que han cambiado con los datos de las filas más recientes obtenidos directamente de las tablas de usuario. Por consiguiente, el seguimiento de cambios está más limitado en las cuestiones históricas que puede responder en comparación con la captura de datos modificados. Sin embargo, para las aplicaciones que no requieren información histórica, hay mucha menos sobrecarga de almacenamiento debido a que los datos modificados no se están capturando. Se utiliza un mecanismo de seguimiento sincrónico para realizar el seguimiento de los cambios. Se ha diseñado de modo que suponga una sobrecarga mínima para las operaciones DML.

La ilustración siguiente muestra un escenario de sincronización que se beneficiaría del uso del seguimiento de cambios. En este escenario, una aplicación requiere la información siguiente: todas las filas de la tabla que se cambiaron desde la última vez que la tabla se sincronizó, y solo los datos de las filas actuales. Dado que para realizar el seguimiento de los cambios se utiliza un mecanismo sincrónico, una aplicación puede realizar una sincronización bidireccional y detectar de forma confiable cualquier conflicto que pudiera haberse producido.

Ilustración conceptual del seguimiento de cambios

Seguimiento de cambios y servicios de sincronización para ADO.NET

Sync Services for ADO.NET permite la sincronización entre bases de datos, proporcionando una API intuitiva y flexible que permite generar aplicaciones destinadas a escenarios para la colaboración y sin conexión. Sync Services for ADO.NET proporciona una API para sincronizar los cambios, pero no realiza el seguimiento de los cambios que se producen en el servidor o en la base de datos del mismo nivel. Puede crear un sistema de seguimiento de cambios personalizado, pero esto introduce normalmente una complejidad significativa y una sobrecarga de rendimiento. Para realizar el seguimiento de cambios en un servidor o una base de datos del mismo nivel, se recomienda usar el seguimiento de cambios en SQL Server 2012 porque es fácil de configurar y proporciona un seguimiento del alto rendimiento.

Para obtener más información sobre el seguimiento de cambios y Sync Services for ADO.NET, utilice los vínculos siguientes:

Acerca del seguimiento de cambios (SQL Server)

Se describe el seguimiento de cambios, se proporciona información general de alto nivel sobre cómo funciona el seguimiento de cambios y se describe cómo interactúa con otras características de Motor de base de datos de SQL Server.
Microsoft Sync Framework Developer Center

Proporciona documentación completa para Sync Framework and Sync Services. En la documentación de Sync Services, el tema "Usar el seguimiento de cambios de SQL Server" contiene información detallada y ejemplos de código.