Page History

...

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

...

1. the

...

1. SQL Compliance Manager

...

1. Run the IderaSQLcmInstallationKit*.exe setup file.
2. Extract the SQL Compliance Manager installation kit to the specified directory, and then launch it. SQL Compliance Manager displays the product Quick Start window.
3. In the Quick Start window, click Cluster Configuration Console. SQL Compliance Manager 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 Compliance Manager 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 Compliance Manager displays the Add SQLcompliance Agent Service - Collection Server window.
   Image Removed
8. Specify the name of the server where SQL Compliance Manager is installed, and then click Next. SQL Compliance Manager 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 Compliance Manager 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 Compliance Manager 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 Compliance Manager 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 Compliance Manager 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.

...

1. Install cluster agent services on all Listener nodes using

...

Use the following steps on each node involved in the AlwaysOn group before adding the listener to SQL Compliance Manager 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 within the cluster service group, and then select Properties. 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. The cluster service names 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.
   Image Removed
10. Click Next, the New Resource Wizard displays the Confirmation window.
11. Verify that the displayed information is correct, and then click Next.
12. In the Configure Generic Service window, Failover Cluster Manager creates the new resource. Click Next.
13. In the Summary window, verify the information regarding the new resource, and then click Finish.
    Image Removed
14. In the Other Resources area of the Failover Cluster Manager window, select the SQLcomplianceAgent$[listener name] cluster service group, and then click Bring this resource online from the context menu. In the following example, SQLcomplianceAgent$AOAGLISTENER is the cluster service and is currently offline as noted in the Status column.
    Image Removed
    Failover Cluster Manager bring the service online and displays the updated status, as shown in the following example.
    Image Removed
15. While the cluster service is online, select the SQLcomplianceAgent$[listener name] cluster service, and then select Properties from the context menu. In the following example, SQLcomplianceAgent$AOAGLISTENER is the cluster service name. Failover Cluster Manager displays the Properties window.Verify that the Agroup01 dependency is added, as shown in the following example.
    Image RemovedOn the Registry Replication tab, click
16. Add . Failover Cluster Manager displays the Registry Key window.Type SOFTWARE\Idera\SQLcompliance as the registry key value, and then click OK.
    Image Removed
    The new root registry key appears in the Registry Replication tab of the Properties window, as shown in the following example.
    Image Removed
17. Close the Properties window by clicking OK.

...

1. Listener to SQL Compliance

...

Use the following steps to add the listener to SQL Compliance Manager for auditing.

...

1. 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 

...

Removing a Listener from SQL Compliance Manager

Use the following steps to remove the listener from SQL Compliance Manager auditing.

1. Open Server Manager.
2. In the Server Manager tree, click Server Manager > Features > Failover Cluster Manager. The system displays Failover Cluster Manager.
3. Take the cluster service agent SQLcomplianceAgent$[listener name] offline by selecting the service in the tree, clicking the cluster service agent in the Other Resources area, and then clicking Take this resource offline in the Actions panel. Verify in the confirmation message that you want to take the resource offline. In the following example, SQLcomplianceAgent$AOAGLISTENER is the now offline cluster service agent.
   Image Removed
4. Keep Failover Cluster Manager open as you will return to this view after removing the listener from SQL Compliance Manager.
5. Open the SQL Compliance Manager Management Console.
6. Click the listener name in the Explore Activity panel, and then click Remove.
   Image Removed
   SQL Compliance Manager displays an error message concerning the inability to contact the agent when removing the listener.
   Image Removed
7. Click Yes to confirm that you want to continue with removal of the instance.
8. If you want to re-add this listener for auditing at a later time, do not continue with the next steps. If you no longer want to use this listener, continue with the following steps for all nodes included in the AlwaysOn Availability Group.
9. Return to Failover Cluster Manager.
10. Delete the cluster service agent SQLcomplianceAgent$[listener name] by selecting the service in the tree, clicking the cluster service agent in the Other Resources area, and then clicking Delete in the Actions panel. Verify in the confirmation message that you want to delete the resource. In the following example, SQLcomplianceAgent$AOAGLISTENER is the cluster service agent.
    Image Removed
    The following example shows that the resource is now gone after deletion.
    Image Removed
