Sql-server – Enabling Auditing feature in SQLServer 2005


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.

Best Solution

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.