Comparando el seguimiento de cambios para la captura de datos
Al aplicar un método de seguimiento de los cambios en su base de datos, tendrá que decidir entre el control de cambios y los métodos de los CDC. Cada uno de estos métodos permite determinar si ha ocurrido un cambio en los datos. En las secciones siguientes se comparan las ventajas de cada método y describir cómo funciona cada método.
Change Tracking - Seguimiento de Datos (Más información AQUI):
El seguimiento de cambios tiene las siguientes funciones:
- Proporciona la funcionalidad con instrucciones DML.
- Pueden responder a preguntas tales como:
- ¿Qué filas de la tabla han cambiado?
- ¿Qué columnas han cambiado?
- ¿Una fila determinada sido actualizada?
- ¿Hizo un INSERT, UPDATE, o DELETE?
- Opera de forma sincrónica para proporcionar información de los cambios inmediatamente.
- Proporciona una sobrecarga de almacenamiento inferiores a los CDC.
- Proporciona un mecanismo de limpieza integrado.
- Utiliza la confirmación de la transacción para determinar el orden de los cambios.
- Trabaja sin que requiera cambios en el esquema de la tabla o desencadenantes adicionales.
- Debe estar activado en el nivel de base de datos mediante ALTER DATABASE.
La captura de datos tiene las siguientes funciones:
- Proporciona la funcionalidad con instrucciones DML.
- Puede contestar las mismas preguntas que el control de cambios, así como los siguientes:
- ¿Cuáles fueron los cambios intermedios realizados a los datos desde la última sincronización?
- ¿Cuántas veces se ha actualizado una fila desde la última sincronización?
- Utiliza las tablas de cambio para registrar las modificaciones de datos de columnas y los metadatos que se requiere para aplicar los cambios a un entorno de destino.La estructura de columnas de la tabla de cambio refleja la estructura de la tabla de origen.
- Utiliza el registro de transacciones como entrada para la información agregada a las tablas de cambios.
- Funciona de forma asincrónica y los cambios sólo están disponibles después de que la sentencia DML se ha completado.
- Proporciona funciones con valores de tabla para permitir el acceso a los datos de las tablas de cambios.
- Requiere ser habilitar en la base de datos usando sys.sp_cdc_enable_db.
- Requiere SQL Server 2008 Enterprise, Developer o Evaluation Edition.
Antes de configurar el seguimiento de cambios en las tablas, debe habilitar el seguimiento de cambios en la base de datos. Puede usar tanto SSMS o ALTER DATABASE para permitir el seguimiento de cambios en su base de datos.
Nivel de compatibilidad mínimo: Antes de configurar el seguimiento de cambios para una determinada base de datos, debe establecer el nivel de compatibilidad de base de datos para SQLServer 2005 (90) o más alta para esa base de datos.Habilitando Change Tracking para una base de datos
Cuando se habilita el seguimiento de cambios de una base de datos, puede configurar las siguientes opciones:
- Change Tracking: Se establece en True o False (por defecto False) para activar o desactivar el control de cambios.
- Retention Period: Establece en un valor numérico que representa la cantidad mínima de tiempo que los cambios se mantienen. El período de retención por defecto es 2.
- Retention Period Units: Establecer días (por defecto), horas o minutos.
- Auto CleanUp: ON (por defecto) o OFF para activar o desactivar el proceso que elimina la información obsoleta del seguimiento de cambios.
Habilitar Clean-Up automático restablece valores predeterminados: Cada vez que el Auto Clean-Up opción está establecida en ON, el período de retención y unidades de retención de cada periodo se restablece al valor predeterminado de 2 y 2 días, respectivamente.Para habilitar el seguimiento de cambios con SSMS, debe realizar los siguientes pasos:
- En el Explorador de objetos, expanda la carpeta Bases de datos, haga clic en la base de datos donde desea utilizar el control de cambios, y seleccione Propiedades.
- En el panel Seleccionar la pestaña Propiedades de la base, haga clic en Change Tracking.
- En la página de Change Tracking, configurar las opciones apropiadas y haga clic en Aceptar (Más información AQUI).
Se puede habilitar el seguimiento de cambios ejecutando el comando ALTER DATABASE. El siguiente código incluye la sintaxis completa del comando ALTER DATABASE para las opciones relacionadas con el control de cambios:
Puede ejecutar el siguiente comando para habilitar el seguimiento de cambios en la base de datos AdventureWorksDW2008. Los cambios se mantienen durante al menos siete días y el proceso de limpieza automática elimina la información de seguimiento de cambios que se creó hace más de siete días:
ALTER DATABASE AdventureWorksDW2008
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)
Habilitación de Change Tracking para una tabla
Una vez que el control de cambios ha sido habilitada en la base de datos, el comando ALTER TABLE se utiliza para habilitar el seguimiento de cambios en las tablas individuales que desea realizar un seguimiento. Sólo hay dos argumentos en el comando ALTER TABLE que afectan el control de cambios:
- Change_Tracking: Setear en ENABLE (por defecto) o DISABLE para determinar el estado de seguimiento de cambios en la tabla.
- Track_Columns_Updated: Establecido en ON o OFF (por defecto) para determinar si desea mantener una lista de las columnas que se actualizan cuando el comando UPDATE se ejecuta en la tabla.
Puede ejecutar el siguiente código para permitir el control de cambios, incluyendo información sobre los cambios a las columnas individuales que se modifican en la tabla de DimEmployee en la base de datos AdventureWorksDW2008:
ALTER TABLE DimEmployee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
Deshabilitar Change Tracking
Debe deshabilitar el seguimiento de cambios en cada tabla utilizando cualquiera SSMS o ALTER TABLE antes de que pueda desactivar el control de cambios sobre la base de datos.
Para determinar las tablas en la base de datos AdventureWorksDW2008 que tienen seguimiento de cambios habilitado, se puede consultar la vista de catálogosys.change_tracking_tables como se muestra en la figura siguiente (Más información AQUI).
Para deshabilitar el seguimiento de cambios en la tabla DimEmployee, ejecute el siguiente código:
ALTER TABLE DimEmployee
DISABLE CHANGE_TRACKING;
Una vez que haya desactivado el seguimiento de cambios en todas las tablas en la base de datos, puede desactivar el seguimiento de cambios en la base de datos mediante el uso de SSMS o el comando ALTER DATABASE.
El siguiente comando deshabilita el seguimiento de cambios en la base de datosAdventureWorksDW2008:
ALTER DATABASE AdventureWorksDW2008
SET CHANGE_TRACKING = OFF
Entender los efectos adicionales de Change Tracking
Los siguientes comandos pueden comportarse de manera diferente cuando el seguimiento de cambios está habilitado:
- TRUNCATE TABLE: Con éxito pero los registros borrados no se siguen y la versión válida mínima es actualizada, lo que requiere que las aplicaciones sean reiniciadas antes de poder reanudar la sincronización.
- DROP INDEX o ALTER INDEX DISABLE: Falla sólo si la referencia al índice de que aplica la restricción es PRIMARY KEY.
- DROP TABLE: Tiene éxito y elimina toda la información de seguimiento de cambios relativos a la tabla eliminada.
- ALTER TABLE DROP CONSTRAINT: Fracasa si intenta quitar la restricción PRIMARY KEY. Debe deshabilitar el control de cambios antes de que pueda quitar la restricción de PRIMARY KEY en la Tabla.
- ALTER TABLE DROP COLUMN: Tiene éxito, siempre y cuando la columna no forma parte de la restricción PRIMARY KEY. A menos que la aplicación está programada para manejar la columna eliminada, los datos de la columna eliminada aún podría ser devueltos como parte de la información de seguimiento de cambios.
- ALTER TABLE ADD COLUMN: Tiene éxito y comienza a seguir los cambios que se realicen en la nueva columna.
- ALTER TABLE ALTER COLUMN: Exitoso, pero los cambios de tipos de datos en las columnas de clave no primarias no son seguidos.
- ALTER TABLE SWITCH: Falla si uno o ambas tablas tiene seguimiento de cambios habilitado.
Cuando se están desarrollando aplicaciones para sincronizar los datos utilizando el control de cambios, puede utilizar varios comandos T-SQL y vistas de catálogo que se proporcionan en SQL Server 2008.
Las siguientes funciones permiten obtener información de los cambios y administrar el entorno de control de cambios:
- CHANGETABLE: Se utiliza para devolver toda la información para cambiar una tabla mediante la opción de CHANGES , o cambiar la información de una fila específica mediante el uso de la opción VERSION. (Más información AQUI)
- CHANGE_TRACKING_MIN_VALID_VERSION: Devuelve la versión mínima que sea válida para su uso en la obtención de control de cambios cuando se ejecuta la función CHANGETABLE. Debe especificar el ID de objeto de tabla para la tabla de seguimiento como un argumento para el comando. (Más información AQUI)
- CHANGE_TRACKING_CURRENT_VERSION: Obtiene la versión que está asociada con la última transacción confirmada. Usted puede utilizar esta información de la versión cuando se ejecuta la función CHANGETABLE. (Más información AQUI)
- CHANGE_TRACKING_IS_COLUMN_IN_MASK: Interpreta el valor SYS_CHANGE_COLUMNS devuelto por el CHANGETABLE(CHANGES ...) La función permite a su aplicación determinar si la columna que figuran en el argumento está incluido en los valores devueltos para SYS_CHANGE_COLUMNS. (Más información AQUI)
- WITH CHANGE_TRACKING_CONTEXT: Le permite configurar la información de contexto en una columna varbinary (128) con la información de seguimiento de cambios cuando se producen cambios.Esto le permite determinar qué aplicación se inició una actualización. (Más información AQUI)
La función CHANGETABLE (CHANGES ) muestra las siguientes columnas cuando se consulta:
- SYS_CHANGE_VERSION: Muestra la versión del cambio más reciente en la fila.
- SYS_CHANGE_CREATION_VERSION: Muestra el valor de la versión de la última operación INSERT.
- SYS_CHANGE_OPERATION: muestra U para UPDATE, D para DELETE, o I para INSERT.
- SYS_CHANGE_COLUMNS: Muestra las columnas que han cambiado desde la versión básica. Esta columna contiene NULL en las siguientes circunstancias:
- El argumento Track_Columns_Updated está en OFF.
- La operación es una inserción o borrado.
- Todas las columnas que no son de clave principal se actualizaron en la misma operación.
- SYS_CHANGE_CONTEXT: Muestra un contexto para la actualización si está definida. Configurar el contexto como parte de la INSERT, UPDATE, o DELETE con la cláusula WITH.
- <primary key column value>: Muestra el valor de clave principal de la fila.
Una nueva fila se inserta en la tabla DimEmployee para un empleado llamado Jonathan Haas. Su clave de empleado es de 299. La figura siguiente muestra el cambio mediante el uso de la función CHANGETABLE. Observe que el SYS_CHANGE_VERSION es 1 y el SYS_CHANGE_OPERATIONque es para INSERT.
Para el ejemplo siguiente, dos registros se actualizan en la tabla DimEmployee.Jonathan Haas, del empleado 299, se convirtió en un vendedor (la columna SalesPersonFlag se establece en 1). Syed Abbas, empleado 294, ya no es un vendedor, por lo tanto, la columna SalesPersonFlag se establece en 0. La siguiente imagen muestra los resultados de la CHANGETABLE (CHANGES), función que muestra todas las actualizaciones de la primera captura de este punto. Observe que el SYS_CHANGE_VERSION se ha incrementado a 2 para el EmployeeKey 299, pero la operación sigue siendo I. La fila no ha sido sincronizado desde la inserción inicial, por lo que un INSERT todavía tiene que ocurrir cuando la tabla se sincroniza, pero los datos nuevos en SalesPersonFlag = 1 se incluye en la sincronización. Para los empleados 294 la SYS_CHANGE_VERSION está ajustado a 3 (de cada actualización de la tabla de incrementos), el SYS_CHANGE_OPERATION es de U de actualización, pero la SYS_CHANGE_CREATION_VERSION se establece en NULL, porque esta fila se inserto antes de seguimiento de cambios se ha habilitado.
La muestra final para este escenario incluye la supresión del nuevo usuario,Jonathan Haas. Observe en la imagen siguiente que para la fila con EmployeeKey 299 de la SYS_CHANGE_VERSION ha incrementado en 4 y el SYS_CHANGE_OPERATION es ahora D para borrar.
En el segundo conjunto de imágenes, un servidor limpio se usa y el seguimiento de cambios está habilitado en la base de datos, una vez más. Cuando el control de cambios está habilitado en la tabla DimEmployee, el argumento TRACK_COLUMNS_UPDATED está en ON. Una vez más, una fila por JonathanHaas, ahora con un EmployeeKey de 297, se inserta en la base de datos y la consulta en la figura siguiente se ejecuta. Tenga en cuenta que los resultados son los mismos que con la primera serie de consultas.
Una vez más, para la consulta siguiente, dos registros se actualizan en la tabla DimEmployee. Jonathan Haas, del empleado 299, se convirtió en un vendedor (la columna SalesPersonFlag se establece en 1). Syed Abbas, empleado 294, ya no es un vendedor, por lo tanto, la columna SalesPersonFlag se establece en 0. La figura siguiente muestra los resultados de la CHANGETABLE (CHANGES), la función que muestra todas las actualizaciones de la primera captura de este punto. Observe que el SYS_CHANGE_COLUMNS incluye un valor binario que representa la columna que se ha cambiado. Usted debe utilizar la función CHANGE_TRACKING_IS_COLUMN_IN_MASK cuando es necesario interpretar esta información.
Para el último ejemplo, que se muestra en la figura posterior, vemos una vez más que el argumento columnas TRACK_COLUMNS_UPDATED no tiene ningún efecto sobre la instrucción DELETE.
Gestionar Change Tracking
Las vistas de catálogo siguientes muestran el seguimiento de cambios de información de configuración:
- sys.change_tracking_databases: Muestra la siguiente información: (Más información AQUI)
- Database_id: Un campo entero que representa el identificador único de base de datos dentro de la instancia de SQL Server para bases de datos donde se ha habilitado el seguimiento de cambios.
- Is_auto_cleanup_on: Un campo de bit con valor 0 para off y 1 para on.
- Retention_period: Un campo entero que representa la cantidad mínima de tiempo que los cambios realizados se guardan antes de que se eliminen en el proceso de auto-limpieza.
- Retention_period_units_desc: Un campo nvarchar especificando Minutos, Horas o Días (“Minutes”, “Hours”, o “Days”).
- Retention_period_unit: Un campo tinyint pone a 1 para minutos, 2 para horas o 3 para días.
- sys.change_tracking_tables: Muestra la siguiente información: (Más información AQUI)
- Object_id: Un campo entero que representa el ID único de la tabla para las tablas de la base de datos que tienen un diario de cambios. Una tabla donde se encuentra el control de cambios desactivado todavía puede tener un diario de cambios.
- Is_track_columns_updated_on: Un campo de bit con valor 0 para off y 1 para on.
- Begin_version: Un campo bigint que contiene la versión de la base de datos cuando se inició el seguimiento de cambios de la tabla.
- Cleanup_version: Un campo bigint que contiene el número de versión cuando, antes de esta versión, los datos pueden haber sido eliminados por el proceso de auto-limpieza.
- Min_valid_version: Un campo bigint que contiene el número mínimo de versión válida de seguimiento de cambios para cada tabla.
Para permitir que los CDC en una base de datos, se ejecuta el procedimiento almacenado del sistema sys.sp_cdc_enable_db.
El siguiente comando permite a los CDC sobre la base de datos AdventureWorksDW2008:
USE AdventureWorksDW2008R2;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
Cuando habilita CDC para una base, el sistema establece la columna is_cdc_enabled en la vista de catálogo sys.databases a 1 y objetos que requiere CDC para funcionar, tales como una(s) cuenta(s) de usuario(s), las tablas, trabajos, procedimientos almacenados y funciones.
Importante: No se puede permitir CDC sobre bases de datos del sistema.Habilitación de CDC en las tablas (Más información AQUI)
Una vez que han permitido a CDC sobre base de datos, se puede activar para las tablas de la base de datos.
Al habilitar el CDC en una tabla mediante el procedimiento almacenado del sistema sys.sp_cdc_enable_table, se crea una instancia de captura de la tabla de origen.
El procedimiento almacenado de sistema sys.sp_cdc_enable_table incluye los siguientes argumentos:
- source_schema: Define el esquema al que pertenece la tabla de origen.
- source_name: Define el nombre de la tabla de origen. Este nombre debe existir en la base de datos actual y no puede existir en el esquema de los CDC.
- role_name: Define el rol en la base de datos que se utiliza para proporcionar acceso a los datos capturados. Si el rol no existe, SQL Server intenta crearlo. Si el usuario ejecuta el comando no tiene permisos suficientes para crear un rol, toda la operación del procedimiento almacenado produce un error.
Nota Importante: Cuando se ejecuta sys.sp_cdc_enable_table debe proporcionar la source_name source_schema, y los argumentos role_name. El argumento role_name se puede establecer en NULL. - capture_Instance: Define un nombre dado a la instancia que se utiliza para la denominación de la instancia de objetos específicos. Una tabla de origen puede tener un máximo de dos instancias de captura definidas. Si no se especifica capture_instance, el nombre por defecto es schemaname_sourcename. El nombre capture_instance no puede exceder de 100 caracteres.
- supports_net_changes: Indica si la compatibilidad con la consulta de cambios de red se va a habilitar para esta instancia de captura. supports_net_changes es bit con un valor predeterminado de 1 si la tabla tiene una clave principal o un índice único identificado mediante el parámetro @index_name. De lo contrario, el parámetro tiene como valor predeterminado 0. Si es 0, sólo se generan las funciones de compatibilidad para consultar todos los cambios. Si es 1, se generan también las funciones necesarias para consultar los cambios de la red.
- index_name: Define el nombre de un índice único válido en la tabla de origen.
- captured_column_list: Identifica las columnas de origen que se va a capturar. Si este campo es nulo, todas las columnas se incluyen en la tabla de cambios.
- filegroup_name: Define el nombre del grupo de archivos en el que se crea la tabla de cambios. Si este valor es NULL, la tabla de cambio se crea en el grupo de archivos predeterminado. Si filegroup_name se especifica, el nombre del grupo de archivos debe existir en la base de datos actual.
- partition_switch: Establecer en TRUE o FALSE (por defecto) para indicar si el comando SWITCH PARTITION de ALTER DATABASE se puede ejecutar en la tabla de origen cuando CDC está activado.
Las columnas capturadas tienen los siguientes requisitos:
- Usted debe enumerar las columnas incluidas en el campo de clave principal o en el índice único especificado en el argumento index_name.
- Usted debe utilizar comas para separar los nombres de columna.
- Si los nombres de columna incluye una coma incorporado, o si usted quisiera agregar identificadores entre comillas a los nombres de columna, puede utilizar las comillas simples o corchetes en el nombre de columna.
- Usted no puede listar las columnas que se definen con cualquiera de los nuevos tipos de datos introducidos en SQL Server 2008.
Imporante: ¿Cómo SWITCH PARTITION y CDC interactúan? Los cambios de datos que son causadas por un comando SWITCH PARTITION no se refleja en la tabla de cambios. Por ejemplo, si se cambian las filas de la tabla de origen, y en otra tabla, la operación de eliminación no es capturado.Usted puede utilizar el siguiente ejemplo de código para permitir a los CDC en la tabla FactInternetSales en la base de datos AdventureWorksDW2008. Este ejemplo le permite consultar los cambios netos en los datos:
USE AdventureWorksDW2008R2;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'FactInternetSales'
, @role_name = N'cdc_adminR2'
, @capture_instance = N'InternetSalesR2'
, @supports_net_changes = 1
GO
Puede utilizar la función cdc.fn_cdc_get_net_changes verificar la configuración CDC.
Descripción de los permisos CDC
Por defecto, las funciones sysadmin y db_owner tienen todos los permisos a los datos en las tablas de cambios. Cuando la opción role_name se establece en NULL, los CDC están habilitados sin necesidad de utilizar un ROL de entrada.
El ROL de entrada controla el acceso a los datos de la tabla de cambios. Cuando se configura el sistema de CDC de esta manera, cualquier usuario con el permiso SELECT en la tabla de origen se puede acceder a la información en la tabla de cambios.
Cuando se especifica un rol en el argumento role_name, se define un rol de entrada en la tabla de cambio, lo que obliga a todos los usuarios que necesitan acceso a los datos capturados a tener el permiso SELECT en las columnas capturadas en tabla, así como la pertenencia a la rol de acceso para la instancia de captura.
Consulta de los cambios
Puede utilizar cualquiera de las funciónes cdc.fn_cdc_get_all_changes_ <capture_instance> o the cdc.fn_cdc_get_net_changes_<capture_instance> para consultar los datos en la tabla de cambios. Si habilita CDC y establecer el argumento supports_net_changes a 1, las dos funciones estaran disponibles para la instancia de CDC definidos. Si establece el argumento supports_net_changes a 0, o si no se incluye el argumento, sólo la funcion cdc.fn_cdc_get_all_changes_ <capture_instance> está disponible.
Con los cambios de red habilitados, si quieres escribir una consulta que devuelve una fila en el conjunto de resultados para cada fila modificada en Log Sequence Number (LSN) rango incluye sólo el contenido final, incluso si se han producido cambios intermedios, por lo que debe utilizar la función cdc.fn_cdc_get_net_changes_<capture_instance> .
La función cdc.fn_cdc_get_net_changes_ <capture_instance> incluye los siguientes argumentos (Más información AQUI):
- from_lsn: Define el número LSN de partida para crear el rango dentro de las filas que deben ser devueltos. Todas las filas de la tabla de cambio con un valor LSN igual o mayor que el valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite inferior del intervalo de consulta.
- to_lsn: Define el número LSN final a crear el rango de filas devueltas. Todas las filas de la tabla de cambio con un valor LSN menor o igual al valor from_lsn se incluyen en el conjunto de resultados. Esto se refiere como establecimiento del límite superior del intervalo de consulta.
- row_filter: Controla las filas que se devuelven y lo que se muestra en las columnas de metadatos del conjunto de resultados. El argumento row_filter puede tener cualquiera de los siguientes valores:
- All: Devuelve el número LSN de la transacción en la columna _$start_lsn , el valor de la operación que se realiza en la columna de la _$operation, y NULL en la columna _$update_mask.
- All with mask: Devuelve el número LSN de la transacción en la columna_$start_lsn, y el valor de la operación realizada en la columna _$operation. Si la operación UPDATE devuelve un valor de 4, los bits de la columna _$update_mask asociadas con las columnas actualizadas se ponen a 1.
- All with merge: Devuelve el número LSN de la transacción en la columna _ $ start_lsn, y devuelve un valor de 1 en la columna _ $ operation si la fila se ha eliminado y un valor de 5, si necesita un INSERT o UPDATE que se utilizarán para aplicar el cambio. La columna $ update_mask siempre tiene un valor de NULL.
Uso de la opcion All with merge: Esta opción está diseñada para mejorar el rendimiento cuando no es necesario distinguir entre las instrucciones INSERT y UPDATE. Usted debe considerar el uso de esta opción si usted está usando la operación de mezcla disponibles en SQL Server 2008.
- _start_lsn: Muestra el LSN asociado a la acción transacción de confirmación del cambio.
- _$seqval: Muestra el valor de la secuencia para ordenar los cambios que se producen por fila dentro de una transacción. Todas las filas en una transacción tienen el mismo valor de _$start_lsn.
- _$operation: Muestra un valor entero sobre la base de la operación realizada y el parámetro row_filter_option. Cuando se establece la row_filter_option a all o all with mask, esta columna muestra los siguientes valores:
- 1: para las operaciones DELETE
- 2: para las operaciones INSERT
- 4: para operaciones UPDATE
Cuando el parámetro row_filter_option se establece a all with merge, la operación _$operation muestra los siguientes valores: - 1: para las operaciones DELETE
- 5: tanto para operaciones INSERT y UPDATE
- _$update_mask: Muestra una máscara de bits que representan las columnas que están siendo capturados en la tabla de origen. Un 1 en la máscara representa una columna que se ha cambiado. Para las instrucciones INSERT y DELETE, todos los bits se ponen a 1.
- <captured source table columns>: Devuelve cada columna de la tabla de origen que fue capturado.
USE AdventureWorksDW2008R2;
GO
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10)
, @to_lsn binary(10);
-- Set the beginning of the time interval to yesterday to capture all changes.
SET @begin_time = GETDATE() -1;
-- DML statements to produce changes.
UPDATE dbo.FactInternetSales
SET OrderQuantity = 5
WHERE SalesOrderNumber = 'SO43697' AND SalesOrderLineNumber = 1;
UPDATE dbo.FactInternetSales
SET OrderQuantity = 2
WHERE SalesOrderNumber = 'SO43697' AND SalesOrderLineNumber = 1;
DELETE FROM dbo.FactInternetSales
WHERE SalesOrderNumber = 'SO43701'AND SalesOrderLineNumber = 1 ;
-- Set the end of the time interval after changes were completed.
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(
'smallest greater than or equal', @begin_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn(
'largest less than or equal', @end_time);
-- Return the net changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_net_changes_InternetSales(
@from_lsn, @to_lsn, 'all');
Y los resultados:
Si desea escribir una consulta que devuelve filas de todos los cambios que se produjeron durante el período especificado, se debe utilizar la función cdc.fn_cdc_get_all_changes_capture_instance.
Si más de un cambio ha ocurrido en una determinada fila de la tabla de origen, varias filas son devueltas por esta función.
La función cdc.fn_cdc_get_all_changes_capture_instance incluye los siguientes
argumentos (Más información AQUI):
- from_lsn: Define el número LSN de partida para crear el rango dentro de las filas que deben ser devueltos. Todas las filas de la tabla de cambio con un valor LSN igual o mayor que el valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite inferior del intervalo de consulta.
- to_lsn: Define el número LSN final para crear el rango de filas devueltas. Todas las filas de la tabla de cambio con un valor LSN menor o igual al valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite superior del intervalo de consulta.
- row_filter_option: El row_filter_option puede tener cualquiera de los siguientes valores:
- All: Devuelve todos los cambios que se produjeron dentro del rango definido por las opciones from_lsn y to_lsn. Para las filas que se han modificado con la instrucción UPDATE, sólo la fila que contiene los nuevos valores es devuelta.
- All update old: Devuelve todos los cambios que se produjeron dentro del rango definido por las opciones from_lsn y to_lsn. Para las filas que se han modificado con la instrucción UPDATE, tanto en la fila que contiene los valores antes de la actualización y la fila que contiene los valores después de la actualización se devuelven.
la siguiente figura se muestran los resultados de la función cdc.fn_cdc_get_all_changes_InternetSales con los cambios que se produjeron para un idéntico conjunto de resultados que muestra en la siguiente figura. Se pueden comparar estas dos tablas para ver las diferencias entre mostrar los cambios de red y todos los cambios.
Cuando usted está construyendo una aplicación para consultar una tabla de cambios, es posible que desee para definir las filas devueltas en función del tiempo cuando se produjeron los cambios más que en el LSN. Usted puede utilizar la funcion sys.fn_cdc_map_time_to_lsn para determinar el número LSN que se utilizan en la from_lsn y los argumentos de la función dc.fn_cdc_get_all_changes_<capture_instance> o cdc.fn_cdc_get_net_changes_<capture_instance>.
Puede utilizar las siguientes funciones de los CDC al consultar los datos modificados:
- sys.fn_cdc_has_column_changed: Devuelve un 1 o 0 para determinar si la columna identificada por la máscara suministra ha sido actualizada en cualquier fila de cambio asociada. La instancia de captura, nombre de la columna, y los valores de actualización se incluyen como máscara de entrada para esta función (Más información AQUI).
- sys.fn_cdc_increment_lsn: Devuelve el LSN siguiente en la secuencia basada en un valor LSN de entrada. Si usted sabe el valor LSN del límite superior de la consulta anterior, se puede utilizar esta función para definir el nuevo límite inferior del rango de la nueva consulta. Puede usar esta lógica para no tener que conocer el momento específico que la sincronización paso por última vez. Usted sólo tendrá que mantener los valores LSN en el período de sincronización anterior. (Más información AQUI)
- sys.fn_cdc_decrement_lsn: Devuelve el LSN anterior basado en un valor LSN de entrada. Usted puede utilizar esta función para determinar el límite superior de un intervalo de LSN definido en una consulta (Más información AQUI).
- sys.fn_cdc_is_bit_set: Utiliza la posición ordinal en la máscara de bits (el valor de $ _update_mask) para una columna especificada, y la máscara de bits como entradas para devolver un bit que representa si la columna de entrada se ha actualizado. Usted puede utilizar esta función para agregar una columna a la salida que representa el estado de actualización de una columna de origen dado. (Más información AQUI).
- sys.fn_cdc_get_column_ordinal: Utiliza la instancia de captura y la información de la columna nombre como entrada y devuelve un entero que representa la posición ordinal de la columna en la máscara de bits (Más información AQUI).
- sys.fn_cdc_map_lsn_to_time: Utiliza un LSN como entrada y devuelve el valor de fecha y hora de la hora de confirmación de la columna tran_end_time en la tabla del sistema cdc.lsn_time_mapping (Más información AQUI).
- sys.fn_cdc_get_max_lsn: Devuelve el LSN máximo que existe en la columna start_lsn de la tabla del sistema cdc.lsn_time_mapping. Este valor representa el último cambio confirmado propado a una tabla de cambios en la base de datos actual. Este valor no depende de la instancia de captura (Más información AQUI).
- sys.fn_cdc_get_min_lsn: Devuelve el LSN máximo que existe en la columna start_lsn de la tabla del sistema cdc.lsn_time_mapping. Este valor puede cambiar cuando el proceso de limpieza se lleva a cabo. Puede utilizar esta función para determinar el extremo inferior para el valor de LSN para verificar que su rango se encuentra dentro de la línea de tiempo CDC (Más información AQUI).
SQL Server Audit (Más información AQUI)
Usted debe crear un objeto de auditoría de servidor SQL antes de definir las especificaciones de auditoría. Usted puede lograr esto mediante el uso de SSMS o con la declaración CREATE SERVER AUDIT.
En SSMS, se puede utilizar el Explorador de objetos y vaya a la carpeta en la carpeta de Auditorías de Seguridad. A continuación puede hacer clic en la carpeta y seleccione Nueva Auditoría para crear una nueva de SQL Server Audit, como vemos en la siguiente imagen.
Dentro de la definición de auditoría, puede configurar las siguientes opciones:
- Audit Name: Define el nombre asignado al objeto de auditoría.
- Queue Delay: Define la cantidad de tiempo transcurrido en milisegundos que puede pasar antes de que el servidor debierá procesar una acción de auditoría. El valor predeterminado es 1 segundo o 1.000 milisegundos.
- Shut Down Server On Audit Log Failure: Determina si SQL Server se cierra cuando la auditoría produce un error. Usted puede activar esta opción si las políticas de su organización de auditoría o de los requisitos reglamentarios aplicables, tales como la Health Insurance Portability, la Accountability Act (HIPAA), el Payment Card Industry (PCI) act, y la Federal Information Security Management Act (FISMA), que requieren auditar cada caso sin fallos.
- Audit Destination: Especifica donde la información de auditoría sera escrita. Las opciones incluyen un archivo, el registro de seguridad de Windows, o el registro de aplicación de Windows. Cuando se selecciona el destino del archivo de auditoría, también hay que configurar las siguientes opciones:
- File Path: Especifica el directorio donde se guarda el archivo. El motor de base de datos genera automáticamente el nombre del archivo, que se basa en el nombre del objeto de auditoría y la auditoría de identificador único global (GUID).
- Maximum Rollover: Define el máximo número de archivos que se pueden crear, excepto cuando la opción ilimitada es seleccionado. Cuando se habilita la opción ilimitada para este ajuste, no hay límite en el número de archivos creados.
- Maximum Filesize: Especifica el tamaño máximo del archivo antes de crear uno nuevo. Es necesario especificar un valor entero y si ese entero debe ser interpretada como megabytes, gigabytes, o terabytes. Cuando se selecciona Ilimitado, el archivo aumenta hasta que la unidad de disco duro está lleno. Si el archivo se llena el disco duro por completo, se detiene el registro y el servidor se apaga si la opción Shut Down Server On Audit Log Failure se ha habilitado. El valor mínimo permitido para este campo es de 2 MB.
- Reserve Disk Space: Especifica que se crea un archivo en el tamaño máximo del archivo inmediatamente. De lo contrario, el archivo crece a medida que los detalles de auditoría se agregan a él. No se puede elegir esta opción si la opción ilimitada es habilitado para el tamaño máximo de archivo.
CREATE SERVER AUDIT audit_name
{
TO { [ FILE (<file_options> [ , ...n ]) ] | APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [ , ...n ] ) ]
}
[ ; ]
<file_options>::=
{
FILEPATH ='os_file_path'
[ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
[ , MAX_ROLLOVER_FILES = { integer | UNLIMITED } ]
[ , RESERVE_DISK_SPACE = { ON | OFF } ]
}
<audit_options>::=
{
[ QUEUE_DELAY =integer ]
[ , ON_FAILURE = { CONTINUE | SHUTDOWN } ]
[ , AUDIT_GUID =uniqueidentifier ]
}
Una vez creado el objeto SQL Server Audit, que debe activarla. Usted puede lograr esto mediante el uso de SSMS o la sentencia ALTER SERVER AUDIT.
Creación de una especificación de auditoría de base de datos de nivel
Una vez creado el objeto SQL Server Audit, puede optar por crear especificaciones de auditoría a nivel de servidor o el nivel de base de datos. Para el acceso de seguimiento o modificaciones hechas a los datos, se crea una especificación de auditoría de base de datos.
Puede crear una especificación de auditoría de base de datos con la instrucción
CREATE DATABASE AUDIT SPECIFICATION o el Explorador de objetos en SSMS. Las Especificaciones de auditoría de base de datos se encuentran en la carpeta por debajo de [instance name]\[Databases]\[database name]\Security folder.
Para definir una especificación de auditoría de base de datos, debe definir el nombre de la especificación de auditoría, el objeto de auditoría donde los eventos serán escritos, y cada acción de auditoría que se registra.
Hay una amplia variedad de tipos de acciones de auditoría que se puede configurar dentro de cada especificación de auditoría de base de datos. Por ejemplo, puede especificar INSERT, UPDATE, DELETE, o SELECT y definir sobre los objetos que desea realizar un seguimiento de estos comandos. También se puede especificar para qué usuarios y roles le gustaría que los sucesos se registraran.
Además de especificar los eventos individuales que seran registrados, puede especificar un grupo entero de eventos que se registran. El SCHEMA_OBJECT_ACCESS_GROUP, también equivalente a la clase Audit Schema Object Access event, devuelve la información cuando un permiso de objeto, como INSERT, UPDATE, DELETE, SELECT, EXECUTE, o REFERENCES se produce. La información proporcionada por esta clase de eventos notifica que la sintaxis de la consulta que se publicó pero no mantiene un antes y después de los valores de los datos actualizados o eliminados (Más información AQUI).
Performance y Auditoria: ¡Tienes que ser cuidadoso en la definición de auditoría para verificar los efectos del rendimiento de los registros. Hay una tendencia a tratar de registrar todos los eventos, que puede conducir a resultados extremadamente lenta. Considere cuidadosamente los objetos en que la auditoría es necesaria, así como los eventos que deben ser seguidos.
Antes de la especificación de auditoría de base de datos comience a recopilar información, que debe activarla con SSMS, como parte de la instrucción CREATE DATABASE especificación de auditoría, o con la especificación de auditoría ALTER DATABASE.
Resumen de la lección:
- El seguimiento de cambios (Change tracking) debe estár habilitado primero en la base de datos y luego a nivel de tabla.
- El seguimiento de cambios (Change tracking) se puede decir lo que los registros han sido modificados y le proporcionará el resultado final de los datos.
- El seguimiento de cambios (Change tracking) requiere menos recursos del sistema de CDC.
- CDC le puede decir lo que los registros han sido modificados y le proporcionará los datos finales, así como los estados intermedios de los datos.
- SQL Server Audit le permite registrar el acceso a las tablas, vistas y otros objetos.
