A glance at SQL Server Audit
Microsoft does few things right and one such thing was SQL Server Audit. Being audit compliant is not an option for most businesses anymore. Â Previous to SQL Server 2008, SQL Server audit involved setting up login audits, sql trace a.k.a sql profiler, DDL triggers and so on. Starting from SQL Server 2008, Microsoft decided to include a new feature called SQL Server Audit to be more in line with the compliance arena. Â While SQL Server 2008 Standard edition provides the SQL Audit Foundation (basic), the Enterprise edition provides a very robust and detailed audit capability.
The Enterprise Edition lets you setup  both at the instance and database level auditing. Even though Microsoft claims a significant performance improvement on SQL Server Audit vs SQL Trace, any sort of audit generates I/O and lots of them.  It takes storage to store all these audit logs as well.  Use due diligence  when choosing what all you want to monitor. I suggest you make a list of all the different actions you want to audit and then write down what all audit action groups you want to use.
Setting up audit in sql server is a 2 step process unlike the DB2 audit process I mentioned in an earlier post .  Setting up sql server audit begins with  opening SQL Server Management Studio and connecting to the instance you want to audit :
1. Creating Server Audit Object
The Server Audit object defines the destination of the audit files and some high level configuration such as the delay queue, the maximum size of the audit files and so on. Once you define the server audit objects, you need to enable the audit. You can create multiple server audit objects to group similar type of audits for different purpose. To configure, right click Audit under the Security options and select ‘New Audit‘.  Ensure that the destination you choose have enough storage to hold the audit data until your audit reporting software can pick it up. Also setup the options to clean up the files.
. 
2. Creating Server or Database specifications
As the name suggests, in this step you configure the actions you want to monitor. You can find Server Audit Specification under the Security options. Right click and choose ‘New Server Audit Specifications‘. Here you can choose all the actions that you want to audit at the server (instance) level and link it to the server audit objects you created in the first step.
To configure the database audit, open the database you want to audit and find the Database Audit Specification under the Security option. Right click and choose ‘New Database Audit Specifications‘. Choose all the actions you want to audit against the database objects and also choose the userids you want to audit.
Use this link to select the appropriate action type for Server and Database .
Once you have defined  either or both the audit specifications, right click on each of them and enable them to start the audit.
Now that you have enabled the audits, you can/should review the audit logs to ensure that the audit is collecting all the information you needed. Â To view the logs, you can either go to the event view on the windows server or right click the server audit object you created in step 1 and choose view audit logs.
Just like you enabled audits, you can disable audits as well. Â I cannot stress enough the importance of carefully selecting the actions you want to audit. Make sure you are auditing only what is required, otherwise you are going to pay the price for I/O , CPU and storage.
Some of the links that are useful are:
http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
http://msdn.microsoft.com/en-us/library/cc280663(v=SQL.100).aspx
http://msdn.microsoft.com/en-us/library/cc280472(v=SQL.100).aspx