Applies to
Summary
This article outlines the supported disaster recovery solutions that can be utilized for hosting the ApexSQL Audit central repository.
Description
ApexSQL Audit 2024 introduces a novelty in securing the completeness of auditing data with the ability to configure the central repository on a remote clustered SQL instance, and use the failover mechanisms offered by Availability Groups and Failover cluster instances.
This will ensure that an uninterrupted auditing trail is produced by failing over the central repository database to another cluster node.
This solution does not include automatic configuration and setup of SQL clusters by ApexSQL Audit and can only be deployed in already prepared Always On High Availability Groups or Failover Cluster Instances.
ApexSQL Audit central repository installation on a clustered system
To use the central repository disaster recovery feature offered in ApexSQL Audit 2024, It is important to understand the difference between the central application process and the temporary auditing files on one side, and the central repository database on the other.
Although the entire disaster recovery setup will be configured through a single (local) installation process, two dedicated systems are required for deploying the central repository on a clustered instance:
- The machine where the ApexSQL Audit application will be installed locally. This is where the central application process and the temporary auditing files are going to be placed.
- And the remote SQL cluster where the central repository database will be deployed.
This means that Audit’s disaster recovery mechanism only allows for a remote central repository deployment and does not support the installation of the main application on a cluster node because the central application process (and the temporary auditing files) would not be available after a failover, and would consequently lead to data loss.
Both systems involved in the remote central repository deployment need to be located in the same network and also require connectivity to all servers that are to be audited in order to allow proper authorization and communication between components.
Permissions for deploying the central repository on a clustered instance
The required permissions, which can be found in the Permissions and requirements for SQL auditing knowledgebase article, need to be in place on all nodes in the SQL cluster that is being used as the central repository host before starting the installation.
Also, it is important to stress, that the following permissions are necessary on the machine where the main application is installed (where the application processes and temporary auditing files are placed):
- local administrator permissions for the account which is running ApexSQL Audit central application process.
- full permissions to the path where the temporary auditing data is placed for the account which is running the SQL Server service of the remote clustered instance.
The new ApexSQL Audit central repository
Starting with ApexSQL Audit 2024, the central repository setup will be comprised of two separate databases:
- ApexSQLCrd – the database which contains the information about the auditing configuration set by the user in the UI
- ApexSQLAuditData_YYYYMMDD_HHMMSS – the database which contains the auditing data collected from the monitored instances
This setup is deployed with every ApexSQL Audit installation, regardless of whether the repository is installed on a clustered or a standalone instance.
Separating the configuration from the auditing data simplifies the archiving process and enables remote repository deployment. From the user’s perspective, the only difference will be that the archived repositories will no longer contain the configuration data and they will not be renamed after being archived.
Deploying the Central Repository in an Always On Availability Group
Configuring an Availability Group as the host for the ApexSQL Audit central repository is achieved through the installation process.
After the installation is started, select the appropriate component that is to be installed (Install ApexSQL Audit) and choose an installation directory.
The next step in the installation wizard is the ApexSQL Audit central instance setup.
The first and most important option is specifying the remote Availability Group which will host the central repository either by manually entering the Listener name of the Availability Group or by selecting an existing one from the SQL Server combo-box drop-down.
After validating that an existing Availability Group has been specified, the Backup location option will be visible within the Central Repository database section.
Backup location is a mandatory field which is unique to the use case of installing the repository in an Availability Group, meaning that, this additional field will not be displayed if a standalone or a Failover cluster instance is selected. It will be used to specify the path where a full backup of the central repository will be made in order to meet the requirements for joining the Availability Group. If the backup location is not specified, the installation process will not be continued, and the following message will be displayed.
Once the backup location and all other required information is entered (for more specific details, refer to Installing ApexSQL Audit), click on Configure.
Upon completing the installation process, the central repository databases will be created on all configured replicas of the Availability Group, and the active connection to ApexSQL Audit processes will be established with the central repository databases on the primary replica. ApexSQL Audit will not alter any configuration settings, so the original setup of the Availability Group will be preserved.
Deploying the Central Repository in a Failover Cluster Instance
The procedure of installing the central repository in a Failover cluster instance is very similar to the previously described deployment of the central repository in an Availability Group. The only difference is, that, instead of a Listener name, the virtual name (SQL Server Network Name) of the SQL cluster should be specified in the ApexSQL Audit central instance setup.
After the deployment is finished, the central repository databases will be created on the cluster storage drive, and ApexSQL Audit processes will make a connection to the active node.
Preventing against data loss of temporary audited files during failover
In addition to providing failover functionality for the central repository database, the disaster recovery solution in ApexSQL Audit 2024.01 offers another layer of protection, which ensures against the loss of data that is sent from audited servers during a failover.
This is achieved by a new mechanism which preserves the packages on the file system of the audited server until receiving a message from the central application that they can be removed. Therefore, the packages that contain audited events will be kept in the newly added SentPackages folder on the audited server until they are processed by the central application (repository), after which, they will be deleted.
Changing the location for ApexSQL archives and active repository databases for a remote central repository
When deploying the central repository on a remote standalone or clustered instance, setting the location for ApexSQL databases is available only through the installer – either during initial installation or via the installer available in ProgramFiles (“% Program Files\ApexSQL\ApexSQL Audit\ApexSQL.Audit.Installer.exe”).
This means that changing the location of ApexSQL databases via the Archiving Wizard or Maintenance Wizard (Split data) is not supported for remote central repositories. This option is disabled and contains a tooltip that informs the user about the limitation.
