Sanjeeva Samaraweera

Following scripts will enable you to set up a set of asynchronous triggers to log the audit records. Unlike other methods here the service broker is used to insert the records to another database table asynchronously which will reduce the work load in source database.

 

First set up the audit database and its service broker

----------------------------------------------------------------------------------------------------------------------------------------

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

 

-------------------------------------------------------------------------------------------------------------------

once you execute this successfully the guid of the destination service broker will be shown in the result pane of management studio.

now execute the following script which will set up the source service broker and a sample trigger for a new table named person.

before executing replace the guid of destination service broker in this script with the one we got earlier in the result pane

 

----------------------------------------------------------------------------------------------------------------------

 

USE master

IF DB_ID('TestDb1') IS NOT NULL
 DROP DATABASE TestDb1
CREATE DATABASE TestDb1

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

GO
USE TestDb1

GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
 DROP SERVICE [//Audit/DataWriter]

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

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
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 initiator queue
CREATE QUEUE dbo.InitiatorAuditQueue

-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender]
 AUTHORIZATION dbo
 ON QUEUE dbo.InitiatorAuditQueue -- no contract means service can only be the initiator

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_SendAuditData') IS NOT NULL
 DROP PROCEDURE dbo.usp_SendAuditData

GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
 @AuditedData XML
)
AS
BEGIN
 BEGIN TRY
  DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
  SELECT @dlgIdExists = 1

  -- Check if our database already has a dialog id that was previously used
  -- Why reusing conversation dialogs is a good this is explaind here
  -- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
  -- very well
  SELECT @dlgId = DialogId
  FROM MasterAuditDatabase.dbo.AuditDialogs AD
  WHERE AD.DbId = DB_ID()
  IF  @dlgId IS NULL
  BEGIN
   SELECT @dlgIdExists = 0
  END

  -- Begin the dialog, either with existing or new Id
  BEGIN DIALOG @dlgId
   FROM SERVICE    [//Audit/DataSender]             
   TO SERVICE      '//Audit/DataWriter',
       -- this is a MasterAuditDatabase Service Broker Id (change it to yours)
       'A801F163-4DC2-494D-A59B-48D5B6554EB3'
   ON CONTRACT     [//Audit/Contract]
  WITH ENCRYPTION = OFF;

  -- add our db's dialog to AuditDialogs table if it doesn't exist yet
  IF @dlgIdExists = 0
  BEGIN
   INSERT INTO MasterAuditDatabase.dbo.AuditDialogs(DbId, DialogId)
   SELECT DB_ID(), @dlgId
  END
  --SELECT @AuditedData
  
  -- Send our data to be audited
  ;SEND ON CONVERSATION @dlgId 
  MESSAGE TYPE [//Audit/Message] (@AuditedData)
 END TRY
 BEGIN CATCH
  INSERT INTO AuditErrors (
    ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
    ErrorSeverity, ErrorState, AuditedData)
  SELECT ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
    ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
 END CATCH
END

GO
-- Create Sample Table
IF OBJECT_ID('Person') IS NOT NULL
 DROP TABLE Person

GO
CREATE TABLE Person
(
 ID INT PRIMARY KEY,
 FirstName varchar(50),
 LastName varchar(50),
 DateOfBirth SMALLDATETIME
)

-- Create Trigger that will audit data
GO
IF OBJECT_ID ('trgPersonAudit','TR') IS NOT NULL
    DROP TRIGGER trgPersonAudit

GO
CREATE TRIGGER dbo.trgPersonAudit
ON Person
AFTER INSERT, UPDATE, DELETE
AS
 DECLARE @auditBody XML
 DECLARE @DMLType CHAR(1) 
 -- after delete statement
 IF NOT EXISTS (SELECT * FROM inserted)
 BEGIN 
  SELECT @auditBody = (select * FROM deleted AS t FOR XML AUTO, ELEMENTS),
    @DMLType = 'D'
 END
 -- after update or insert statement
 ELSE
 BEGIN
  SELECT @auditBody = (select * FROM inserted AS t FOR XML AUTO, ELEMENTS)
  -- after update statement
  IF EXISTS (SELECT * FROM deleted)
   SELECT  @DMLType = 'U'
  -- after insert statement
  ELSE
   SELECT @DMLType = 'I'
 END

 -- get table name dynamicaly but
 -- for performance this should be changed to constant in every trigger like:
 -- SELECT @tableName = 'Person'
 DECLARE @tableName sysname
 SELECT @tableName = tbl.name
    FROM sys.tables tbl
   JOIN sys.triggers trg ON tbl.[object_id] = trg.parent_id
    WHERE trg.[object_id] = @@PROCID

 SELECT @auditBody =
  '<AuditMsg>
   <SourceDb>' + DB_NAME() + '</SourceDb>
   <SourceTable>' + @tableName + '</SourceTable>
   <UserId>' + SUSER_SNAME() + '</UserId>
   <DMLType>' + @DMLType + '</DMLType>
   <ChangedData>' + CAST(@auditBody AS NVARCHAR(MAX)) + '</ChangedData>
  </AuditMsg>'
 -- Audit data asynchrounously
 EXEC dbo.usp_SendAuditData @auditBody

GO
-- we want this trigger to fire last for each command so that if there are other triggers
-- that update the table they finish their job before auditing
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'delete'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'insert'
EXEC sp_settriggerorder 'dbo.trgPersonAudit', 'Last', 'update'

-- Run code for TestDb1 in each database you wish to enable audting in.

 

-----------------------------------------------------------------------------------------------

 

Now you can test

 

---------------------------------------------------------------------------------------------------

USE TestDb1
-- will be audited
INSERT INTO Person
SELECT 1, 'name 1', 'surname 1', '19761215' UNION ALL
SELECT 2, 'name 2', 'surname 2', '19780705' UNION ALL
SELECT 3, 'name 3', 'surname 3', '19660305' UNION ALL
SELECT 4, 'name 4', 'surname 4', '19660609' UNION ALL
SELECT 5, 'name 5', 'surname 5', '19990606' UNION ALL
SELECT 6, 'name 6', 'surname 6', '20010202' UNION ALL
SELECT 7, 'name 7', 'surname 7', '19440404' UNION ALL
SELECT 8, 'name 8', 'surname 8', '19630523' UNION ALL
SELECT 9, 'name 9', 'surname 9', '19860929'
-- will be audited
UPDATE Person
SET  DateOfBirth = '19800217'
WHERE id = 5
-- won't be audited because of the rollback
BEGIN TRANSACTION
UPDATE Person
SET  DateOfBirth = '19701215'
WHERE id < 6
ROLLBACK
GO

-- check if auditing succeeded
USE MasterAuditDatabase
GO
SELECT * FROM MasterAuditTable
ORDER BY ChangeDate DESC

SELECT * FROM AuditDialogs

 

We can write a sp to execute as a priviledged user in the following  way, so a less privileged user can execute.

alter PROCEDURE test_authenticate1

WITH

EXECUTE AS 'sa'

AS

BEGIN

            create database ……

END

GO

Also we can impersonate a user like this

EXECUTE

AS USER = 'sa'

we can check the current user by following command

SELECT SESSION_USER

To return back to original user use following command

revert

 

SQL Server Enterprise Edition	2000	2005	2008
Database Failover	Mainly Replication	Mirroring introduced	Enhanced Database Mirroring
Database Recovery	users to wait until incomplete transactions rolled back	can reconnect to a recovering database after the transaction log has been rolled forward	Enhanced snapshot and mirroring, backup compression which can be further integrated in Log Shipping
Dedicated Administrator Connection		Introduce DAC to access a running server even if the server is not responding or is otherwise unavailable	Enhancements to DAC
Online Operations		Introduce index Operations and Restore possible while database online	Introduce partial database availability
Replication		enhanced replication using a new peer-to-peer model	identity range management,peer to peer and replication monitor improvements
Scalability and performance		table partitioning, snapshot isolation, 64-bit support, Improved  replication  performance, Introduce covering indexes, Statement-Level Recompiles	Improved core SSIS,SSRS,SSAS processing engines. Performance data collection, Extended profile Events, Backup compression, improved Data compression, single common framework for performance related data collection, Resource Governor
Security		Surface Area Configuration tool, password policies, DDL triggers, catalog views, granular permission sets Encryption	Data Auditing and External Key Management introduced
Availability and Reliability		Memory can be added on the fly and recognized	CPU can be added on the fly and recognized
Application Framework		Service Broker, Notification Services, SQL Server Mobile, and SQL Server Express	Service Broker interface
Developer Productivity		CLR/.NET Framework Integration, Visual Studio Integration, SQL Management Objects, XML Web services, XML Data Type	Date Time Data Type, HierarchyID, LINQ, SQL Server Change Tracking, Table Valued Parameters, Spatial data
Improved BI		A complete BI platform	Enhanced features and performance

This is the sample stored procedure you can enhance

CREATE PROCEDURE SelectIds(@productIds xml) AS

DECLARE

@Products TABLE (ID int)

INSERT

INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)') FROM @productIds.nodes('/Products/id') as ParamValues(ID)

SELECT

* FROM @Products

 

Use Following SQL command to test the stored procedure

DECLARE

@productIds xml

SET

@productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

EXEC

SelectIds @productIds

If we are going for transactional replication, a primary key is mandatory for each replicating table. Even if we go for merge repln, best practice is to have PKs. Best practice of having PKs is to set them with identity.

In a  high availability environment we must manage our identity columns. We have data being inserted on more than one machine that replication brings together. If machine A inserts identity value 1 and machine B also inserts identity value 1, then we are going to have a collision. This means that we must ensure that the identity values being used on each machine are unique.

If we are not careful, when we insert into an identity column, the identity is reseeded using a value of 1 greater than the value we inserted into the identity column. This is why we should be careful on a replicated system because every time replication runs, your identities get reseeded. This manifests itself  a primary key violation. But no worries, with respect to replication, you can disable the reseeding of an identity column by simply using the "not for replication" clause on every identity column.

What the ‘not for replication’ clause does is to tell SQL Server to disable the code that reseeds an identity column when a replication agent is inserting data into an identity column. This preserves all of the identity ranges that we have setup.

We can seed the PK column at different values on each publisher. For example, if we expect a lot of records to be inserted on each of the publishers, we can seed them as follows:

Publisher A: [PK] [int] IDENTITY(1,1) not for replication  not null

Publisher B: [PK] [int] IDENTITY(100000000,1) not for replication  not null

Publisher C: [PK] [int] IDENTITY(200000000,1) not for replication  not null

This configuration would allow users to add up to 100 million records to this table in each database. Furthermore, it also gives us an easy way of identifying records created on each server. If our application grows by leaps and bounds, we can change the identity seed using DBCC CHECKIDENT statement at any time. So if we reach the identity seed of 199,999,999 on publisher A and we are about to step into the range of the publisher B, we can change the identity seed on the first server as follows:

DBCC CHECKIDENT (<table>, RESEED, 400000000)