How to configure ApexSQL Audit in a large scale environment

Applies to:

ApexSQL Audit

Summary:

This article describes different approaches to configure ApexSQL Audit to audit large number SQL Server instances.

Description:

Auditing large number of SQL Server instances with large amount of data can negatively impact performance of both the dedicated VM where the central ApexSQL Audit instance is operating as well as audited production environments. When challenged with a large amount of audited data for a single repository database this article explains how to mitigate the amount of processed data per single repository to gain the optimum working pace.

ApexSQL Audit central repository operates with no or very low expected impact when auditing millions of audited events on daily basis and it is recommended to operate in the proposed range where on average for 1 central repository goes 10-12 audited SQL Server instances. So, in cases where the audited trail gets out of this range, multiple central repositories mold the solution

Quick tip icon

Disclaimer:

The number of SQL Audited servers per one central repository listed above is a general recommendation so there could be variations in specific cases, and it is not recommended to follow it as a rule, but more as a guideline

  • The first step in this configuration process is defining groups of the SQL Servers per same central repository on which the first consideration will be the relation between the SQL Server in terms of audited data. This simply means that, before deployment, some ahead planning to evaluate and create lists of audited instances to be grouped per central repository is in order. For example it would not be recommended that one central repository collects data from all high traffic SQL Servers, and another central repository collects data from all low traffic SQL servers; that way the very concept of grouping would remain unutilized
  • Once the grouping is complete, the next step is defining the client host for the central repository on which more context can be found in the installation recommendations article
  • Proceed by deploying the product as described in installing ApexSQL Audit against each and every central repository client host. Once the installation is completed, continue the process by adding the SQL Audited servers batch and finish by configuring with proper auditing filters as depicted in initial configuration article where the each central repository will be shaped out similarly to the below screenshot

    https://blog.apexsql.com/wp-content/uploads/2019/05/word-image-4.png

The process is completed once all the SQL Server groups are successfully added and configured for auditing.