Seguimiento de los cambios de datos (SQL Server)

SQL Server 2014 proporciona dos características que realizan un 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 se pueden habilitar en la misma base de datos; no se requieren consideraciones especiales. Para las ediciones de SQL Server que admiten la captura de datos modificados y el seguimiento de cambios, vea Características compatibles con las ediciones de SQL Server 2014.

Ventajas del uso de la captura de datos modificados o 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 un seguimiento de los cambios en una base de datos, en lugar de desarrollar una solución personalizada, tiene las siguientes ventajas:

• Se reduce el tiempo de desarrollo. Dado que la funcionalidad está disponible en SQL Server 2014, no es necesario desarrollar una solución personalizada.

• Los cambios de esquema no son necesarios. No es necesario agregar columnas, agregar desencadenadores ni crear una tabla lateral en la que realizar un seguimiento de las filas eliminadas o almacenar información de seguimiento de cambios si no se pueden agregar columnas 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 se requiere la limpieza personalizada de los datos almacenados en una tabla lateral.

• Se proporcionan funciones para obtener información de cambio.

• Hay poca sobrecarga para las operaciones DML. El seguimiento de cambios sincrónico siempre tendrá cierta sobrecarga. Sin embargo, el uso del seguimiento de cambios puede ayudar a minimizar la sobrecarga. La sobrecarga suele ser menor que la del uso de soluciones alternativas, especialmente las soluciones que requieren los desencadenadores de uso.

• El seguimiento de cambios se basa en transacciones confirmadas. El orden de los cambios se basa en el tiempo de confirmación de la transacción. Esto permite obtener resultados confiables cuando hay transacciones superpuestas y de larga duración. Las soluciones personalizadas que usan timestamp valores deben diseñarse específicamente para controlar estos escenarios.

• Las herramientas estándar están disponibles que puede usar para configurar y administrar. SQL Server 2014 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

En la tabla siguiente se enumeran 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 cambios del registro de transacciones para que los cambios estén disponibles después de la operación DML. En el seguimiento de cambios, el mecanismo de seguimiento implica un seguimiento sincrónico de los cambios en línea con las operaciones DML para que la información de cambios esté disponible inmediatamente.

Captura de datos modificados

La captura de datos modificados proporciona información histórica de cambios para una tabla de usuario mediante la captura del hecho de que se realizaron cambios de DML y los datos reales que se cambiaron. Los cambios se capturan mediante un proceso asincrónico que lee el registro de transacciones y tiene un impacto bajo en el sistema.

Como se muestra en la ilustración siguiente, los cambios realizados 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 modificadosque SQL Server proporciona permiten que los datos modificados se consuman de forma sencilla y sistemática.

Modelo de seguridad

En esta sección se describe el modelo de seguridad de captura de datos modificados.

Configuración y administración
Para habilitar o deshabilitar la captura de datos modificados para una base de datos, el autor de la llamada 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 . Habilitar y deshabilitar la captura de datos modificados en el nivel de tabla requiere que el autor de la llamada de sys.sp_cdc_enable_table (Transact-SQL) y sys.sp_cdc_disable_table (Transact-SQL) sea miembro del rol sysadmin o miembro 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 está restringido a los miembros del rol de servidor sysadmin y a los miembros del rol database db_owner.

Cambiar consultas de enumeración y metadatos
Para obtener acceso a los datos modificados asociados a una instancia de captura, se debe conceder al usuario acceso selecto 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 la llamada también debe ser miembro del rol de acceso especificado. Otras funciones generales de captura de cambios de datos para obtener acceso a los metadatos serán accesibles para todos los usuarios de la base de datos a través del rol público, aunque el acceso a los metadatos devueltos también se limitará normalmente mediante el uso de acceso selecto a las tablas de origen subyacentes y la pertenencia a cualquier rol de acceso definido.

Operaciones de DDL para tablas de origen habilitadas para captura de cambios de datos
Cuando una tabla está habilitada para la captura de datos modificados, las operaciones DDL solo se pueden aplicar a la tabla mediante un miembro del rol fijo de servidor sysadmin, un miembro de database role db_owner o un miembro de database role db_ddladmin. Los usuarios que tienen concesiones explícitas para realizar operaciones DDL en la tabla recibirán el error 22914 si prueban esta operación.

