Tuesday, February 23, 2010

Service Broker-MAster Audit Database

USE master

-- one audit database and table for all
IF DB_ID('MasterAuditDatabase') IS NOT NULL
DROP DATABASE MasterAuditDatabase
CREATE DATABASE MasterAuditDatabase

GO
-- enable service broker
ALTER DATABASE MasterAuditDatabase SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE MasterAuditDatabase SET TRUSTWORTHY ON

GO
USE MasterAuditDatabase

GO
-- get service broker guid for MasterAuditDatabase.
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
-- it's E5E588AC-346C-445C-92C5-DADBDC5DC93C for my MasterAuditDatabase
SELECT service_broker_guid
FROM sys.databases
WHERE database_id = DB_ID()

GO
IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
DROP TABLE dbo.MasterAuditTable

GO
-- Master Audit Table
CREATE TABLE dbo.MasterAuditTable
(
Id BIGINT IDENTITY(1,1),
SourceDB sysname NOT NULL,
SourceTable sysname NOT NULL,
UserID NVARCHAR(500) NOT NULL,
-- D = Delete, I = Insert, U = Update
DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
ChangedData XML NOT NULL,
ChangeDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
DROP TABLE dbo.AuditDialogs

GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
DbId INT NOT NULL,
DialogId UNIQUEIDENTIFIER NOT NULL
)

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
ErrorProcedure NVARCHAR(126) NOT NULL,
ErrorLine INT NOT NULL,
ErrorNumber INT NOT NULL,
ErrorMessage NVARCHAR(4000) NOT NULL,
ErrorSeverity INT NOT NULL,
ErrorState INT NOT NULL,
AuditedData XML NOT NULL,
ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
DROP PROCEDURE dbo.usp_WriteAuditData

GO
-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
DECLARE @msgBody XML
DECLARE @dlgId uniqueidentifier

WHILE(1=1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- insert messages into audit table one message at a time
;RECEIVE top(1)
@msgBody = message_body,
@dlgId = conversation_handle
FROM dbo.TargetAuditQueue

-- exit when the whole queue has been processed
IF @@ROWCOUNT = 0
BEGIN
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
BREAK;
END

DECLARE @SourceDB sysname, @SourceTable sysname,
@UserID NVARCHAR(500), @DMLType CHAR(1), @ChangedData XML

-- xml datatype and its capabilities rock
SELECT @SourceDB = T.c.query('/AuditMsg/SourceDb').value('.[1]', 'sysname'),
@SourceTable = T.c.query('/AuditMsg/SourceTable').value('.[1]', 'sysname'),
@UserID = T.c.query('/AuditMsg/UserId').value('.[1]', 'NVARCHAR(50)'),
@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
@ChangedData = T.c.query('*')
FROM @msgBody.nodes('/AuditMsg/ChangedData') T(c)

INSERT INTO dbo.MasterAuditTable(SourceDB, SourceTable, UserID, DMLType, ChangedData)
SELECT @SourceDB, @SourceTable, @UserID, @DMLType, @ChangedData

-- No need to close the conversation because auditing never ends
-- you can end conversations if you want periodicaly with a scheduled job
-- END CONVERSATION @dlgId

IF @@TRANCOUNT > 0
BEGIN
COMMIT;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK;
END
-- insert error into the AuditErrors table
INSERT INTO AuditErrors (
ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
ErrorSeverity, ErrorState, AuditedData)
SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
ERROR_SEVERITY(), ERROR_STATE(), @msgBody
END CATCH;
END
END

GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
DROP QUEUE dbo.TargetAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts WHERE NAME = '//Audit/Contract')
DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
DROP MESSAGE TYPE [//Audit/Message]

GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
([//Audit/Message] SENT BY INITIATOR)

-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
WITH STATUS=ON,
ACTIVATION (
PROCEDURE_NAME = usp_WriteAuditData, -- sproc to run when the queue receives a message
MAX_QUEUE_READERS = 50, -- max concurrently executing instances of sproc
EXECUTE AS 'dbo' );

-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
AUTHORIZATION dbo
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])

1 comment: