Page History

...

SQL Compliance Manager provides auditing of the AlwaysOn-configured database and audits the events on the alwayson AlwaysOn database along with the failovers.

How AlwaysOn integrates with SQL

...

Compliance Manager

There are two scenarios of how SQL Compliance Manager can work with AlwaysOn availability group databases:

• Listener. Use this scenario when you want to audit a listener (virtual SQL server instance) that works only with a node in the PRIMARY role.
• Nodes. Use this scenario when you want to audit every node that can be in PRIMARY or SECONDARY roles. Note that the secondary role is read-only.

...

...

Review the following links to configure AlwaysOn Availability Groups:

Configuring Listener scenario:

...

The Listener scenario is recommended for users who want to audit only AlwaysOn databases on the Primary node using LISTENER. If you want to audit read-only Secondary nodes , use the Nodes scenario.

...

1. Install cluster agent services on all Listener nodes using the SQL Compliance Manager Cluster Configuration Console

Use the following steps on each node involved in the AlwaysOn group before adding the listener to SQL CM for auditing.

1. Run the IderaSQLcmInstallationKit*.exe setup file.
2. Extract the SQL Compliance Manager installation kit to the specified directory, and then launch it. SQL CM displays the product Quick Start window.
3. In the Quick Start window, click Cluster Configuration Console. SQL CM displays the Cluster Configuration Setup Wizard.
4. Follow the steps in the Setup Wizard to install and configure the SQLcompliance Agent Service on this cluster node.
5. Once installation is complete, open the Cluster Configuration Console by clicking Start > Idera > Cluster Configuration Console.
   Image Removed
6. Click Add Service to specify the listener. SQL CM displays the Add SQLcompliance Agent Service - General window.
   Image Removed
7. Type the SQL Server instance name, and then click Next. If you receive a message stating that the selected SQL Server instance is not clustered , click Yes. This is correct behavior when configuring a Listener scenario and confirms that the selected SQL Server instance is hosted on a Windows Failover Cluster. SQL CM displays the Add SQLcompliance Agent Service - Collection Server window.
   Image Removed
8. Specify the name of the server where SQL CM is installed, and then click Next. SQL CM displays the Add SQLcompliance Agent Service - SQLcompliance Agent Service Account window.
   Image Removed
9. Specify the login credentials for the Agent service account, and then click Next. This account must have administrator privileges. Idera recommends that you use the same account as used for the Collection Server. After clicking Next, SQL CM displays the SQLcompliance Agent Service - SQLcompliance Agent Trace Directory window.
   Image Removed
10. Specify the trace directory for the cluster agent service, and then click Next. Note that the administrator account specified for the cluster agent service has read/write permissions for this trace directory folder. SQL CM displays the Add SQLcompliance Agent Service - CLR Trigger Location window.
    Image Removed
11. Specify the location where you want the SQLcompliance Agent to store the corresponding CLR trigger assemblies, and then click Next.  Note that the administrator account specified for the cluster agent service has read/write permissions for this assembly folder. SQL CM displays the Add SQLcompliance Agent Service - Summary window.
    Image Removed

12. Verify that the Summary window displays the correct SQL Server instance that you want to audit, and then click Finish.  If you receive a message stating that the selected SQL Server instance is not clustered , click Yes. This is correct behavior when configuring a Listener scenario and confirms that the selected SQL Server instance is hosted on a Windows Failover Cluster. SQL CM displays a confirmation message.
    Image Removed

    Info
    Repeat these steps on each node in your AlwaysOn Availability Group. When you are finished configuring all the nodes, register the cluster service agent as a shared resource.

Configuring Listener nodes using the Failover Cluster Manager

2. Install cluster agent services on all Listener nodes using the Failover Cluster Manager

Use the following steps on each node involved in the AlwaysOn group before adding the listener to SQL CM for auditing.

1. After installing the cluster agent service on all Listener nodes, open Server Manager.
2. In the Server Manager tree, click Server Manager > Features > Failover Cluster Manager. The system displays Failover Cluster Manager.
3. Select the cluster service group created for the cluster agent service. In the following example, AGroup001 is the cluster service group.
   Image Removed
4. In the Server Name area, select the resource name of the cluster service group, and then click Properties in the Actions panel. In the following example, aoaglistener is the resource name. Failover Cluster Manager displays the Properties window. Click the Dependencies tab.
   Image Removed
5. Verify that the Resource field displays the listener IP address.
6. In the Other Resources area of the Failover Cluster Manager window, select the resource of the cluster service group, and then click Properties in the Actions panel. In the following example, AGroup01 is the other resource name. Failover Cluster Manager displays the Properties window. Click the Dependencies tab.
   Image Removed
7. Verify that the Resource field displays the listener name. Click Cancel to close this window.
8. After adding the resource information, right-click the cluster service group in the tree, and then select Add a resource > 4 - Generic Service. Failover Cluster Manager displays the New Resource Wizard to allow you to create the new resource.
9. In the Select Service page of the New Resource Wizard, select the cluster service agent from the available list, and then click Next. The cluster service name are displayed in the format SQLcomplianceAgent$[listener name] where [listener name] is a virtual SQL Server name. In the following example, SQLcomplianceAgent$AOAGLISTENER is the service name.

1. Left click ‘Next >’ button, until ‘Summary’ page of the ‘New Resource’ wizard will be opened.

1. Left click ‘Finish’ button to close the ‘New Resource’ wizard.

...

1. Install cluster agent services on all Listener nodes using the Failover Cluster Manager
2. Add the Listener to SQL Compliance Manager 

Configuring Nodes scenario: 

• Manually deploy the SQL Compliance Manager Agent

Ensure to review additional information to start working with AlwaysOn Availability Groups:

• Removing a Listener from SQL Compliance Manager 
• Exporting/importing audit settings for all AlwaysOn nodes
• Removing an AlwaysOn node from SQL Compliance Manager 

1. The ‘SQLcomplianceAgent$[listener name]’ cluster service need to be online (see the screenshot below):

1. Select the ‘SQLcomplianceAgent$[listener name]’ cluster service (in this case it is ‘SQLcomplianceAgent$AOAGLISTENER’) in the ‘Other Resources’ section. Left click the ‘Properties’ item from the context menu. In the opened window, click the ‘Dependencies’ tab. Ensure the resource ‘Agroup01’ dependency is added (see the screenshot below):

1. Go to the ‘Registry Replication’ tab and click the ‘Add’ button. Specify ‘SOFTWARE\Idera\SQLcompliance’ registry key value. Press ‘OK’ button.

1. Press ‘OK’ button to close the ‘Properties’ of ‘SQLcomplianceAgent$[listener name]’ cluster service.

1. Run SQLCM console application and open ‘Add Server’ wizard for [listener name] (virtual SQL server name, in this case ‘AOAGLISTENER’). Click ‘Next’ button.

1. Check ‘This SQL Server instance is hosted by a Microsoft SQL Server Cluster virtual server.’ check-box. This makes a [listener name] (in this case ‘AOAGLISTENER’) to be a virtual SQL server name. Press ‘Next’ button:

1. Ensure the ‘Manually Deploy’ radio button is chosen (it is available for virtual SQL servers). Press ‘Next’ button.

1. Select always on database (in this case ‘TestBase’). Press ‘Next’ button.

1. Users should see the information about all nodes where always on database will be replicated (see the screenshot below). If database is not always on, this screen will be skipped.

1. Choose audit collection level and follow all necessary steps.

1. Ensure that all permissions checks are passed (see the screenshot below).

1. Press ‘Finish’ button to finish the server adding.

1. The added SQL server with database like on the screenshot below need to be seen.

1. Make all necessary audit settings for listener and always on databases, then update configuration and collect data. It is recommended to update configuration before collecting data because users don’t know which node is primary at the moment of use. This can be done by clicking the ‘Refresh’ item from the node context menu.

SQL Compliance Manager audits all activity on your server. Learn more > >

The Listener scenario is recommended for users who want to audit only AlwaysOn databases on the Primary node using LISTENER. If you want to audit read-only Secondary nodes , use the Nodes scenario.

...