11. Open the Cluster Configuration Console by clicking Start > Idera > Cluster Configuration Console.
12. Select the virtual SQL Server listener, and then click Remove Service. In the following example, AOAGLISTENER is the listener.
    Image Removed
13. Click Yes in the confirmation message. The cluster service agent is removed.
14. If you no longer need to add listeners , uninstall the Cluster Configuration console.

...

The Nodes scenario is recommended for users who want to audit regular databases and AlwaysOn databases on nodes that can be in PRIMARY or READONLY SECONDARY nodes.

The SQL Compliance Manager administrator adds each node or instance of SQL Server involved in the availability group individually, which is the same process as with any regular SQL Server instance. You can then add any database that you want to audit. While you can automatically deploy the agent through the console, it is recommended that you manually deploy in case the automatic deployment fails. Note that the permissions requirements are the same as for the Listener scenario. For more information about permissions, see Permissions requirements.

AlwaysOn databases running as the secondary replica do not appear in the Add Database wizard unless the replica is marked as read-only. Note that the default status is non-readable.

Example of manually deploying the agent

The following example shows the steps necessary to manually deploy the agent service to all AlwaysOn nodes. This example uses AOAGNODE1 and AOAGNODE2, which are in the AlwaysOn group.

1. Start the SQL Compliance Manager Management Console.

2. Select the SQL Server instance to which you want to manually deploy the agent, and then click Add Server. SQL Compliance Manager opens the SQL Compliance Manager Configuration Wizard - Add Server, Specify SQL Server page.
   Image Removed

3. Click Next. SQL Compliance Manager displays the Existing Audit Data page of the Add Server wizard.
   Image Removed
4. Select the option to retain all of the previously-collected audit data and use the existing database, and then click Next. SQL Compliance Manager displays the SQL Server Cluster page.
   Image Removed
5. Check this option if the instance is a virtual SQL Server, and then click Next. For this example, this is a regular SQL Server instance. SQL Compliance Manager displays the SQLcompliance Agent Deployment page.
   Image Removed
6. Verify that the Manually Deploy option is selected, and then click Next. SQL Compliance Manager displays the Select Databases page. This option is selected because you cannot automatically deploy the agent. Agent services must be manually installed on each node.
   Image Removed
7. Select the AlwaysOn database, and then click Next. This example uses the databases TestBase. SQL Compliance Manager then displays the AlwaysOn Availability Group Details page. This page displays information about all nodes where the AlwaysOn database will be replicated. Note that this page does not appear if the database is not AlwaysOn.
   Image Removed
8. Review the available information, and then click Next. SQL Compliance Manager displays the Audit Collection Level page.
   Image Removed
9. Select the Default audit level, and then click Next. SQL Compliance Manager displays the Permissions Check page.
   Image Removed
10. Verify that all permissions pass, and then click Next. SQL Compliance Manager displays the Summary page.
    Image Removed
11. Click Finish.

After adding all nodes, the SQL Compliance Manager displays the primary node, as shown in the following image. You also now can audit any AlwaysOn databases in the added nodes if they are in PRIMARY or READ-ONLY SECONDARY roles.

Image Removed

Exporting/importing audit settings for all AlwaysOn nodes

Users can select all of the appropriate audit settings for each AlwaysOn database and export these settings as XML files. You then can import the files into the remaining instances or nodes in the group.

Image Removed

To import the audit settings to each node, click Import on the Summary tab. Choose the exported XML file, the information you want to import, and the servers to which you want to apply the settings. Select all the other servers in the availability group as the target for audit settings. After users apply the settings from the file, each member of their availability group is set to audit in exactly the same way as noted in the exported file. This process also allows you to add additional databases that are the part of an availability group on these servers.

Removing an AlwaysOn node from SQL Compliance Manager

To remove an AlwaysOn node from SQL Compliance Manager, first stop the agent service using the Failover Cluster Manager before attempting to remove a node instance from SQL Compliance Manager. This step must be performed if you may want to add back to SQL Compliance Manager the removed node using the Manual Deployment option without any agent deployment. In this case, ignore the error message that appears after you remove the node.

...