The Nodes scenario is recommended for users who want to audit regular databases and AlwaysOn databases on nodes that can be in PRIMARY or READ-ONLY 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.

Review the following steps to manually deploy the agent service to all AlwaysOn node.

  1. Start the SQL Compliance Manager Management Console.
  2. Select the SQL Server instance to which you want to manually deploy the agent, and click Add Server. SQL Compliance Manager displays the SQL Compliance Manager Configuration Wizard - Add Server.

  3. On the Specify SQL Server page, specify or browse the node, and click Next.

  4. On the Existing Audit Data page, select the option to retain all of the previously-collected audit data and use the existing database, and click Next.

  5. On the SQL Server Cluster page, check this option if the instance is a virtual SQL Server, and click Next.

  6. On the SQLcompliance Agent Deployment page, verify that the Manually Deploy option is selected, and click Next

  7. On the Select Databases page, select the AlwaysOn database, and click Next

  8. SQL Compliance Manager 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.

  9. Review the available information, and click Next
  10. On the Audit Collection Level page, select the Default audit level, and click Next
  11. On the Permissions Check page, verify that all permissions pass, and click Next
  12. SQL Compliance Manager displays the Summary page. 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.



SQL Compliance Manager monitor, audit and alert on SQL user activity and data changes.
IDERA | Products | Purchase | Support | Community | Resources | About Us | Legal

  • No labels