Auditoria en SQL Server 2008 R2

SQL Server provee una herramienta robusta para auditar eventos a nivel de servidor, como a nivel de base de datos y sobre datos inclusive en consultas DML. A continuación detallo los pasos necesarios para utilizar este tipo de auditoria. 1. En Audits, dentro de Security crear un nuevo Audit

2. Completamos la información del Audit, el cual servirá únicamente como medio de almacenamiento. Podremos crear varios audit con diferentes finalidades. Podemos establecerle límites de espacio y tiene una opción de apagar el sevidor si la auditoria falla.

3. Una vez creada la Audit procederemos a habilitarla, cualquier modificación sobre ella deberá esta deshabilitada.

4. El siguiente paso puede ser en 2 formas: 1 – Nivel de Servidor 2 – Nivel de Base de Datos. 1 – Nivel de Servidor Sobre la opción de Server Audit Specification, crearemos una nueva.

5. En una nueva opción, hay diferentes grupos de opciones que podemos auditar, favor revisar el siguiente enlace donde se especifican las cualidades de cada grupo. http://technet.microsoft.com/en-us/library/cc280663%28SQL.100%29.aspx Vamos a realizar el ejemplo de auditar BACKUP/RESTORE y además, LOGIN de usuarios. Quedaría de la siguiente forma:

6. Una vez registrado la auditoria de servidor procederemos a activarla

7. Cuando existan LOGINS al servidor, respaldos o restauración de base de datos, vamos a tener información de ello en el “AuditModificaciones”

Podemos ver los Logins y el Backup que hice de prueba

8. El siguiente paso sería configurar la auditoria a nivel de base de datos

9. Especificamos la auditoria para la tabla “Clientes” en el evento INSERT, además especificamos auditoria ante cualquier cambio en la estructura de la base de datos (CREATE, ALTER, DROP)

10. Siempre la habilitamos porque se crea deshabilitada,

11. Vemos los resultados en el audit “AuditModificaciones”

Utilizando Change Tracking y Chage Data Capture en SQL Server

Change Tracking  es un método que provee SQL Server 2008,  para determinar qué cambios ha ocurrido en los datos y estructura de la base de datos.

Podemos realizar las siguientes funciones:

·         Funcionalidad con DML

·         Responde preguntas como:

o   Cuantas filas en la tabla han cambiado

o   Que Columnas ha cambiado

o   Que fila en particular ha sido actualizada

Configurando Change Tracking

Para habilitarlo lo podemos hacer vía SSMS o por ALTER DATABASE.

Se deberá configurar las siguientes opciones:

·         Change Tracking definirlo en True

·         Retention Period  Define la cantidad de tiempo que se le dara mantenimiento.  Por defecto es 2

·         Tipo de Unidad de Retention Period.   Dias, Horas, Minutos.  Por defecto es Dias

·         Auto CleanUp.   Lo pondremos en ON  para que automáticamente limpie la información

O bien definir la siguiente instrucción T-SQL

ALTER DATABASE AdventureWorksDW2008

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

Vemos lo que tenemos configurado:

SELECT  * FROM sys.change_tracking_databases ctd

SELECT OBJECT_NAME([object_id]), * FROM sys.change_tracking_tables ctt

Al realizar INSERT,  UPDATE  o DELETE   podremos verificar la información de indica que se realizado una modificación y en que numero de registro de una tabla

DECLARE @last_synchronization_version bigint

SET @last_synchronization_version =

CHANGE_TRACKING_Min_VALID_VERSION(2105058535);

SELECT  CT.* FROM CHANGETABLE(CHANGES NOMBRE_TABLA, @last_synchronization_version) AS CT

 

Habilitando el CDC (Change Data Capture)  

Para habilitarlo en la base de datos utilizar la siguiente instrucción T-SQL

EXECUTE sys.sp_cdc_enable_db;

Luego habilitarlo para una tabla específica,  utilizaremos el ejemplo de la tabla llamada “Director”

EXEC sys.sp_cdc_enable_table

    @source_schema = N'dbo'

  , @source_name = N'Director'

  , @role_name = N'cdc_admin'

  , @capture_instance = N'Director'

  , @supports_net_changes = 1

El nombre de instancia de captura,  es el nombre se utilizara para llamar posteriormente a esta tabla en los CDC.

DECLARE @begin_time datetime, @end_time datetime,

@from_lsn binary(10)  , @to_lsn binary(10);

SET @begin_time = GETDATE() -15;

 SET @end_time = GETDATE();

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);

SELECT  * FROM cdc.fn_cdc_get_all_changes_Director(

       @from_lsn, @to_lsn, 'all');

O bien obtener todo lo que ha realizado en la tabla Director.  Podemos ver las tablas creadas en las tablas del sistema de la base datos.

SELECT * FROM cdc.Director_CT

Gestion de Tablas Particionadas

Un desafío para un diseñador de base de datos es diseñar sistemas que sean mantenibles, escalables y que tengan un buen desempeño.

Aunque el objetivo suena obvio,  algunas veces es difícil de lograr.

Una técnica  usada para intentar lograr un balance razonable entre  mantenimiento y escalabilidad vs. Desempeño  es las Tablas Particionadas.

 Esto no es nuevo, fue un cambio significante en SQL Server 2005 y continua en SQL Server 2008 proveyendo herramientas para ayudar a los desarrolladores a lograr esta tarea.

