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
ApexSQL Audit contains five components that need to be installed:
- Central processor – the software component that handles communication between the central repository database and the audited instances
- Server-side components– the software component that gathers and processes SQL server trace files
- Central Repository Database – a SQL Server database that stores all data on the events captured
- Audit Main GUI – a GUI where you can perform configuration and maintenance tasks, as well as data analysis using a comprehensive suite for reporting. The GUI is installed together with the central processor and central repository database, but can also be installed standalone for remote management.
- Audit reports – a standalone version of the reporting suite from the main GUI, so non-technical personnel can be given access to reporting without access to configuration
When the Main application 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 processor and Central Repository Database can be installed in two different architectures:
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
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 computer 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 trace file that is 10 MB may require over 200 MB of memory for processing of the collected events
The best way for estimating 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 test also allows more precise planning of archival. For example, if an average of 1GB of audit data is collected than planed size of database should be more than 30GB, with plan to archive events every 30 days
The ApexSQL Audit central instance default port is 64561. Make sure that your Firewall/IP tables don’t block the central instance port.
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 only the server-side components
Installing the server-side components can be done on any computer across the network to allow auditing of the SQL Server instances remotely. Installation of the server-side components is straightforward as all configuration is handled from the Audit Main GUI and the server-side components are very light and with no GUI of their own. After starting the main GUI, the Add server dialog will list all the SQL instances in reach and adding one will install the server-side components automatically on the machine in question.
The server-side components are easily installed via the main installer as well.
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 main GUI standalone and Audit reports
The main GUI standalone and Audit reports follow a similar installation procedure. They 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 the 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 from the main application. It is, in fact, the same GUI. Audit reports will, on the other hand, offer all the functionality of the reporting module from the main application only, making it suitable for placement on machines that are in use by non-technical personnel that still need access to reports.
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.