Habilitación y deshabilitación de la captura de datos modificados

Se aplica a:SQL ServerAzure SQL Managed Instance

En este artículo se describe cómo habilitar y deshabilitar la captura de datos modificados (CDC) para una base de datos y una tabla para SQL Server y Azure SQL Managed Instance. Para Azure SQL Database, consulta CDC con Azure SQL Database.

Permisos

Se requieren permisos sysadmin para habilitar o deshabilitar la captura de datos modificados de SQL Server y Azure SQL Managed Instance.

Habilitación para una base de datos

Para poder crear una instancia de captura para tablas individuales, debes habilitar la base de datos para la captura de datos modificados.

Para habilitar la captura de datos modificados, ejecute el procedimiento almacenado sys.sp_cdc_enable_db (Transact-SQL) en el contexto de la base de datos. Para determinar si una base de datos ya tiene la CDC habilitada, consulta la columna is_cdc_enabled en la vista de catálogo sys.databases.

Cuando una base de datos tiene la captura de datos modificados habilitada, el esquema cdc, el usuario cdc, las tablas de metadatos y otros objetos de sistema se crean para la base de datos. El esquema cdc contiene las tablas de metadatos de la captura de datos modificados y, una vez que las tablas de origen han sido habilitadas para esta captura, también contiene las tablas de cambios individuales que sirven como repositorio de los datos de cambios. Este esquema cdc también contiene las funciones de sistema asociadas que se usan para consultar los datos modificados.

La captura de datos modificados requiere el uso exclusivo del esquema cdc y del usuario cdc . Si en una base de datos existe actualmente un esquema o un usuario de base de datos denominado cdc, no se puede habilitar la captura de datos modificados para la base de datos hasta que se elimine el esquema o el usuario, o se cambie su nombre.

-- ====
-- Enable Database for CDC
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO

Nota:

Para buscar las plantillas relacionadas con CDC en SQL Server Management Studio, ve a Ver, selecciona Explorador de plantillas y, a continuación, Plantillas de SQL Server. Change data capture es una subcarpeta que contiene las plantillas

Deshabilitar una base de datos

Use sys.sp_cdc_disable_db (Transact-SQL) en el contexto de la base de datos para deshabilitar la captura de datos modificados para una base de datos. No es necesario deshabilitar CDC para tablas individuales antes de deshabilitar CDC para la base de datos. Al deshabilitar CDC para la base de datos, se eliminan todos los metadatos asociados a la captura de datos de cambios, incluidos el usuario cdc, el esquema y los trabajos de captura de datos de cambios. Sin embargo, los roles de control de acceso creados por CDC no se eliminarán automáticamente y deben eliminarse explícitamente. Para determinar si una base de datos tiene habilitado CDC, consulta la columna is_cdc_enabled en la vista de catálogo sys.databases.

Si se quita una base de datos habilitada para la CDC, se quitarán automáticamente los trabajos de captura de datos modificados.

-- Disable Database for change data capture
USE MyDB
GO
EXEC sys.sp_cdc_disable_db
GO

Habilitación para una tabla

Una vez se haya habilitado una base de datos para la captura de datos modificados, los miembros del rol fijo de base de datos db_owner pueden crear una instancia de captura para tablas de origen individuales mediante el procedimiento almacenado sys.sp_cdc_enable_table. Para determinar si una tabla de origen ya se ha habilitado para la captura de datos de cambios, consulte la columna is_tracked_by_cdc en la vista de catálogo sys.tables.

Importante

Para obtener más información sobre los argumentos del procedimiento almacenado sys.sp_cdc_enable_table, consulta sys.sp_cdc_enable_table (Transact-SQL).

Se pueden especificar las opciones siguientes al crear una instancia de captura:

Columnas en la tabla fuente que se capturarán.

De forma predeterminada, todas las columnas de la tabla de origen se identifican como columnas capturadas. Si solo es necesario realizar el seguimiento de un subconjunto de las columnas, por ejemplo, por motivos de privacidad o de rendimiento, use el parámetro @captured_column_list para especificar el subconjunto de columnas.

Un grupo de archivos para contener la tabla de cambio.

