Applies to
Summary
This article is meant to describe ApexSQL Audit configuration blocks and explain how to configure SQL server auditing session settings to achieve greater control on audited data flow.
Description
To fully take advantage of the hardware resources while implementing SQL server audit configuration across the environment, ApexSQL Audit provides an extended level of settings and allows you to easily manage how the auditing sessions will be created and consumed.
In other words, auditing data flow can be customized to an extent that offers higher control management and eases the process of applying SQL server audit configuration within an environment, especially when the data flow management requires to be considered to a certain degree. ApexSQL Audit offers settings to describe how audited data is being populated in the session files, at what frequency the data is synchronized with the central repository database and more.
This configuration setting is brought through the Auditing agent properties dialog, and it can be prompted in two different scenarios:
- When adding a new SQL Server instance
- Or when editing existing SQL Server instance auditing configuration
Regardless of the approach, the agent auditing properties will ask for fewer basic information to collect in order to put auditing into motion for the SQL Server:
-
Credentials
Windows user or group managed service account details with administrative permissions on both SQL Server and Windows level
-
Advanced
This configuration block is already filled with default values for:
- Technology – this control is set to Auto and it is meant to apply certain SQL auditing method, with the ability to choose from three different options:
- Trace – applicable for all SQL Server versions
- Extended Events – applicable for SQL Server 2012 and higher
- SQL Audit – applicable for SQL Server 2017 and higher
Quick tip:
The Auto option applies the best available auditing method for the audited SQL server instance, prioritizing SQL Audit over Extended Events, and Extended events over SQL Trace
- Central host name – Central repository host identifier, it is by default populated with the current machine name
- Timeout
- Files location – A placeholder of the audited temporary data storage that is used to temporarily store audited data in form of a flat-file before the auditing data is forwarded to the central repository database
- Technology – this control is set to Auto and it is meant to apply certain SQL auditing method, with the ability to choose from three different options:
-
Folder size limit – Control that allows specification of the folder size limit where session files being stored temporarily. When enabled, the auditing temporary folder will allocate memory up to the specified size limit, and it may result in an auditing pause in cases when auditing session files consume memory up to the limit
Quick tip:
Folder size limit is disabled by default. It is recommended to keep the temporary folder unlimited and avoid audit data loss due to potential size limit pauses
- Push frequency – An interval to define maximum time before audited the agent sends files to the central repository if the files are not received in accordance with session file gather frequency
- Session file size limit – Session files rollover size on which audited agent should send audit data and create a new file to continue collecting audit trail
- Gather frequency – A frequency at which the audited agent is in correspondence with SQL Server to populate the session files with SQL events
While it is a common use case where the login details are the only user input to configure auditing agent properties, on the other hand, the advanced users opt to modify additional options to drive the SQL Server auditing accordingly to specific needs.
Besides basic data inputs, additional advanced options are rendered when “show more options” is tickled in the very same dialog:
In the expanded options, the following controls are available to configure
With this broaden the number of options it is available to drill down into detailed properties and control the audited data flow in the working environment, in other words, this control set would let you designate the heartbeat pace and the blood pressure if the SQL Server audit data flow is compared to the human circulatory system.