Consideraciones sobre el tipo de datos para la captura de datos modificados

Todos los tipos de columna base son compatibles con la captura de datos modificados. En la tabla siguiente se enumeran el comportamiento y las limitaciones de varios tipos de columna.

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

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

• Creación de reflejo de la base de datos

• Replicación transaccional

• Restauración o asociación de bases de datos

Reflejo de la base de datos

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

1. Asegúrese de que el Agente de SQL Server está funcionando en el servidor reflejado.

2. Cree el trabajo de captura y el trabajo de limpieza en el espejo después de que el servidor principal haya realizado la conmutación al espejo. Para crear los trabajos, use el procedimiento almacenado sys.sp_cdc_add_job (Transact-SQL).

Para obtener más información sobre la creación de reflejo de la base de datos, consulte Creación de reflejo de la 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 controla de forma diferente cuando ambas características están habilitadas. La captura de datos modificados y la replicación transaccional siempre usan el mismo procedimiento, sp_replcmds, para leer los cambios del registro de transacciones. Cuando la captura de datos modificados está habilitada por sí 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 lectura de registros 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, consulte Agente lector del registro 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, la tarea de captura llama a sp_replcmds. La base de datos está habilitada para la replicación transaccional y se crea una publicación. Ahora, se crea el Agente del Lector de Registro para la base de datos y se elimina la tarea de captura. El Agente lector de registros continúa escaneando el registro desde el último número de secuencia de registro que se confirmó en la tabla de cambios. Esto garantiza 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 se vuelve a crear el trabajo de captura.

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

SQL Server usa la siguiente lógica para determinar si la captura de datos modificados permanece habilitada después de restaurar o adjuntar una base de datos:

• Si se restaura una base de datos en el mismo servidor con el mismo nombre de base de datos, la captura de datos modificados permanece habilitada.

• Si se restaura una base de datos en otro servidor, de forma predeterminada se deshabilita la captura de datos modificados y se eliminan todos los metadatos relacionados.

  Para conservar la captura de datos modificados, use la KEEP_CDC opción al restaurar la base de datos. Para obtener más información sobre esta opción, consulte RESTORE.

• Si una base de datos está desasociada y adjunta al mismo servidor u otro servidor, la captura de datos modificados permanece habilitada.

• Si se adjunta o restaura una base de datos con la KEEP_CDC opción a 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 de una base de datos restaurada o adjunta.

Seguimiento de cambios

El seguimiento de cambios captura el hecho de que se cambiaron las filas de una tabla, pero no captura los datos que se cambiaron. 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 aquellas aplicaciones que no requieren la información histórica, hay mucho menos sobrecarga de almacenamiento debido a que los datos modificados no se capturan. Se usa un mecanismo de seguimiento sincrónico para realizar el seguimiento de los cambios. Esto se ha diseñado para tener una sobrecarga mínima para las operaciones DML.

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

Servicios de sincronización y seguimiento de cambios para ADO.NET

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

Para obtener más información sobre el seguimiento de cambios y los servicios de sincronización para ADO.NET, use los vínculos siguientes:

• Acerca del seguimiento de cambios (SQL Server)

  Describe el seguimiento de cambios, proporciona información general de alto nivel sobre cómo funciona el seguimiento de cambios y describe cómo interactúa el seguimiento de cambios con otras características del motor de base de datos de SQL Server.

• Centro para desarrolladores de Microsoft Sync Framework

  Proporciona documentación completa para Sync Framework y Sync Services. En la documentación de Sync Services, el tema "How to: Use SQL Server Change Tracking" contiene información detallada y ejemplos de código.

Tareas relacionadas (obligatorias)

Véase también

Funciones de captura de cambios de datos (Transact-SQL)
Funciones de seguimiento de cambios (Transact-SQL)
Procedimientos almacenados de captura de datos de cambios (Transact-SQL)
Tablas de captura de datos de cambios (Transact-SQL)
Vistas de administración dinámica relacionadas con la captura de datos modificados (Transact-SQL)