seguimiento de que datos son los que cambian entre determinados periodos de tiempo. Un claro ejemplo de este requisito, son las técnicas de seguimiento de cambios que es necesario implementar para mantener actualizados los sistemas datawarehouse de forma incremental. Para dar solución a estos requisitos, SQL Server 2008 proporciona dos nuevas características: Change Data Capture y Change Tracking.

Change Data Capture

Change Data Capture, nos permite capturer los cambios realizados sobre determinadas tablas de nuestra base de datos, y nos permite accede a ellos en un formato relacional, que facilita su explotación. Una vez hayamos activado Change Data Tracking a nivel de base de datos, y hayamos seleccionado las tablas que deseamos monitorizar, el proceso de captura extrae del log de transacciones los cambios (inserciones, actualizaciones y borrados) sobre esas tablas y los inserta en las tablas asociadas, que después podremos consultar a través de determinadas funciones que se encargan de proporcionar unos datos formateados. A continuación veremos que pasos debemos de seguir para acticar esta características y como utilizarla.

En primer lugar, debemos de activar Change Data Capture en la base de datos:

USE AdventureWorks2008

GO

EXEC sys.sp_cdc_enable_db

GO

Al habilitar una base de datos para Change Data Tracking, se crea en esa base de datos un esquema denominada cdc y un usuario con el mismo nombre, así como las tablas de metadatos necesarios para el funcionamiento. A continuación, y tras haber comprobado que el servicio SQL Server Agent está iniciado, habilitaremos las tablas que deseamos comprobar. En el siguiente ejemplo se habilita el seguimiento para la tabla HumanResources.Department:

EXEC sys.sp_cdc_enable_table @source_Schema='HumanResources',@source_name='Department',@role_name='rolCDC'

El mensaje que nos devuleve la ejecución de este comando, es muy explicativo de cómo funciona internamente esta característica. El mensaje dirá algo como:

Job 'cdc.AdventureWorks2008_capture' started successfully.

Job 'cdc.AdventureWorks2008_cleanup' started successfully.

Como se desprende de estos mensajes, el procedimiento almacenado creará dos trabajos del Agente de SQL Server. El primero de ellos, que se encargará capturar los cambios, mientras que el segundo se encarga de realizar tareas de limpieza. Obviamente, estos trabajos, se crean únicamente para la primera de las tablas activadas. Posteriormente, el trabajo se encarga de leer los metadatos para conocer cuales son las tablas que deben de controlarse. Para obtener información acerca de los cambios registrados, debemos de realizar varias operaciones.

En primer lugar, Change Data Capture, almacena información acerca del instante en el que se produce un cambio, capturando los LSN del registro de transacciones. Obviamente, nuestras aplicaciones no explotarán los datos utilizando un número de secuencia, sino usando fechas. Para ello, disponemos de una función, denominada sys.fn_cdc_map_time_to_lsn, que nos permite obtener un LSN de cambio, a partir de la fecha.

Una vez obtenidos los LSN que marquen el intervalo de tiempo del que queremos obtener los cambios, ya podemos utilizar las funciones creadas para la consulta de datos. En el momento en el que activamos el Change Data Capture en una table, se crea una función para cada tabla, denominada, en nuestro caso cdc.fn_cdc_get_all_changes_HumanResources_Department, que pasándole como parámetro los lsn inicial y final, nos devolverá los cambios realizados sobre esta tabla en ese intervalo.

Change Tracking

Otra característica que nos permitirá llevar un control sobre las operaciones realizadas en una determinada tabla es Change Tracking. A diferencia de lo que ocurre con Change Data Capture, con esta característica podemos obtener información sobre los cambios realizados y a que registros afectaron (a través de la clave primaria de la tabla) pero no almacena los datos modificados, por lo que a lo largo del tiempo, iremos perdiendo esos valores, después de varios cambios sobre los mismos datos. Además, el mantenimiento de esa información, se realiza de forma síncrona en el momento en el que se realiza el cambio, y se almacenan los metadatos en las tablas internas utilizadas por Change Tracking. Debido a este comportamiento, Change Tracking puede sernos útil en escenarios de sincronización, en los que el dato importante es el último, pero no el histórico de cada uno de los cambios realizados.

Para utilizar Change Tracking,d ebemos de activarlo, de modo similar a Change Data Capture, primero a nivel de base de datos y después, seleccionando la tabla, como muestra el siguiente código.

ALTER DATABASE AdventureWorks2008

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

GO

ALTER TABLE HumanResources.Department

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

Una vez activado

Comparativa entre Change Data Capture y Change Tracking

Como resumen, a modo de comparativa entre las dos tecnologías podemos comentar las siguientes diferencias:

·         Desde el punto de vista del mecanismo utilizado para realizar seguimiento de los cambios, Change Data Captura utiliza un trabajo del Agente para leer, de forma asíncrona los cambios desde el registro de transacciones de la base de datos, mientras que Change Tracking realiza un seguimiento de cambios síncrono.

·         Por lo que respecta a los datos obtenidos, Change Tracking tan solo realiza seguimiento de las operaciones realizadas, manteniendo el valor de la clave primaria del registro modificado como enlace entre las tablas de metadatos y la tabla auditada, por lo que no podemos obtener la información completa, mientras que Change Data Captura si nos proporciona esa información

Auditoria

Establecer un sistema de auditoría para una aplicación, es uno de los puntos cada vez más solicitados, debido fundamentalmente a requisitos legales , y hasta ahora un verdadero quebradero de cabeza para los administradores de bases de datos. Para un sistema SQL Server disponemos de varias opciones para establecer sistemas de auditoria:

·         SQL Trace. Utilizando trazas, bien se a través de procedimientos almacenados o a través de SQL Profiler, podemos registrar diferentes eventos que se producen en la instancia SQL Server.

·         Triggers. Otra opción, sería implementar un sistema basado en triggers. En SQL Server 2005, se añadió la posibilidad de crear triggers para sentencias DDL, por lo que el rango de eventos que podemos controlar es mayor.

·         En SQL Server 2008, disponemos de un nuevo sistema de auditoría, implementado a través del objeto SQL Server Audit. Este objeto, nos permite definir auditoría a nivel de instancia o a nivel de base de datos, y almacenar los resultados de la auditoría bien en ficheros, o bien en el Visor de Sucesos de Sistema Operativo, ya sea en el registro de Aplicación o en el registro de Seguridad, y que será el que analizaremos en este apartado.

El primer objeto que debemos de crear es un objeto SQL Server Audit, que definimos a nivel de la instancia de SQL Server, y en el que lo que configuramos es el destino de la Auditoria. Como hemos comentado con anterioridad, el destino de la Auditoria pueden ser: Fichero, Registro de Seguridad y Registro de Aplicación.

Una vez hemos creado el objeto SQL Server Audit (podemos crear varios objetos) debemos de especificar cuales son los eventos que deseamos auditar. Estos eventos podemos especificarlos utilizando los denominados Audit Groups, que no son más que grupos de acciones, a dos niveles:

·         A nivel de instancia, a través de la Server Audit Specification. Nos permite auditar eventos que ocurren a nivel de instancia de SQL Server, tales como inicios de sesión, copias de seguridad, ejecución de comandos DBCC, o eventos de sesiones de Service Broker.

·         A nivel de base de datos, a través de la Database Audit Specification. Para aquellos eventos que deseemos auditar a nivel de objetos u operaciones de las bases de datos, debemos de utilizar los Audit Groups a nivel de base de datos, que nos permiten auditar eventos tales como los eventos de creación, borrado o eliminación de objetos, o cambios en la membresía de roles de usuarios.

En el siguiente ejemplo, vemos como se puede crear una auditoría que registro los inicios de sesión fallidos:

--Creamos el objeto de Auditoria que define la ubicación

CREATE SERVER AUDIT AuditTest

TO FILE

(     FILEPATH = N'C:\temp\'

      ,MAXSIZE = 0 MB

      ,MAX_ROLLOVER_FILES = 2147483647

      ,RESERVE_DISK_SPACE = OFF

)

WITH

(     QUEUE_DELAY = 1000

      ,ON_FAILURE = CONTINUE

)

GO

--Comprobamos que se ha creado correctamente

select * from sys.server_audits

GO

--Inspeccionamos cuales son las acciones que podemos auditar

Select * from sys.dm_audit_Actions

GO

--Creamos una especificación a nivel de instanacia para registrar los inicios de sesión fallidos

CREATE SERVER AUDIT SPECIFICATION AuditServerSpecification

FOR SERVER AUDIT AuditTest

ADD (FAILED_LOGIN_GROUP)

GO

--comprobamos que se ha creado correctamente

select * from sys.server_audit_specifications

select * from sys.server_audit_specification_details

GO

--Activamos el objeto de Auditoria

ALTER SERVER AUDIT AuditTest WITH(STATE= ON)

GO

--Activamos la especificación

ALTER SERVER AUDIT SPECIFICATION AuditServerSpecification WITH(STATE= ON)

-- Leemos los datos del fichero de auditoria

select * from fn_get_audit_file('C:\Temp\*',DEFAULT, default)

Si quisiéramos crear una auditoria a nivel de base de datos que, por ejemplo, auditase las sentencias SELECT ejecutadas en una determinada tabla, podríamos hacerlo del siguiente modo:

USE AdventureWorks2008

GO

CREATE DATABASE AUDIT SPECIFICATION AuditSelect

FOR SERVER AUDIT [AuditTest]

ADD(SELECT ON HumanResources.EmployeePayHistory BY dbo)

WITH (STATE = ON)

GO

De este modo, podemos crear sistemas de auditoria muy completos, y a la vez, no intrusivos, por lo que el impacto en el rendimiento  es menor que el de otras aproximaciones.