¿Por qué particionar?

El particionamiento divide grandes cantidades de datos en pequeños y más manejables sets.

¿Como ayuda?

 Modificar y buscar datos en un conjunto más pequeño es más fácil y rápido que trabajar con un gran set de datos.

Por ejemplo: Buscar  una factura por una fecha dada es más eficiente cuando buscamos en un archivador organizado.  Lo mismo sucede cuando buscamos en las tablas.

Beneficios:

Más rápido y eficiente acceso a datos.

El desempeño incrementa para operaciones en paralelo con servidores multiprocesadores,  cada procesador puede acceder a multiples particiones simultáneamente.

Nota:

Particionamiento de tablas e índices solo está disponible en SQL Server 2008 Enterprise, Developer y Ediciones de Evaluacion.

Terminos a Considerar

Partition Key:  Una columna en una Tabla determina a cual partición los datos residirán

Partition Function :  Una función que especifica que datos iran a que partición definidos por el Partition Key.

Partition Schema: Mapea una partición a un Filegroups.

Limites LEFT y RIGHT

Una función para 4 particiones:

Aquí lo que significa:

-- Crear una funcion de particion

CREATE PARTITION FUNCTION VentasxQuarterPF(datetime)

AS RANGE RIGHT

FOR VALUES ('19960101', '19960401', '19960701', '19961001')

GO

-- Filegroup para ventas < 01/01/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1995Q4FG

GO

-- -- Filegroup para ventas >= 01/01/1996 AND <= 03/31/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q1FG

GO

-- Filegroup para ventas >= 04/01/1996 AND <= 06/30/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q2FG

GO

-- Filegroup para ventas >= 07/01/1996 AND <= 09/30/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q3FG

GO

-- Filegroup para ventas >= 10/01/1996 AND <= 12/31/1996

ALTER DATABASE Demo

ADD FILEGROUP Ventas1996Q4FG

GO

-- Para futuro crecimiento, adicionar un Filegroup

ALTER DATABASE Demo

ADD FILEGROUP Ventas1997Q1FG

GO

Creando archivos para los FileGroup

-- Ventas2002Q4FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1995Q4,

    FILENAME = 'C:\SQL Server\Ventas1995Q4.ndf')

TO FILEGROUP Ventas1995Q4FG

GO

-- Ventas1996Q1FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q1,

    FILENAME = 'C:\SQL Server\Ventas1996Q1.ndf')

TO FILEGROUP Ventas1996Q1FG

GO

-- Ventas1996Q2FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q2,

    FILENAME = 'C:\SQL Server\Ventas1996Q2.ndf')

TO FILEGROUP Ventas1996Q2FG

GO

-- Ventas1996Q3FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q3,

    FILENAME = 'C:\SQL Server\Ventas1996Q3.ndf')

TO FILEGROUP Ventas1996Q3FG

GO

-- Ventas1996Q4FG filegroup

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1996Q4,

    FILENAME = 'C:\SQL Server\Ventas1996Q4.ndf')

TO FILEGROUP Ventas1996Q4FG

GO

--  filegroup adicional

ALTER DATABASE Demo

ADD FILE(NAME = Ventas1997Q1,

    FILENAME = 'C:\SQL Server\Ventas2004Q1.ndf')

TO FILEGROUP Ventas1997Q1FG

GO

-- Crear un partition scheme usando un file group diferente para cada particion

-- NOTE: Ventas1997Q1FG es una particion extra.  SQL la marcara como NEXT used

CREATE PARTITION SCHEME VentasxQuarterPS

AS PARTITION VentasxQuarterPF

TO (Ventas1995Q4FG, Ventas1996Q1FG, Ventas1996Q2FG,

    Ventas1996Q3FG, Ventas1996Q4FG, Ventas1997Q1FG)

GO

Crear la tabla donde se utilizara las particiones

CREATE TABLE FacturasPT(

       [Codigo_Pedido] [int] NOT NULL,

       [Codigo_Cliente] [varchar](20) NULL,

       [Fecha] [datetime] NULL,

       [Origen] [varchar](50) NULL,

       [Descripcion] [varchar](max) NULL,

       [Total] [money] NOT NULL)

       ON VentasxQuarterPS(Fecha)

      

      

            

       INSERT INTO FacturasPT

       SELECT p.Codigo_Pedido, p.Codigo_Cliente, p.Fecha, p.Origen, p.Descripcion, p.Total

         FROM Pedidos p

      

SET STATISTICS IO ON

SELECT * FROM FacturasPT p

SELECT p.Codigo_Cliente, p.Total, p.Descripcion FROM Pedidos p

----  INFORMACION DE LA PARTICION

SELECT

  $PARTITION.VentasxQuarterPF(Fecha) AS Partition,

  COUNT(*) AS NumeroVentas

FROM FacturasPT

GROUP BY $PARTITION.VentasxQuarterPF(Fecha)

ORDER BY PARTITION

SELECT DISTINCT Fecha,

    $PARTITION.VentasxQuarterPF(Fecha) AS Partition

FROM FacturasPT

WHERE Fecha IN('19960115', '19960627',

    '19960817', '19971105')

ORDER BY Fecha

SELECT name, data_space_id, type, function_id

FROM sys.partition_schemes

-- Diplay information about partition filegroups

SELECT name, data_space_id, type

FROM sys.data_spaces