Advantages and Disadvantages of native SQL Server 2008 auditing

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.