Deploying, installing and configuring ApexSQL Audit in a clustered environment (4211283)

Applies to

ApexSQL Audit

Summary

This article explains how to deploy and configure SQL auditing for the Server Failover clustered instances.

Description

ApexSQL Audit supports auditing of SQL Server clusters right out of the box, whether they are in simple Failover mode or with Always On – High availability groups. At any moment, the active node is being audited and if the failover occurs, the auditing will switch to the newly activated node automatically.

Applying SQL auditing to clustered instances does not differ very much when compared to auditing standalone instances described in the article: SQL Server auditing – initial configuration. With further explanation, we will describe some details that apply to SQL Server clusters only and are different from standalone instances.

ApexSQL Audit central instance installation

To install the ApexSQL Audit main application and the central data repository it is recommended to choose a standalone, non-clustered system with a standalone SQL Server to host the repository database. The chosen system should be in the network domain with the clustered servers to allow proper authorization and communication. Failover mechanisms that are available through clustering are not supported, so if the application is located on a clustered node, in case of a failover the application will be shut down and so the audited data collection. If disaster recovery enablement for the central repository is a necessity, it can be achieved by following the instructions from this article: How to configure SQL auditing data central repository automatic backups

Deployment and installation of the ApexSQL Audit service on cluster nodes

By default, the remote auditing component, i.e. the server-side components should be installed automatically when a SQL Server cluster is added for auditing. However, in case of a deployment failure, due to, for example, some network constrictions the installation can be done manually with these steps:

1. Copy the ApexSQL Audit installation file to each cluster node

   Quick tip: ApexSQL Audit auditing instance can be deployed and installed remotely on one or multiple machines. For details, please read Remote installation of ApexSQL Audit auditing instance (service)

2. Run the installer and select the Install server-side components in the first step of the installation wizard and press the Next button:
    

   

3. After short processing, installation will complete. To ensure that the installed ApexSQL Audit service is up and running, run the Services from Control Panel\All Control Panel Items\Administrative Tools and confirm that ApexSQL Audit service is started
4. Repeat steps #1 to #3 for each cluster node

Adding the SQL cluster for auditing:

As previously mentioned, the procedure to add a SQL cluster for auditing is very similar to adding standalone SQL Servers with one difference – the cluster name, or so-called “virtual name” should be added for auditing instead of a specific node (in case of High Availability groups this would be the listener).

Start the ApexSQL Audit GUI and press the Add server in the Configure pane:

In the Server name field, use drop menu to choose or manually type the name or IP address of the cluster and click the Add button:
 

The SQL auditing agent properties dialogue will be shown where, as usual, auditing account credentials should be entered, and the auditing behavior customized. One difference can be noted in comparison to a standalone SQL Server auditing configuration, this dialogue will show the SQL Server cluster as target and the list of nodes that belong to that cluster. This is the confirmation that the cluster was correctly chosen:

With the auditing agent properties successfully applied and the agent started, the SQL Server cluster name will show up in the server tree along with the other audited servers. On mouse hover over this server tree item, the tooltip will pop up with the information about nodes present in the cluster and which node is currently active:

Now that the server is added, the only remaining task is to configure auditing filters to commence auditing:
 

As already mentioned, the failover scenario will be fully supported this way. In case one node goes down, the SQL auditing configuration will automatically be applied to the second node and vice-versa: