SQL SERVER – 2012 Auditing Enhancement – On Audit Log Failure Options – Maximum Rollover Files

Recently I was exploring SQL Server Audit and found something very interesting. I found two enhancements in the SQL Server 2008 Create Audit Screen.

SQL Server 2012 Create Audit Screen

SQL Server 2008 Create Audit Screen

On Audit Log Failure Options

You can see that in SQL Server 2012 they have added two more options for audit log failure. In earlier version the only option was to shut down the server when there was audit log failure. Now you can fail the operation as well continue on log failure. This new options now give finer control on the behavior of the audit failure scenario.When target is not available due to any reason and audit cannot log the event, it can be now continued, in another word the audit continues to attempt to log events and will resume if the failure condition is resolved. This is very important feature because earlier when Audio was failing the option which we had was to shutdown the server. There were the cases when shutting down the server is not the good option but continuing the business operation is the priority, this option should be exercised. Additionally, note that the new default value is now CONTINUE. User has option to select Fail Operation where the Audit will not attempt when the target is available or continuing auditing is possible.

Maximum Rollover Files

Earlier, there were two options – have infinite number of log files or roll over the files after fixed number. Now in SQL Server 2012 there is option to keep the fixed number of the files along with no roll-over. This gives additional control to user when they want to save every single data and do not want to lose any critical information due to rollover.

Reference : Pinal Dave (http://blog.sqlauthority.com)

3 thoughts on “SQL SERVER – 2012 Auditing Enhancement – On Audit Log Failure Options – Maximum Rollover Files

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  2. I had been hoping that maybe in 2012 they would add query start/stop times into the audit log, but alas they did not. Any thoughts on how to audit start/stop times on all queries on a server. I’m wanting to make a report of our long running queries to try and start working on making them more efficient. Our report writers are not necessarily thinking in terms of efficiency. Unfortunately, I’m not finding any good leads for how to accomplish this.

    Like

  3. I had been hoping that maybe in 2012 they would add query start/stop times into the audit log, but alas they did not. Any thoughts on how to audit start/stop times on all queries on a server. I’m wanting to make a report of our long running queries to try and start working on making them more efficient. Our report writers are not necessarily thinking in terms of efficiency. Unfortunately, I’m not finding any good leads for how to accomplish this.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s