In chapter 28, Jasper Smith (Blog) introduces us to the built-in auditing features of SQL Server 2008. This.is.awesome. For those of you who have had to “build your own” dml and ddl auditing in the past, it was not intuitive nor was it an easy “out of the box” solution.
A few years back (o.k., 9 years), I was tasked with creating a dml audit for all select, update, insert and delete statements against tables that contained personal information. This effort required a continual trace for DDL and another for DDL. Once I had trace files being created, I then wrote additional t-sql and bcp jobs to insert the data into a set of tables so that the security team could review who was looking at what data, where they were logged in from, etc… While it was a great excercise for me from a professional development standpoint, it wasn’t as easy as the point-n-click implementation that Jasper walks through in this chapter.
I would highly, highly recommend that every DBA out there set up DDL auditing in their environments. It can help with so many issues that come up. I have several clients who run a lot of Commercial Off The Shelf (COTS) packages and when upgrades happen in these environments and the database slows down or rapidly speeds up, many DBA’s are left out in the cold wondering what happened. This most often means that a call has to be put into the vendor and the DBA spends a significant amount of time understanding what happened and why it had the effect that it did to their systems. If DDL auditing had been set up, it would have been a quick check and may not even involve having to call into the vendor… score!
Another common scenario is a new release of your custom software will go out and during the deployment phase a piece of DML code updates too much data or not enough data and the release has issues. Having the ability to know what ran, when, by whom etc… is truly invaluable in these scenarios.
Thanks for a great chapter Jasper!