Applies to
Summary
This article provides information about how to customize the target server’s Quarantine folder options as well as how to set quarantine-related system alerts in ApexSQL Audit.
Description
Starting from ApexSQL Audit 2024, the ability to customize the Quarantine folder’s size and location for the target server is available in the Advanced section of the Auditing agent properties dialog, which is brought up when adding a new SQL instance for auditing or editing an existing one. The quarantine options that are at disposal are almost identical to what is present in the advanced settings for Data storage and Session files.
Setting a size limit for the Quarantine folder enables the utilization of the newly added Quarantine folder system alerts which can be further customized to accommodate the necessary requirements.
Customization of the Quarantine options and alerts will not be available for the Central server, and will only be present when configuring audited servers.
Setting Quarantine folder options in the Auditing agent properties dialog
The customization of the Quarantine folder size and location can be achieved in two scenarios:
- When adding a new SQL Server instance for auditing
- Or when editing the existing auditing configuration of a SQL Server instance
In both cases, the Auditing agent properties dialog will be brought up with appropriate input fields and options. The Quarantine folder settings can be accessed by selecting the Advanced tab and then navigating to the Quarantine folder settings.
The Quarantine folder includes two settings which can be customized:
- Files location – which enables the user to set a custom location for the Quarantine folder, allowing further granularity in controlling the allocation of system resources based on auditing needs and requirements.
The folder is set by default to have the same location as temporary auditing session files (set in the Data storage section).
The only prerequisite for selecting the custom location is that it is accessible from the central server, be it a local or a remote path located on a network drive.
- Size limit – which enables the user to set a limit for the Quarantine folder’s size in order to optimize disk usage and prevent the accumulation of files when there is a known issue in the environment and it does not affect compliance.
It is of high importance to understand that after reaching the size limit which was set for the folder, ApexSQL Audit would not send files to quarantine anymore, but would delete them instead.
The size limit option is disabled by default (with the value set to No), but can be enabled by selecting Yes from the drop-down. This will bring up an additional input field where a numerical value can be entered for the size.
Just as it is the case with the Data storage option, the Quarantine folder size limit is set in gigabytes (GB).
Setting up Quarantine folder system alerts
With the release of ApexSQL Audit 2024, three new system alerts have been added to the Manage section and they pertain to the following use cases:
- Package(s) sent to Quarantine alert – when new files are detected in the Quarantine folder
- Quarantine folder disk space alert – when files are not being sent to the Quarantine folder because the specified folder size or storage space available on disk has been exceeded
- Quarantine reaching size limit alert – when the size of the Quarantine folder reaches the specified percentage of the folder size limit
Package(s) sent to Quarantine alert
In case there is a problem with the processing of a specific event, ApexSQL Audit will flag the auditing session file and send it to the Quarantine folder. This allows the user to examine the problem as well as to process the file at a later time (if the issue in question can be remedied).
The Package(s) sent to Quarantine alert is created and enabled automatically with the installation of ApexSQL Audit and it notifies the user when new auditing files are detected in the Quarantine folder.
The default frequency for checking if there are new files in quarantine is 1 hour, meaning, that a check would be performed every hour. This, as well as other settings can be customized via the Alert wizard, as is the case with all ApexSQL Audit alerts.
When the alert wizard is started for Package(s) sent to Quarantine alert, the first step offers the ability to edit the alert message using the available system variables (Condition, Creation date, Instance name, Quarantine packages, etc.) and to change the Severity of the alert if required.
The following step allows the user to choose which audited instances should be monitored by the alert. By default, all instances are selected.
In the next step, the alert condition can be modified. It represents the frequency at which the Quarantine folder is scanned for new files,.
Start – Setting a starting date configures the point in time from which the condition will be applied. The date can be chosen from the embedded calendar and the time can be specified in the field next to it.
Repeat – Next, a numerical value can be set by entering a number in the appropriate field while the available unit of measurement (Day, Hour, Minute) can be selected from the drop-down. As previously stated, the default value will be set to 1 Hour.
The fourth step in the wizard offers the functionality of sending an email notification when the alert is triggered along with the option to enable writing the alert to the Windows event log (which is no longer enabled by default).
For more information about setting up SMTP servers and recipient accounts, review the Email configuration section of Overview of general SQL auditing administration options KB article.
The final step provides an overview of the alert’s configuration and the only remaining actions are to alter the default name of the alert (if desired) and to click on Finish to complete the wizard and apply the configuration.
Package(s) sent to Quarantine alert will only be triggered when there are new files in the Quarantine folder since the last check, and will not be triggered for files that are already present and for which the user was previously notified via this alert.
Quarantine reaching size limit alert
The Quarantine reaching size limit alert requires additional setup for achieving the desired configuration.
The alert can be enabled by setting a size limit for the Quarantine folder in the Auditing agent properties as described in the section related to setting a size limit for the Quarantine folder. This alert will notify the user when the Quarantine folder reaches the size equivalent to the specified percentage of the folder’s size limit (80% by default)
To customize the alert condition and properties, the Alert wizard needs to be started. All steps in the wizard that are not directly related to the conditions for triggering the alert are the same as the ones mentioned in the section about customizing the Package(s) sent to Quarantine alert.
For example, in a scenario when the default value of 80% is selected, and the alert is enabled
by setting the Quarantine folder size limit to 10 GB – the alert would be triggered when the folder’s size reaches 8 GB (80% of its size limit).
The frequency at which the alert’s condition will be evaluated is another option which is shared by all ApexSQL Audit alerts and is set to 1 Hour by default for the Quarantine folder reaching size limit alert.
Quarantine folder disk space alert
The Quarantine folder disk space alert is another alert which is automatically enabled upon installation.
Its sole purpose is to inform the user when files which are flagged for quarantine during processing will not be sent to the Quarantine folder anymore due to lack of storage space. These files would be discarded and will not be available for processing at a later time.
Depending on user configuration, there are two use cases in which this alert can be triggered:
- When the user has set the maximum size limit for the Quarantine folder – the alert will be triggered either when the size limit of the Quarantine folder has been exceeded, or when the storage drive which hosts the Quarantine folder has reached its full capacity.
- When the user hasn’t set the maximum size limit for the Quarantine folder – in this scenario, the alert will only be triggered when there is no storage space available on the drive where the Quarantine folder is located.
As is the case with other alerts, the Alert wizard can be used to alter its condition and properties, as explained in previous sections.
The most prominent characteristic of the Quarantine folder disk space alert is the warning icon and message which will be displayed in the Status tab when the alert is triggered. It informs the user that newly unprocessed files will not be sent to the Quarantine folder, ensuring that the user is immediately informed of the issue while working in the UI.
Best practices for using additional options for the Quarantine folder
As previously stated, by default, the Quarantine folder is placed in the same location as the temporary auditing session files.
In use cases where there aren’t any known issues (such as problems with parsing particular queries) that might potentially lead to a greater number of quarantined files – preserving the default settings is a viable and recommended option.
However, in scenarios where it is expected to receive a larger number of quarantined files – providing an isolated, custom location for the Quarantine folder, which contains sufficient storage space, might be the most efficient solution to ensure that ApexSQL auditing and other processes on the server are not affected by a considerable increase in the number of files that are sent to Quarantine.
Advanced quarantine options and alerts allow for a more comprehensive overview of the flow of data in the environment and assist the user in detecting any potential underlying issues which might threaten adhering to compliance standards.
