PROJECT SERVER. USO DE LA AUDITORIA DE SQL SERVER 2008 R2 PARA UN TRIGGER DE UNA TABLA DE PROJECT SERVER.

Buenas,

Primero de todo, desearos Feliz Año. Vamos a empezar el blog este 2013 de la misma manera que acabamos el año pasado, con un excelente artículo de nuestro colega Marc Biarnes, que trata acerca del uso de la auditoría y triggers de SQL Server, para monitorizar  y obtener más información, que nos pueda ayudar a entender por qué nos encontramos con determinados comportamientos. Podéis encontrar su post original (en francés) en su blog:

https://blogs.technet.com/b/frenchpjblog/archive/2012/12/24/3542471.aspx

“Los triggers de SQL Server son una solución simple y eficaz para extender la funcionalidad de Project Server, como alimentar una tabla externa o enviar una notificación por correo electrónico; se recomienda usar esto con moderación, debido al impacto significativo que puede tener en el rendimiento; que puede, en casos extremos, llegar a causar interbloqueos. El trigger puede resultarnos de interés también a la hora de buscar la razón de una acción de la cual no se sabe la causa.

En el ejemplo que trataremos hoy, consideraremos un caso en el que se eliminan registros de una tabla, sin que sepamos por qué, pero que hace que no aparezcan algunas líneas en el parte de horas en al acceder a Mis Tareas. En nuestro caso, se eliminan registros de las tabla MSP_TASKS_SAVED de la base de datos Published, creando registros huérfanos en las tabla MSP_ASSIGNED_SAVED

Como tenemos el escenario para reproducir a voluntad este fenómeno, tenemos la posibilidad de elegir entre:

1. Capturar una traza con el Profiler de SQL Server para registrar la actividad de esta base de datos, y detener la traza tan pronto se detecte el problema.
2. Crear un trigger DDL (el trigger DML no puede usarse en una acción DELETE) que registre la acción en una tabla temporal.
3. Usar la función auditoría de SQL Server 2008 (R2) para iniciar la acción, capturando la información complementaria que disparó el trigger

La primera solución es la más fácil de implementar, pero el resultado puede variar considerablemente dependiendo de la actividad sobre la base de datos, y lo más importante: si se tarda demasiado en buscar el rastro, se puede perder información interesante. Además, esta solución tiene un impacto significativo en el rendimiento y por lo tanto, no se puede utilizar durante un período largo.

La segunda solución es también muy fácil de implementar, pero el resultado seguirá siendo bastante limitado, porque no se puede obtener información sobre el evento: quién y cuándo ha generado esto. Pero no se puede saber el qué, porque la función SQL EVENTDATA puede ser utilizada en un trigger DML, pero recordemos que no puede ser asociado con una acción DELETE en una tabla.

La última solución es la mejor porque, además de dar información sobre el quién y cuándo, también nos dirá el qué. A continuación tenemos los detalles para esta solución:

Creación del fichero de Log:

El primer paso es crear el archivo de registro donde se almacenará la información de auditoría:

use master

go

-- Create the server audit.

CREATE SERVER AUDIT Audit_mytrig

TO FILE ( FILEPATH = 'C:\' ) ;

GO

-- Enable the server audit.

ALTER SERVER AUDIT Audit_mytrig

WITH (STATE = ON) ;

GO

Creación del trigger:

USE PROJECTSERVER_PUBLISHED ;

GO

CREATE DATABASE AUDIT SPECIFICATION Audit_mytrig_Table

FOR SERVER AUDIT Audit_mytrig

ADD (DELETE

ON MSP_TASKS_SAVED by dbo)

WITH (STATE = ON) ;

GO

Después de hacer esto, cualquier acción de DELETE en la tabla MSP_TASKS_SAVED escribirá la información en el archivo de log

Recuperación del archivo de registro:

Para leer el contenido del archivo de registro, podemos utilizar el siguiente procedimiento:

USE master

GO

-- get the audit file

DECLARE @filepattern VARCHAR(300)

DECLARE @folder VARCHAR(255)

DECLARE @auditguid VARCHAR(36)

SELECT @auditguid = audit_guid,@folder = log_file_path

FROM sys.server_file_audits WHERE name = 'Audit_mytrig'

SELECT @filepattern = @folder + '*_' + @auditguid + '*'

-- view the results

SELECT event_time,session_id,a.name AS Action,c.class_type_desc AS ObjectType,

f.server_principal_name,f.schema_name,f.OBJECT_NAME,f.statement,f.additional_information

FROM fn_get_audit_file(@filepattern,NULL,NULL) AS f

JOIN sys.dm_audit_class_type_map c ON f.class_type = c.class_type

JOIN sys.dm_audit_actions a ON f.action_id = a.action_id

AND c.securable_class_desc = a.class_desc

WHERE f.action_id <> 'AUSC'

ORDER BY event_time DESC,sequence_number

Además de la información básica de quién y cuándo, resulta una información muy interesante saber el qué. Esto puede ser una consulta o un procedimiento almacenado. En este último caso, el nombre del procedimiento almacenado aparece en la columna Statement.

Conclusiones:

Podemos concluir que este procedimiento puede resultar muy útil para saber la razón de un fenómeno que no podemos explicar. ¿Se trata de una consulta, o procedimiento almacenado, vinculado al código personalizado? ¿Cuál es el código relacionado con una acción en particular realizada en Project Server?

La información en Statement puede ayudarnos mucho a entender lo que está ocurriendo y puede ser un muy buen punto de partida a la hora de buscar una causa o un escenario de reproducción.”

Bueno, esperamos os haya resultado tan útil e interesante como nos lo ha parecido a nosotros.

Un saludo

Jorge Puig