WordPress.com
This document contains all code used for demonstrating the auditing operation using Service Broker’s External Activator Service.
The first section contains the SQL script used to create the objects in the database, the second section contains the External Activator Service’s configuration file and the third section contains the Auditing Application’s code.
1. Database objects creation script
USE [master]
GO
-- 1. Databases
-- Database which contains the table to be audited
CREATE DATABASE OLTP_DB
GO
-- Database which contains the table which stores the audit information
CREATE DATABASE AUDIT_DB
GO
-- 2. Tables
-- Create table to be audited
USE OLTP_DB
GO
CREATE TABLE INVOICES (ID INT PRIMARY KEY CLUSTERED, AMOUNT MONEY NOT NULL)
GO
-- Create tables to store the audit info
USE AUDIT_DB
GO
CREATE TABLE INVOICES_AUDIT
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
AUDIT_DATE DATETIME NOT NULL,
AUDIT_USER SYSNAME NOT NULL,
AUDIT_TYPE CHAR(1) NOT NULL CHECK (AUDIT_TYPE IN ('I', 'U', 'D')), -- For each type of change
INVOICE_ID INT NOT NULL
)
CREATE TABLE INVOICES_AUDIT_DETAILS
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
INVOICES_AUDIT_ID INT NOT NULL REFERENCES INVOICES_AUDIT(ID),
AUDITED_FIELD SYSNAME NOT NULL, -- For storing the column than was audited
VALUE_BEFORE NVARCHAR(MAX), -- For storing the value in the column before the change
VALUE_AFTER NVARCHAR(MAX) -- For storing the value in the column after the change
)
-- 3. Service Broker Objects
USE OLTP_DB
GO
-- Turn on Service Broker
-- IMPORTANT - Open transactions in the database will be rolled back
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE is_broker_enabled = 0)
ALTER DATABASE OLTP_DB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
GO
-- Create Message Type of the Audit Message
CREATE MESSAGE TYPE AUDIT
VALIDATION = WELL_FORMED_XML
GO
-- Create Message Type of the Audit Message's Response
CREATE MESSAGE TYPE AUDIT_RESPONSE
VALIDATION = NONE
GO
-- Create Contract for the Conversation
CREATE CONTRACT AUDIT_CONTRACT
(AUDIT SENT BY INITIATOR,
AUDIT_RESPONSE SENT BY TARGET )
GO
-- Stored Procedure for ending the Conversation
CREATE PROCEDURE USP_END_AUDIT_CONVERSATION
AS
BEGIN
SET NOCOUNT ON
DECLARE @handle UNIQUEIDENTIFIER
WAITFOR (RECEIVE TOP (1)
@handle = CONVERSATION_HANDLE
FROM AUDIT_RESPONSES -- Queue which stores the responses
)
,TIMEOUT 10000; -- 10 seconds
IF @handle IS NULL
RETURN -- In case of a Timeout
END CONVERSATION @handle;
END
GO
-- Queue to store the Audit Messages
CREATE QUEUE AUDIT_MESSAGES
WITH STATUS = ON,
RETENTION = OFF
ON [PRIMARY]
GO
-- Queue to store Responses
CREATE QUEUE AUDIT_RESPONSES
WITH
STATUS = ON,
RETENTION = OFF,
ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = USP_END_AUDIT_CONVERSATION,
MAX_QUEUE_READERS = 10, -- Up to 10 instances of USP_END_AUDIT_CONVERSATION can be kicked off
-- depending on the load (Internal Activation)
EXECUTE AS SELF) -- The stored procedure will be executed as the current user
ON [PRIMARY]
GO
-- Initiator Service
CREATE SERVICE INITIATOR_SERVICE
ON QUEUE AUDIT_RESPONSES
([AUDIT_CONTRACT]) -- Message Contract
GO
-- Target Service
CREATE SERVICE TARGET_SERVICE
ON QUEUE AUDIT_MESSAGES
([AUDIT_CONTRACT]) -- Message Contract
GO
-- Queue for the External Activator Service to monitor
-- When messages arrive in this Queue, the External Activator Service knows there is work to be done
CREATE QUEUE EXTERNAL_ACTIVATION
WITH
STATUS = ON,
RETENTION = OFF
ON [PRIMARY]
GO
-- Service for External Activation
CREATE SERVICE EXTERNAL_ACTIVATION_SERVICE
ON QUEUE EXTERNAL_ACTIVATION
([])
-- System-defined contract for Event Notifications
GO
-- External Activation Event
CREATE EVENT NOTIFICATION AUDIT_EVENT
ON QUEUE AUDIT_MESSAGES -- When messages arrive in this Queue, the Event will be raised
FOR QUEUE_ACTIVATION -- The type of Event
TO SERVICE 'EXTERNAL_ACTIVATION_SERVICE' , 'current database'
GO
-- Stored Procedure which will be used to send the Audit Message to the Queue
-- Error handling has been avoided to keep the sample code brief
CREATE PROCEDURE USP_SEND_AUDIT_MESSAGE(@data XML)
AS
BEGIN
DECLARE @handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE INITIATOR_SERVICE
TO SERVICE 'TARGET_SERVICE'
ON CONTRACT AUDIT_CONTRACT
WITH ENCRYPTION = OFF; -- Messages are not encrypted
SEND ON CONVERSATION @handle
MESSAGE TYPE AUDIT
(@data)
END
GO
-- Trigger to build the audit message using the modified data
CREATE TRIGGER [dbo].[TG_INVOICES]
ON [dbo].[INVOICES]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @data XML, @table sysname
-- The table name is kept static to avoid any performance overhead associated with reading from
-- the metadata, however if overhead is not a concern, this can be done by reading from sys.triggers
SELECT @table = N'INVOICES'
-- Build audit message
SET @data =
'
'+ @table + '
' + SUSER_NAME() + '
' + CAST(GETDATE() AS VARCHAR) + '
' + ISNULL((SELECT * FROM INSERTED FOR XML RAW), '') + '
' + ISNULL((SELECT * FROM DELETED FOR XML RAW), '') + '
';
EXEC USP_SEND_AUDIT_MESSAGE @data
END
GO
2. External Activator Service’s Configuration File
External Activation Service
server=AJIT-LAPTOP;database=OLTP_DB;Application Name=External Activator;Integrated Security=true;
AJIT-LAPTOP
OLTP_DB
dbo
AUDIT_MESSAGES
c:\audit\AuditApp.exe
c:\audit
All Levels
All Modules
All Entities
Verbose
3. Auditing Application
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Imports System.IO
Imports System.Security.Cryptography
Imports System.Text
Imports System.Xml
Class Audit
'''
''' This sample code makes use of hardcoded values here. For a production system, these values must be passed in as command line arguments.
''' The values have been hardcoded to enable the reader to get the sample application configured quickly.
'''
Private Shared oltp_server = "AJIT-LAPTOP" 'Remember to change the value here (connection will be made using Integrated Security)
Private Shared oltp_database = "OLTP_DB"
Private Shared audit_server = "AJIT-LAPTOP" 'Remember to change the value here (connection will be made using Integrated Security)
Private Shared audit_database = "AUDIT_DB"
Private Shared message_queue_schema = "dbo"
Private Shared message_queue_name = "AUDIT_MESSAGES"
'''
''' Application name used when connecting to Sql Server.
'''
Private Const ApplicationName As String = "AuditApp"
'''
''' Defines how many milliseconds the RECEIVE statement should wait for messages.
'''
Private Const WaitforTimeout As Integer = 5000
'''
''' Predefined Sql Server message type name for end dialog messages.
'''
Private Const EndDialogMessageType As String = ""
'''
''' Predefined Sql Server message type name for conversation error messages.
'''
Private Const ErrorMessageType As String = ""
'''
''' Wraps the given Sql Server sysname in brackets and escapes any closing brackets already present in the name.
'''
Private Shared Function BracketizeName(sysname As String) As String
Return String.Format("[{0}]", sysname.Replace("]", "]]"))
End Function
'''
''' Services request messages by doing all the necessary computation.
'''
Private Shared Function ProcessMessage(payload As XmlDocument) As String
'Note that the code here has been written to only process the INVOICES table.
'For a production application, a generic processing function should be written
'All operations should ideally be done as part of a single transaction
'The logic caters to 1 record being changed at a time. If multiple records are created, altered or deleted in batches,
'then the logic should be changed to have an enumerator for records within the INSERTED and DELETED nodes
If payload.Item("AUDIT_MESSAGE").GetElementsByTagName("TABLE").Item(0).InnerText() = "INVOICES" Then
Dim audit_user As String = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("AUDIT_USER").Item(0).InnerText()
Dim audit_datetime As String = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("AUDIT_DATETIME").Item(0).InnerText()
'Decipher the type of operation based on the presence/absence of data from the INSERTED and DELETED tables
Dim audit_operation As String = String.Empty
If payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).HasChildNodes() = False Then
audit_operation = "I" 'Insert
ElseIf payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).HasChildNodes() = False Then
audit_operation = "D" 'Delete
Else
audit_operation = "U" 'Update
End If
Dim invoice_id As String = String.Empty
Dim audited_field As String = String.Empty
Dim value_before As String = String.Empty
Dim value_after As String = String.Empty
If audit_operation = "I" Or audit_operation = "U" Then
invoice_id = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes("ID").Value()
Else
invoice_id = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes("ID").Value()
End If
'Build connection string for the audit database
Dim csb As New SqlConnectionStringBuilder()
csb.ApplicationName = ApplicationName
csb.DataSource = audit_server
csb.InitialCatalog = audit_database
csb.IntegratedSecurity = True
'Create the audit records
Dim audit_id As String = String.Empty
Using sqlconn As New SqlConnection(csb.ToString())
sqlconn.Open()
Using sql As SqlCommand = sqlconn.CreateCommand()
mandText = "INSERT [INVOICES_AUDIT]([AUDIT_DATE],[AUDIT_USER],[AUDIT_TYPE],[INVOICE_ID]) VALUES (@date, @user, @type, @invoice); SELECT SCOPE_IDENTITY();"
sql.Parameters.Add(New SqlParameter("@date", audit_datetime))
sql.Parameters.Add(New SqlParameter("@user", audit_user))
sql.Parameters.Add(New SqlParameter("@type", audit_operation))
sql.Parameters.Add(New SqlParameter("@invoice", invoice_id))
audit_id = sql.ExecuteScalar()
End Using
'Build list of columns (For a production system, it is better to query the metadata to cope with changing table structures)
Dim list(1) As String
list(0) = "ID"
list(1) = "AMOUNT"
For Each column In list
audited_field = column
If audit_operation = "I" Then
value_before = ""
value_after = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes(column).Value()
ElseIf audit_operation = "D" Then
value_before = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes(column).Value()
value_after = ""
Else 'Update
value_before = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("DELETED").Item(0).ChildNodes(0).Attributes(column).Value()
value_after = payload.Item("AUDIT_MESSAGE").GetElementsByTagName("INSERTED").Item(0).ChildNodes(0).Attributes(column).Value()
End If
Using sql As SqlCommand = sqlconn.CreateCommand()
mandText = "INSERT [INVOICES_AUDIT_DETAILS]([INVOICES_AUDIT_ID],[AUDITED_FIELD],[VALUE_BEFORE],[VALUE_AFTER]) VALUES (@id, @field, @value_before, @value_after)"
sql.Parameters.Add(New SqlParameter("@id", audit_id))
sql.Parameters.Add(New SqlParameter("@field", audited_field))
sql.Parameters.Add(New SqlParameter("@value_before", value_before))
sql.Parameters.Add(New SqlParameter("@value_after", value_after))
sql.ExecuteNonQuery()
End Using
Next
End Using
End If
'Send response back so that conversation can be closed
Return "Audit Message processed!"
End Function
Public Shared Sub Main()
Dim csb As New SqlConnectionStringBuilder()
csb.ApplicationName = ApplicationName
csb.DataSource = oltp_server
csb.InitialCatalog = oltp_database
csb.IntegratedSecurity = True
' MARS is necessary to be able to send response messages.
csb.MultipleActiveResultSets = True
Try
Using sqlConnection As New SqlConnection(csb.ToString())
sqlConnection.Open()
Dim messageReceived As Boolean
Do
messageReceived = False
Using transaction As SqlTransaction = sqlConnection.BeginTransaction()
Dim receiveCommand As SqlCommand = sqlConnection.CreateCommand()
receiveCommand.Transaction = transaction
' Even if message_body is always XML, don't cast to XML inside the RECEIVE statement as this
' may cause issues with activation. Convert/cast to XML after the RECEIVE is done.
'
mandText = String.Format("WAITFOR (RECEIVE TOP(1) conversation_handle, message_type_name, message_body FROM {0}.{1}), TIMEOUT {2}", BracketizeName(message_queue_schema), BracketizeName(message_queue_name), WaitforTimeout)
Using reader As SqlDataReader = receiveCommand.ExecuteReader()
If reader.Read() Then
messageReceived = True
Dim conversationHandle As Guid = reader.GetGuid(0)
Dim messageTypeName As String = reader.GetString(1)
Dim messageBody As SqlBinary = reader.GetSqlBinary(2)
If messageTypeName = EndDialogMessageType OrElse messageTypeName = ErrorMessageType Then
If messageTypeName = ErrorMessageType Then
'Handle the error message
End If
Dim endConversationCommand As SqlCommand = sqlConnection.CreateCommand()
endConversationCommand.Transaction = transaction
mandText = "END CONVERSATION @handle"
endConversationCommand.Parameters.Add(New SqlParameter("@handle", conversationHandle))
endConversationCommand.ExecuteNonQuery()
Else
Dim payload As New XmlDocument()
payload.LoadXml(System.Text.Encoding.Unicode.GetString(messageBody.Value).Substring(1)) 'First char is eliminated as it is invalid
Dim responsePayload As String = ProcessMessage(payload)
Dim sendCommand As SqlCommand = sqlConnection.CreateCommand()
sendCommand.Transaction = transaction
'Note that the message type is hardcoded here. This should not be done for a production application.
mandText = "SEND ON CONVERSATION @handle MESSAGE TYPE [AUDIT_RESPONSE] (@body)"
sendCommand.Parameters.Add(New SqlParameter("@handle", conversationHandle))
sendCommand.Parameters.Add(New SqlParameter("@body", responsePayload))
sendCommand.ExecuteNonQuery()
End If
End If
End Using
mit()
End Using
Loop While messageReceived
End Using
Catch e As Exception
' Whenever anything fails, make sure the failure is visible in the event log.
'
If Not EventLog.SourceExists(ApplicationName) Then
EventLog.CreateEventSource(ApplicationName, ApplicationName)
End If
EventLog.WriteEntry(ApplicationName, e.ToString(), EventLogEntryType.[Error])
Throw
End Try
End Sub
End Class
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- wordpress passing data between pages
- wordpress business templates
- wordpress rss feed not working
- wordpress jquery is not defined
- create wordpress blog
- wordpress roles editor
- wordpress full rss feed
- wordpress rss feed settings
- wordpress rss feed plugin
- wordpress display rss feed
- wordpress rss feed link
- wordpress rss feed to post