De forma predeterminada, la tabla de cambios se encuentra en el grupo de archivos predeterminado de la base de datos. Los propietarios de base de datos que quieran controlar la ubicación de las tablas de cambios individuales pueden usar el parámetro filegroup_name para especificar un grupo de archivos determinado para la tabla de cambios asociada a la instancia de captura. El grupo de archivos con nombre debe existir previamente. Generalmente, se recomienda que las tablas de cambios se coloquen en un grupo de archivos independiente de las tablas de origen. Consulte la plantilla Habilitar una tabla que especifique un opción de grupo de archivos para ver un ejemplo que muestra el uso del parámetro @filegroup_name .

-- Enable CDC for a table specifying filegroup
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @filegroup_name = N'MyDB_CT',
    @supports_net_changes = 1
GO

Un rol para controlar el acceso a una tabla de cambio.

La finalidad del rol con nombre es controlar el acceso a los datos de cambios. El rol especificado puede ser un rol fijo de servidor existente o un rol de base de datos. Si el rol especificado todavía no existe, se crea automáticamente un rol de base de datos con ese nombre. Los usuarios deben tener el permiso SELECT en todas las columnas capturadas de la tabla de origen. Además, cuando se especifica un rol, los usuarios que no sean miembros del rol sysadmin o db_owner también deben ser miembros del rol especificado.

Si no quieres usar un rol de acceso, debes establecer explícitamente el parámetro @role_name en NULL. Vea la plantilla Enable a Table Without Using a Gating Role para obtener un ejemplo de cómo habilitar una tabla sin un rol de acceso.

-- Enable CDC for a table using a gating role option
USE MyDB
GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = NULL,
    @supports_net_changes = 1
GO

Una función para consultar los cambios netos.

Una instancia de captura siempre incluye una función con valores de tabla (TVF) para devolver todas las entradas de la tabla de cambios que se produjeron dentro de un intervalo definido. Esta función se nombra agregando el nombre de la instancia de captura a `cdc.fn_cdc_get_all_changes_``. Para obtener más información, consulte cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Si el parámetro supports_net_changes está establecido en 1, también se genera una función de cambios netos para la instancia de captura. Esta función devuelve solo un cambio por cada fila distinta que haya cambiado en el intervalo especificado en la llamada. Para obtener más información, consulte cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Para poder usar las consultas net changes, la tabla de origen debe tener una clave principal o un índice único que identifique las filas de forma única. Si se usa un índice único, el nombre del índice se debe especificar con el parámetro @index_name . Las columnas definidas en la clave principal o índice único deben estar incluidas en la lista de columnas de origen que se van a capturar.

Consulte la plantilla Habilitar una tabla para consultas de todos los cambios y cambios netos para ver un ejemplo que demuestra la creación de una instancia de captura con ambas funciones de consulta.

-- Enable CDC for a table for all and net changes queries
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @supports_net_changes = 1
GO

Nota:

Si la captura de datos modificados está habilitada en una tabla con una clave principal existente y el parámetro @index_name no se usa para identificar un índice único alternativo, la característica de captura de datos modificados usará la clave principal. Los cambios subsiguientes en la clave principal no se permiten sin deshabilitar primero la captura de datos modificados para la tabla. Esto es así independientemente de si se solicitó la posibilidad de realizar consultas de cambios netos cuando se configuró la captura de datos de cambios. Si no hay ninguna clave principal en una tabla en el momento en que se habilita para la captura de datos modificados, la captura de datos modificados omite la incorporación posterior de una clave principal. Dado que la captura de datos modificados no utilizará ninguna clave principal que se cree una vez habilitada la tabla, las columnas de clave y la clave se pueden quitar sin restricciones.

Desactivar para una tabla

Los miembros del rol fijo de base de datos db_owner pueden quitar una instancia de captura de las tablas de origen individuales utilizando el procedimiento almacenado sys.sp_cdc_disable_table. Para determinar si una tabla de origen está habilitada actualmente para la captura de datos modificados, examine la columna is_tracked_by_cdc en la vista de catálogo sys.tables. Si no hay ninguna tabla habilitada para la base de datos después de que tenga lugar la deshabilitación, también se quitarán los trabajos de captura de datos modificados.

Si se elimina una tabla con la captura de datos modificados habilitada, los metadatos de captura de datos modificados asociados a la tabla se eliminan automáticamente.

Vea la plantilla Deshabilitar una instancia de captura para una tabla si desea obtener un ejemplo de deshabilitación de una tabla.

-- Disable a Capture Instance for a table
USE MyDB
GO
    EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @capture_instance = N'dbo_MyTable'
GO

Contenido relacionado

  • Last updated on