Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 265

How to do it...

The following steps are essential to implement auditing and service level security features on
an existing SQL Server instance. The initial phase of the implementation is concentrated on
the auditing features:

1. The first step in configuring the auditing for the SQL Server instance is to create
a Server Audit object with a file target or Security log from Windows event viewer
log target.
2. To create a server audit object of a file target, we will use the CREATE SERVER AUDIT
command and the steps are as follows:
USE master
GO
CREATE SERVER AUDIT DBIASSQA_Marketing_Server_Audit
TO FILE
( FILEPATH = 'd:\Entz\FS\',
MAXSIZE = 500 MB,
MAX_ROLLOVER_FILES = 15,
RESERVE_DISK_SPACE = OFF)
WITH ( QUEUE_DELAY = 1500,
ON_FAILURE = CONTINUE)

The default option for the ON_FAILURE option is CONTINUE. This argument
is essential and the login issuing this statement must have the SHUTDOWN
permission. If the login does not have such permission, then the function
will fail with an error: MSG_NO_SHUTDOWN_PERMISSION message.

3. To create a server audit object to the Windows application log, use the

following statement:
USE master;
go
CREATE SERVER AUDIT DBIASSQA_Marketing_Server_Audit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO

The QUEUE_DELAY is determined in milliseconds, and the minimum
delay value that can be set to process an audit is 1000 , which is one
second and the maximum value is 2,147,483,647 , which is 24
days, 20 hours, 31 minutes, and 24 seconds.