SQL Server Portal

SQL Server 2012 came up with lots of new exciting features along with great enhancements as well. One of the enhancements available in SQL SERVER 2012 is its audit features.

Given below are the details of the enhancements:

• Database level auditing is available in Developer, Enterprise & Evaluation editions but its support is available in all editions of SQL Server. So, now you can take the benefit of database audit in every edition.
• Another major issue is resolved using this new enhancement. Lets say if you setup a target directory over the network and due to any reason (e.g network failure) the target is not available, you usually lose the data in the earlier version. But in SQL Server 2012, it will automatically recover once the connection is restored.
• Now, you can restrict the number of files to be created for audit. This will stop creating unnecessary files in the audit folder. But there’s one more option to make it unlimited. (Note: be careful when you use this option, it requires proper maintenance).
• This feature is basically helpful for the database developers because now it gives the additional information of audit log and you can even trace whether the SQL Query is passed through application directly or via stored procedures.
• Now you can also write audited events in the audit log using a new stored procedure (sp_audit_write) introduced in SQL Server 2012.
•  Using user-defined audit event, now applications can write custom audit information in the log as well.
• To keep a track of user defined events, SQL Server has provided the new catalog views (sys.server_audits, sys.server_file_audits) .
• It is also very easy to filter the audit events before it is written into the audit files. So you can customize the type of events you would like to capture. Otherwise you need to capture unnecessary events.
• As we discussed contained database features in my earlier article, now you can monitor contained database users & its activity as well.
• And the last but not the least, the dialogue boxes are now available in SQL Server management studio. So not only from script but also you can get much more control than earlier version through SSMS as well.