SQL Server Audit

Overview

This chapter explains how the SQL Server auditing process works and how the ThinkServer module retrieves the auditing information.

The first requirement is to create a datasource for the SQL Audit. This is from where the audit data is retrieved. When the datasource is created, an auditing process (trace) is created on the server.

Once you have created a datasource, you can reduce the amount of audited data by applying filters. You can configure filters at monitor level and set conditions for setting health levels and assigning actions to invoke.

Please note that:

• The auditing process runs on the server, whether the ThinkServer service is running or not.
• The retrieved auditing events are stored in temporary files on the server.
• In SQL Server 2000 servers and later, data is stored in binary files in a system folder.
• The process created on a server receives events from the entire server, not just from specific database. If you only need to audit a specific database set Datasource filters on DatabaseName or DatabaseID variables.
• Auditing an SQL Server can have important effects on a server system. The input/output operations can increase to dangerous levels if the appropriate filters are not defined. Note that a simple query could generate 30 events that are written to a file or database. So it is very important to decide which users, databases, and types of query you need to audit.
• Retrieving a large amount of events may lead to storage problems on the server. If ThinkServer is not running or if the speed at which new events are generated is greater than the speed at which the ThinkServer reads the events, storage problems are likely to occur.
• Auditing events from the temporary files are retrieved from the datasource collection process using SQL queries.

SQL Server Template System Requirements

• SQL Server 2005 or higher