Enabling Auditing feature in SQLServer 2005

Question

Did you ever use SQL Server auditing features on a production db?

How did that impact on performances, and are there differences you noticed between different versions of SQL Server.

Also how we need to enable the audit features.

Answer 1

The C2 audit mode suffers from a variety of problems, and your question touched on only one of them - performance. C2 auditing has a tremendous performance impact, very high.

Setting the performance issue aside, it's very difficult to manage. It's not a set-it-and-forget-it configuration switch. You have to spend quite a bit of time doing configuration, setting up the logging to go to files, and then when you're done, someone else can come behind you and get rid of the audit files pretty easily. There's no way to quickly poll all of your servers and make sure C2 auditing is working correctly, or that someone isn't dropping files.

SQL Server 2008 makes compliance much easier. I'd recommend taking a look at the SQL Server 2008 Compliance portal, which has an excellent whitepaper on how to configure 2008's new compliance features. 2008's new auditor uses the xEvent handling which has dramatically lower performance requirements and it's much easier to manage. You can use 2008's Policy-Based Management to check your servers, ensure you're auditing, and help get auditing configured again when things break.

Unfortunately, one weakness is still control of the audit output files - the bad guys can simply delete the files. Another weakness is a lack of reporting - just because you've got gigs of audit data doesn't mean you can do anything with it. You still have to write your own reports in order to analyze the audit data and find out who's doing what. It's not easy - but it's a lot easier and lower-impact than SQL 2005's C2 auditing.

Answer 2

C2 Audit mode is what you are looking for, triggers seem like not the way to go for auditing. What did you end up using?