August 29, 2011 at 12:18 pm
Can anybody please confirm if you already using SQL 2008 Audit feature for your environment and have analyzed the performance against Server side Trace , we are evaluating the audit feature for our environment but before starting to capture the performance comparison efforts , wanted to check the experience if anybody has done this already or using Audit feature and have any pros/cons or suggetions or any best practices?
Thanks
August 29, 2011 at 3:00 pm
Yes I have SQL Server audit enabled with some defined audits on all of our SQL 2008 Enterprise servers. I found a couple of things... a setting needs to be made in local security policy under "User Rights Assignment" and permission granted to the service account for the item "Manage auditing and security Log". I also found when setting up to write to the security log millions of errors were logged to the SQL Agent log. I did briefly contact someone at Microsoft regading this and I was supposed to get a fix from them. I never got that and I've not followed up on this. Since then, a couple of service packs have been released and I need to get this working. I'm not seeing anything in my logs at the moment.
August 29, 2011 at 5:34 pm
Thanks , how is the performance hit after enabling this ? also did you consider server side trace performance comparion with audit function?
August 29, 2011 at 11:58 pm
The SQL Server Audit mechanism is built on Extended Events which are much more light-weight than SQL Trace so there should be no noticeable impact.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply