Applies to
Summary
This article provides information for evaluators on how to evaluate ApexSQL Audit to match their auditing needs, but also as a reference for the first-time users
Description
ApexSQL Audit consists of three core components that are included in the installer:
- Central components – the software component that handles communication between the central repository database and the audited instances and feed data into central repository database
- Auditing components – the software component that gathers and processes SQL server session files
- Application management console (Graphical User Interface) – a single point of access to manage the product, define auditing policies, create and run reports for data analysis using a comprehensive suite for reporting, create alert definitions and manage notifications. This component can be separately installed on any host in the network to allow remote access and management whilst it is included as a component on the central host together with the central processor and central repository database
|
Quick tip: When the Install ApexSQL Audit option is selected in the installer’s Select components, the central processor, central repository database and Audit Main GUI will be installed all together |
ApexSQL Audit components and where to install them
The Central components can be installed in two different architectures::
-
1. Dedicated server/Virtual machine – Installation of ApexSQL on a dedicated server provides the full performance without the impact on the production database server. Dedicated server/Virtual machine installation type is best solution for large to enterprise organizations, data centers, ISP’s and MSSP’s
|
Quick tip: When installing the central repository database on a virtual machine, ensure that it is installed on a physical disk mapped to the virtual machine. Installing the central repository database on a virtual disk will affect performance and is inadvisable. |
-
Database server – Installation of ApexSQL Audit on a database server relieves the need for any additional hardware but can cause certain performance impact on the database server. Database server installation type is best solution for cloud or hosting implementations, OEM solutions and SMB’s
-
The central repository database requires SQL Server 2008, or later, to be installed
-
Carefully estimate the amount of audit data that compliance requests may generate, and ensure the central host has ample memory and database space
-
Keep in mind that one million events may require 1-2 GB of database space to store the audit data. A 10 MB session file may require over 200 MB of memory for processing of the collected events
-
The best way for estimating the volume of audit data is performing a test auditing of SQL Server instances for approximately two weeks, in order to track how much space is used by the repository databases. Use the resultant event collection rate to estimate the database size you will need to store and process audit data over time. Such a test also allows more precise planning of archival. For example, if an average of 1GB of audit data is collected then planned size of the database should be more than 30GB, with a plan to archive events every 30 days
|
Quick tip: The ApexSQL Audit central instance default port is 64561. Make sure that your Firewall/IP tables don’t block the central instance port. |
|
Quick tip: The amount of collected data tightly depends on defined audit settings. Test defined audit settings to identify real requirements and then set memory and hardware needs accordingly. |
Installing auditing components
Installing the auditing can be done on any host across the network to enable auditing of the SQL Server instances remotely. Installation of the auditing components is straightforward as all configuration is handled from the Audit Main GUI. Remote installation is initialized when opting to Add server in the configuration tab of Audit Main, GUI, and adding one will install the auditing components automatically on the machine in question.
The Auditing components are easily installed via the main installer by choosing the option to “Install server-side components”
|
Quick tip: ApexSQL Audit supports auditing a SQL Server Failover cluster instance. Detailed information can be found in the knowledgebase article: Deploying, installing and configuring ApexSQL Audit in a clustered environment |
Installing the ApexSQL Audit Main GUI
The main GUI standalone follows a similar installation procedure. It should be installed once the main application is set up. Upon installation, the only configuration needed is selecting the machine that hosts the main application and making sure port 64561 via TCP is open for communication.
The main GUI standalone will offer all the functionality from managing auditing targets, configuring filters and reports to managing alerts remotely. The remote access through the main GUI can be granted for any user or group of users based on the application roles. We differentiate three level of access:
- Administrator – Role associated to unlimited access
- Power user – All application features except for account administration
- Reader – A dedicated role for report only use. This type of users are not eligible to make any changes in the auditing configuration
Check the ApexSQL Audit status
Once ApexSQL Audit is installed and auditing instances added, checking the status of the audited servers is recommended by selecting the Overview tab. The Overview tab displays the aggregate information and the status of audited servers including the information about the last operation collected.
The central instance service/central repository database computer should always be turned on. Auditing performs best if a high-speed network exists between the computer that hosts the central instance service and the audited instance. Even though monitoring will work through a WAN or low-speed network, it possibly will cause a certain delay in collecting the data gathered by the auditing instance.
Useful link
