Webinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
Ans: The MAXSIZE option specifies the maximum size that an audit file can reach before it's closed and a new file is opened, which is called "rolling over." The size is specified as an integer followed by MB, GB, or TB for megabytes, gigabytes, or terabytes, respectively. It's important to note that the minimum value allowed is one megabyte. Additionally, MAXSIZE can be set to UNLIMITED, which is the default value, allowing the audit file to grow to any size before rolling over.
Ans: The MAX ROLLOVER FILES option helps manage the file system automatically as auditing data accumulates over time. By default, this option is set to UNLIMITED, meaning that no cleanup is performed when new audit files are created. This could eventually fill up the disk. Alternatively, you can specify an integer value for this option, indicating how many audit files are retained in the file system as they roll over, while older audit files are automatically deleted.
Ans: The MAX FILES option, introduced in SQL Server 2012, requires manual file system grooming. When the specified number of files is created, SQL Server will start generating errors for any audited action. It won't roll over or delete old auditing files as they accumulate over time. This option is useful if you want to ensure that audit files are managed manually, and SQL Server never deletes audit files automatically.
Ans: The RESERVE DISK SPACE option is OFF by default, which means that disk space is dynamically allocated for the audit file as it grows to record more events. However, preallocating disk space for the audit file when it's created can improve performance and reduce disk fragmentation. When you set this option to ON, the audit file will be allocated the amount of space specified by the MAXSIZE option. To use RESERVE DISK SPACE=ON
, MAXSIZE must be set to a value other than the default UNLIMITED.
Ans: You can create and save audit objects to the Windows event log using the "TO APPLICATION_LOG" or "TO SECURITY_LOG" options. The former sends audit entries to the Application event log, while the latter sends them to the Security event log. This allows you to track specific events in these logs. An example of creating an audit object recorded in the Application event log will be demonstrated in an upcoming sample.
Ans: A server audit specification is created to monitor server-level events, such as failed login attempts or other actions not associated with any specific database. As mentioned earlier, specifications are linked to an audit object configured to record to either the file system or the event log.
Ans: To create a specification that monitors server-level events for auditing, use the "CREATE SERVER AUDIT SPECIFICATION" statement. The "FOR SERVER AUDIT" clause associates the specification with an audit object that defines the destination. The "ADD" clauses specify the server-level audit action groups that will be monitored. You can also use the "ALTER SERVER AUDIT SPECIFICATION" statement to add new action groups to be monitored or drop ones that should not be monitored. Audit specifications can be created and enabled simultaneously by using the "CREATE SERVER AUDIT SPECIFICATION" with the "STATE=ON" option.
Ans: A database audit specification is similar in concept to a server audit specification. Both specify events to be tracked and directed to a specific audit object. However, the key difference is that database audit specifications are associated with actions against a specific database, as opposed to server-level actions. These specifications are stored in the database for which they were created, and they can't audit actions in tempdb.
Ans: Database audit specifications, like server audit specifications, can be created with the "WITH (STATE=ON)" clause. The "CREATE DATABASE AUDIT SPECIFICATION" and "ALTER DATABASE AUDIT SPECIFICATION" statements function similarly to their counterparts for server audit specifications. Around 24 database-level action groups can be monitored for auditing, including changes in database ownership or permissions, and specific actions on database objects like schemas, tables, views, stored procedures, etc. The "SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES" actions are the seven database-level audit actions. You can create a database audit specification recorded to the event log.
-- Create an event log audit USE master GO CREATE SERVER AUDIT MyEventLogAudit TO APPLICATION_LOG ALTER SERVER AUDIT MyEventLogAudit WITH (STATE-ON) -- Create a new database IF EXISTS(SELECT name FROM sys.databases WHERE name = "MyDB") DROP DATABASE MyDB GO CREATE DATABASE MYDB GO USE MyDB GO -- Monitor database for all DML actions by all users to the event log CREATE DATABASE AUDIT SPECIFICATION CaptureDbActionsToEventLog FOR SERVER AUDIT MyEventLogAudit ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo BY public) WITH (STATE-ON)
SQL Server audit options and specifications provide essential tools for managing and securing your database environment. The MAXSIZE option allows you to control audit file sizes, while MAX_ROLLOVER_FILES and MAX_FILES
help automate file system cleanup. The RESERVE_DISK_SPACE
option can enhance performance by preallocating space. Server audit specifications enable monitoring of server-level events, and database audit specifications provide granular control over database-level actions. By understanding and effectively utilizing these options, you can enhance security, optimize performance, and maintain a well-organized database system.
SQL Server MERGE Statement: Question and Answer
Mastering INSERT and OVER DML Syntax: Interview Questions Guide
SQL CLR Deployment and Error Resolution: Question and Answer
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment