--- PDF PAGE 368 ---
353
Security log, then it is important that you consider, and potentially increase, the size of 
these logs before you begin using them for your audit. Also, work with your Windows 
administration team to decide on how the log will be cycled when full and if you will be 
achieving the log by backing it up to tape.
The SQL Server Audit can then be associated with one or more server audit 
specifications and database audit specifications. These specifications define the activity 
that will be audited at the instance level and the database level, respectively. It is helpful 
to have multiple server or database audit specifications if you are auditing many actions, 
because you can categorize them to make management easier while still associating 
them with the same server audit. Each database within the instance needs its own 
database audit specification if you plan to audit activity in multiple databases.
Creating a Server Audit
When you create a server audit, you can use the options detailed in Table 10-4.
Table 10-4.  Server Audit Options
Option
Description
FILEPATH
Only applies if you choose a file target. Specifies the file path, where the 
audit logs will be generated.
MAXSIZE
Only applies if you choose a file target. Specifies the largest size that the 
audit file can grow to. The minimum size you can specify for this is 2MB.
MAX_ROLLOVER_
FILES
Only applies if you choose a file target. When the audit file becomes full, you 
can either cycle that file or generate a new file. The MAX_ROLLOVER_FILES 
setting controls how many new files can be generated before they begin to 
cycle. The default value is UNLIMITED, but specifying a number caps the 
number of files to this limit. If you set it to 0, then there will only ever be one 
file, and it will cycle every time it becomes full. Any value above 0 indicates 
the number of rollover files that will be permitted. So, e.g., if you specify 5, 
then there will be a maximum of six files in total.
(continued)
Chapter 10  SQL Server Security Model

--- PDF PAGE 369 ---
354
Table 10-4.  (continued)
Option
Description
MAX_FILES
Only applies if you choose a file target. As an alternative to MAX_
ROLLOVER_FILES, the MAX_FILES setting specifies a limit for the number 
of audit files that can be generated, but when this number is reached, the 
logs will not cycle. Instead, the audit fails and events that cause an audit 
action to occur are handled based on the setting for ON_FAILURE.
RESERVE_DISK_
SPACE
Only applies if you choose a file target. Preallocate space on the volume 
equal to the value set in MAXSIZE, as opposed to allowing the audit log to 
grow as required.
QUEUE_DELAY
Specifies if audit events are written synchronously or asynchronously. If set 
to 0, events are written to the log synchronously. Otherwise, specifies the 
duration in milliseconds that can elapse before events are forced to write. 
The default value is 1000 (1 second), which is also the minimum value.
ON_FAILURE
Specifies what should happen if events that cause an audit action fail to be 
audited to the log. Acceptable values are CONTINUE, SHUTDOWN, or FAIL_
OPERATION. When CONTINUE is specified, the operation is allowed to 
continue. This can lead to unaudited activity occurring. FAIL_OPERATION 
causes auditable events to fail, but allows other actions to continue. 
SHUTDOWN forces the instance to stop if auditable events cannot be written 
to the log.
AUDIT_GUID
Because server and database audit specifications link to the server 
audit through a GUID, there are occasions when an audit specification 
can become orphaned. These include when you attach a database to an 
instance or when you implement technologies such as database mirroring. 
This option allows you to specify a specific GUID for the server audit, as 
opposed to having SQL Server generate a new one.
It is also possible to create a filter on the server audit. This can be useful when 
your audit specification captures activity against an entire class of object, but you are 
only interested in auditing a subset. For example, you may configure a server audit 
specification to log any member changes to server roles; however, you are only actually 
interested in members of the sysadmin server role being modified. In this scenario, you 
can filter on the sysadmin role.
Chapter 10  SQL Server Security Model