MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.

I'm not curious

Advantages and Disadvantages of native SQL Server 2008 auditing

Published on 08 December 14

There were a number of auditing options available in the earlier versions of SQL Server prior to 2008. Most prominent of them were login auditing to track login events, SQL Trace to satisfy more than 40 auditing needs, DDL triggers to find out DDL events, C2 Audit mode and Common Criteria Compliance. These options were enough to meet most of the auditing needs but could not be relied upon to meet the external compliance requirements.

With SQL Server 2008, a new auditing feature - SQL Server Audit was released that provided a complete auditing solution for enterprise customers. Some of the most important features of SQL Server Audit include centralized storage of audit logs, integration with system center and no significant performance degradation of SQL Server. To top it all, you can even use it to perform fine-grained auditing where every user action against each object can be audited.

SQL Server Audit has been designed with the a number of goals in the mind such as the objects, audit logs and feature itself are secure, minimal performance overhead, easy manageability, and discoverability to ensure straightforward answer to audit questions.

Prominent advantages of SQL Server Audit in SQL Server 2008 are:

  • More aligned to meet external audit requirements such as HIPAA, SOX, PCI etc.
  • It gives option to audit at both Instance and database level.
  • It gives option to audit user activities more granularly.
  • It causes minimal performance overhead in comparison to the older versions.

In spite of these advantages, there are a few points which must be taken into account before one goes full throttle for SQL Server Audit.

  • Though it has been designed to cause minimal performance degradation, it does affect the server performance as it still uses the server resources. So, it may cause performance degradation if implemented on busy SQL Servers.
  • SQL Server Audit has been centralized to the extent of instances i.e. scripting is required to get the audit logs of all instances in one centralized place.
  • There is no built-in reporting mechanism to offer detailed auditing reports. Events can be analyzed using only the native event viewer.
In the later versions of SQL Server, these shortcomings have been covered to some extent and auditing has been made even more comprehensive and flexible. For example, SQL Server 2012 came with some highly desirable audit enhancements such as improved resilience, user-defined audit events, records filtering and many more. However, to make auditing experience even more user-friendly for non-technical users a number of third-party tools are available in the market. You can download and try LepideAuditor for SQL Server for auditing all SQL Servers in the network.
This blog is listed under Data & Information Management Community

Related Posts:
View Comment (1)
Post a Comment

Please notify me the replies via email.

  • We hope the conversations that take place on will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
  1. 11 December 14

    I would like to suggest you to try reliable and reasonable priced utility to restore all of your deleted MDF and NDF file from SQL server database. You can try this tool from here:-

You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url