Centralized Asynchronous Auditing with Service Broker
By Mladen Prajdić
on 16 July 2007
| 4 Comments
| Tags: Administration, Service Broker
Article Series Navigation:
Service Broker is a new feature in SQL Server 2005. It is an integrated part of the database engine and it provides queuing and reliable direct asynchronous messaging between SQL Server 2005 instances only. In the future this is planned to be extended to non-SQL Server instances. This article shows how to use Service Broker and triggers to capture data changes.
How Service Broker works
What Service Broker does is it talks or
converses with other service brokers. It does that by exchanging messages in a
dialog conversation between two service brokers. Imagine two people talking to
each other. The words they exchange are messages and their conversation is a
dialog. To fully understand the basics we must become familiar with the
terminology and what does what. I will show only basic commands. More info can
be found in SQL Server Help better known as Books Online.
Commands are explained in the order of needed
object creation for Service Broker conversations.
The basis of everything is a message type.
A message type defines the validation of the XML message that will be
performed.
The general syntax is:
CREATE MESSAGE TYPE message_type_name
[ AUTHORIZATION owner_name ]
[ VALIDATION = {
NONE | EMPTY | WELL_FORMED_XML |
VALID_XML WITH SCHEMA COLLECTION
schema_collection_name
} ]
This is how to
create a simple message type with validation that conforms to well formed XML:
CREATE MESSAGE TYPE [//Audit/Message] VALIDATION = WELL_FORMED_XML
Next step is to create a message type contract.
This contract defines which message types are allowed in a conversation.
For example, if we take our two people talking from before, a contract means that they are only
allowed to talk about sports. Anything else is rejected by both
persons as garbage.
The general syntax is:
CREATE CONTRACT contract_name
[ AUTHORIZATION owner_name ]
( { { message_type_name | [ DEFAULT ] }
SENT BY { INITIATOR | TARGET | ANY }
} [ ,...n] )
In the SENT BY
part we specify which message is allowed to be sent from each point of the
conversation. So we can specify that the initiator can talk about sports and
women, while the target can only talk about sports.
This is how to
create a simple contract type with previously created message type that can be
only sent by the initiator:
CREATE CONTRACT [//Audit/Contract] ([//Audit/Message] SENT BY INITIATOR)
Next comes a queue.
A queue holds every message received by each point in the
conversation. Each point of conversation has it's own queue in which the
received messages are waiting for processing
The general syntax is:
CREATE QUEUE [ database_name. [ schema_name ] . | schema_name. ] queue_name
This is how to
create a simple queue which takes:
- a stored procedure name to execute when a new message arrives in the queue
- maximum number of concurrently running stored procedures (for very busy queues)
- and user under whose context the procedure will be run
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' );
Every queue is
associated with a service. A service exposes the functionality of
contracts associated with the service to other contracts. It defines which
message types the associated queue will receive. Other types are rejected. If
no contract is specified then that service can only be an initiator of the
conversation so we have to specify which queue it will use and which contrats
are allowed.
The general syntax is:
CREATE SERVICE service_name
[ AUTHORIZATION owner_name ]
ON QUEUE [ schema_name. ]queue_name
[ ( contract_name | [DEFAULT] [ ,...n ] ) ]
This is how to
create a simple service with previously created contract on our previously
created queue with dbo authorization:
CREATE SERVICE [//Audit/DataWriter]
AUTHORIZATION dbo
ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
Building the Centralized Asynchronous Auditing System
Now that we're
familiar with the basics of Service Broker we can go on with building our
auditing system. Auditing is the means of tracking changes of your data. It
provides you with a log of who did what when. In the US and EU it is also
required by law for sensitive data.
Auditing is
usually done by inserting changed data into the accompanying audit table in the
trigger of the source table. Or it can be a part of the Update, Delete and
Insert stored procedures in which case we don't need triggers. Simplest way to
implement auditing is for every table to have another audit table with the same
structure. The downside is that these audit tables grow very fast. This
increases the database size and backup/restore times which is a negative side
effect. With a lot of tables this becomes cumbersome an hard to maintain.
Imagine the work you have to do if you have 10 databases with each having 40
tables. That's 10 x 40 x 2 = 800 tables you have to create. And those are small
databases table-wise.
Another option is
to use third-party Log Readers but they aren't fun, are they? :)
I went about it
differently. I wanted to have only one database that will hold all my audited
data from every database I have on the server. This way my other databases would
be free of bloated audited data. If i wanted to query the audited data i ccould
simply select from one table that holds everything. I also wouldn't want this
scenario to impact my performance. How to implement this? Service Broker to the
rescue.
Service Broker's reliable
asynchronous messaging was the perfect solution. I still used triggers but the
technique can be easily used in stored procedures and OUTPUT clause. I created two
databases each with its own Service Broker. The auditing database is called MasterAuditDatabase and the database to be audited is called TestDb1. Both databases have SET TRUSTWORTHY ON which enables us to use cross-database service broker communication without the use of certificates. Each
database has its own error table that holds errors that happen in Service
Broker communication. Yes, they may happen :)
When an insert,
update or delete happens our audit trigger simply takes appropriate data from the
inserted and deleted pseudo tables, turns it into our well formed XML message
and uses Service Broker to send it to another Service Broker in the Master
Audit Database which then saves it to our auditing table. Every time a new
message arrives to our target queue, a stored procedure is executed that inserts
the queued message into the audit table.
For busy systems only one stored procedure couldn't possibly cope with all incoming messages.
That is why we set the MAX_QUEUE_READERS = 50. To allow 50 concurrent
(parallel) queue readers. Because this is a completely asynchronous operation there's no impact on
performance on the initiator end. The triggers return immediately.
The code can be downloaded and is well documented so I hope it speaks
for itself. The code is broken into two scripts.
- The Master Audit Database (the database that holds all audited information) and its
Service Broker infrastructure
- Sample
audited database and its Service Broker infrastructure
Note: You
will need to copy the GUID returned by the first script and use it in the
second script.
This method
of auditing proved to be very good with no noticeable impact on performance. Of
course the master audit database grows fast and horizontal partitioning will be
surely needed. It can also be
used with multiple servers where one server serves as
keeper of audited data from other servers. To do this we'd have to create
TCP/IP endpoints and create users, certificates and other security measures
which I will show how to do in the next article.
Conclusion
Service Broker is
a great addition to SQL Server
and it's use has just barely scratched the surface. The future is leaning to
service oriented architecture (SOA) and loosly coupled applications. With the
coming of .Net 3.5 and adoption of Windows Communication and Workflow
Foundations, Service Broker will fit in nicely. So learn it.
