ApexSQL Audit installation recommendations, best practices and alternatives

Applies to
ApexSQL Audit

Summary
In this article, we are going to describe three main installation approaches for ApexSQL Audit and provide information on when the specific approach is recommended over the other two.

Before we get into the installation practices, let’s quickly review all ApexSQL Audit components. This will help us get a clear view on individual components as well as on the ApexSQL Audit network topography.

There are three core components of ApexSQL Audit: main application, server-side components and reporting application.

The Main application is collectively the ApexSQL Audit core component which includes the central repository database, GUI, as well as windows service which allow auditing of all local SQL Server instances as well as communication with the central server-side components which allows auditing of remotely located SQL Server instances.

  • Central repository database is a SQL Server database that stores all audited data as well as all configuration detail, reports, alerts etc.
  • Graphical user interface (GUI) is a user interface that is used to configure auditing, alerting, reporting and all other ApexSQL Audit configuration and maintenance tasks
  • Central service is a Windows service which provides and manages all communication with local and remote instances of SQL Server. It provides configuration details and receives audited data.
  • Server-side components allows auditing of a local SQL Server instance and needs to be installed on each remote machine in order to audit SQL Server instances located on that machine

These four components are integrated together into the “main application” and are installed as a bundle. So, there can only be one main application in the audited environment, but additional full GUIs for both configuring and reporting can be installed on as many machines as needed. The main application needs to be installed on the workstation that will be used to configure auditing, alerting and reporting. Since it does include the auditing service, the main application can be installed directly on the machine that hosts SQL Server instances that need to be audited, or on a different machine that will be used as a workstation to operate with ApexSQL Audit remotely. The Main application and all included components are described in detail in this article.

In addition to being a part of the main application, the server-side components needs to be installed on remote machines to allow auditing of all SQL Server instances located remotely of the main application. Only one server-side components service needs to be installed per Windows Server to allow auditing of all SQL Server instances located on that specific machine. Also, it is important to know that there is no limitation on how many separate machines can be connected to one central service via server-side components, so there is no need to spend time on planning the architecture for every machine. Simply apply the server-side components to all machines that host SQL Server instances that need to be audited and all will be available to auditing from the centralized GUI.

The ApexSQL Audit GUI standalone can be installed on any machine to allow equal access as from the main application.

The ApexSQL Audit reports is the built in module in the application GUI to allow users to create auditing reports. This module can be used by installing the GUI part of the application on any machine inside the domain and setting accounts with the reader role to allow different users to create auditing reports.

More information on all ApexSQL Audit components, their relations and network topography can be found in this article.

Now that we’ve defined all ApexSQL Audit component relevant for the different installation practices, we can focus on the best implementation choices. The main question most DBAs will first ask is “Do I want ApexSQL Audit in my production?”, and the answers may warry from one environment to another.

1. Installing ApexSQL Audit directly on the production server

This approach is generally recommended when user wants to audit servers locally and have both SQL Server and all auditing components together on the same machine without the need to allocate any additional resources except for the production machine(s). Implementation of this approach is simple, and the user only needs to install the ‘Main application’ component on the machine and then to configure auditing as per the auditing needs and requirement.

Note that the central repository will need to be installed on one of the local SQL Server instances on that server machine.

Pros:

  • Quickest and easiest setup/installation
  • No additional physical resources required
  • Centralized workstation, all SQL Server processes and auditing mechanisms are located on the same machine
  • No requirements for additional SQL Server (and licenses)
  • No network related restrictions or limitations (speed, traffic congestion…)

Cons:

  • Auditing data will grow as the number of audited events rises, so disk space can get low in some cases after a while since the central repository is hosted on a local SQL Server instance
  • Running both SQL Server and ApexSQL Audit on the same machine may take enough resources to slow down processing and SQL Server itself in performance intensive production environments
  • This approach is not applicable to cluster nodes, since the main application will not be transferable between the active/passive nodes

In general, it is recommended to install ApexSQL Audit directly on the production machine where there is abundance of disk space and resources that can be committed to ApexSQL Audit without worrying that allocated resources will affect other applications or processes in the production environment. Also, being able to freely connect to the production machine to manage auditing at any given time is most welcomed. Here is a guide that describes how to install ApexSQL Audit directly in the production environment.

2. Installing on a remote physical machine (dedicated server)

The next approach for implementing ApexSQL Audit into the environment is to install the ApexSQL Audit main application (including central repository database, GUI, and central service) on a remotely located workstation and not on the production server itself. In addition to the main application being installed on this workstation, server-side components need to be installed on the production machine to allow remote auditing and communication with the central service.

More information on how to setup remote auditing with ApexSQL Audit can be found in this step by step guide.

Since the main application component includes central repository database that requires a local SQL Server instance, the workstation must possess appropriate SQL Server instance that will host the database that will store all audited data.

Pros:

  • No performance or disk space impact on the production server since all temporary auditing files and central repository database are located/stored on a different machine/SQL Server instance and all processing is also performed on this other machine/server
  • User doesn’t need to access the production server directly each time when auditing configuration or other mechanisms need to be tweaked or changed
  • Allows auditing of cluster servers

Cons:

  • Requires additional physical machine as well as one additional SQL Server instance (and license) that will host central repository database
  • Suffers from any network related restrictions or limitations (speed, traffic congestion…)

In general, it is recommended to use this approach when impact to the production server(s) needs to be minimal, and fewer resources of the production should be utilized. Also, when users have limited access to the production server, this approach allows immediate access to all configuration and setup processes even when logging to the production machine is not possible at a given time.

For the visual overview of remote auditing, check this article.

3. Using Virtual Machine as a dedicated server

This approach is similar to the previously described remote auditing process, but instead of using a separate physical machine to host ApexSQL Audit main application, a virtual machine is used instead. This virtual machine can be hosted directly on the production server, or on some network machine. Regardless of this choice, setting up this configuration will be the same as setting remote auditing from physical machine.

The main difference in comparison to the remote auditing that uses physical machine is that usage of VM allows more agility. Users can easily change configuration and resources used by the VM or even move entire VM to another host if required without affecting the auditing process or the production environment

Pros:

  • Provides the same pros as ‘regular’ remote auditing that utilizes physical machine
  • Virtual machine can be migrated to another host if required
  • Easy resource manipulation. Changing configuration of the machine and manipulating assigned resources is quick and easy

Cons:

  • Virtual machine requires specific amount of resources to run on the host machine
  • Requires additional SQL Server instance (license) to be installed on the virtual machine (in addition to the audited service), for the purpose of hosting central repository database
  • Suffers from any network related restrictions or limitations (speed, traffic congestion…)

Summary:

Depending on the domain and SQL Server environment that needs to be audited, as well as the available resources, one of the three main installation practices should be chosen:

  1. Installing ApexSQL Audit directly on the production server provides the quickest solution that will use production server resources when performing auditing (SQL Server processes, disk space etc.)
  2. Installing ApexSQL Audit on a dedicated machine provides solution that will leverage resources of another machine to run auditing which will ensure that minimal resources are used on the production machine. To implement this configuration, additional SQL Server instance is required in order to host central repository database that will collect all data audited across the environment and store it on the dedicate server
  3. Similar to using physical machine as a dedicated server, a virtual machine can be used for the purpose. While agility provided by the virtual machine can provide several benefits, in some cases it can be more expensive (resource wise) to run a dedicated virtual machine in comparison to running dedicated server on a physical machine

Additional resources:

 

Related posts:

  1. ApexSQL Audit installation guides
  2. Evaluating ApexSQL Audit – Installation terms, recommendations, and core components overview
  3. ApexSQL Audit guide for deployment and use