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

Artículo
10/26/2023

Se aplica a:SQL Server Azure 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

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

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 un esquema o un usuario de base de datos denominado cdc existe actualmente una base de datos, no se puede habilitar para la captura de datos modificados hasta que el esquema y/o el usuario se quiten 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. La captura de datos modificados es una subcarpeta que contiene las plantillas

Deshabilitación para 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 la CDC para tablas individuales antes de deshabilitar la CDC para la base de datos. Cuando se deshabilita la CDC para la base de datos, se quitan todos los metadatos de la captura de datos modificados asociados, incluidos el usuario de cdc, el esquema y los trabajos de captura de datos modificados. Sin embargo, los roles de acceso creados por CDC no se quitarán automáticamente y se deben eliminar explícitamente. Para determinar si una base de datos tiene 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 se ha habilitado ya para la captura de datos modificados, examine 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:

Columns in the source table to be captured.

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 demás 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 quiere usar un rol de acceso, debe 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 denomina anexando 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.

Vea la plantilla Enable a Table for All and Net Changes Queries para obtener un ejemplo que muestre 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ó compatibilidad con las consultas net changes cuando se configuró la captura de datos modificados. 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.

Deshabilitación 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 mediante el procedimiento almacenado sys.sp_cdc_disable_tablee. To determine whether a source table is currently enabled for change data capture, examine the **is_tracked_by_cdc** column in the de 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 quita una tabla habilitada para la captura de datos modificados, se quitarán automáticamente los metadatos de la captura de datos modificados que están asociados a la tabla.

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