Recently I was exploring SQL Server Audit and found something very interesting. I found two enhancements in the SQL Server 2008 Create Audit Screen. One is new options on audit log failure, and the other is maximum rollover files.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
2 Comments. Leave new
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.
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.