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.
This looks like exactly the type of solution I've been searching for. Has the author or anyone else for that matter run this in a production environment for any length of time? Are there any major draw backs to this type of auditing in a large OLTP environment? I'm anxious for part two of this article to get more information on configuring service broker across servers. Can you recommend any good service broker learning resources?
one little comment is that your TestDatabase.sql script fails on a server with case-sensitive collation, because the Person.ID column is referenced as "id" later on in the script, thus:
UPDATE Person SET DateOfBirth = '19800217' WHERE id = 5
why do I have a case-sensitive collation on my server? The only reason is so that I am guaranteed that any code I write will work on any server that happens to be case-sensitive (some of my customers use it, so I force myself to use it also).
efelito: i've run it on production for a month withoput problems, but note that this production environment wasn't very high transactional. you'll have to try it for yourself
jezemine: you know i haven't even thought about server collation. thanx for the tip.
Article: Centralized Asynchronous Auditing with Service Broker
Posted - 07/16/2007 : 11:06:57
