Page History
The Listener scenario is recommended for users who want to audit only AlwaysOn databases on the Primary node of the Availability Group , by registering the Availability Group Listener for auditing in SQL Compliance Manager. If you want to audit read-only Secondary nodes, use the Nodes scenario instead.
...
| Note |
|---|
Before stepping through the following instructions make sure , ensure that the SQL CM Collection Server, the Management Console, and the Repository Databases are already installed.
|
- From the installation folder of the SQL Compliance Manager Collection Service on the Collection and Repository database server, copy the SQLComplianceClusterSetup.exe file onto the nodes of the Availability Group, to . To install the Cluster Configuration Console on the nodes of the Availability Group, you are going to be auditing. This is located by default at the following path:
C:\Program Files\Idera\SQLcompliance
2. Beginning with the primary node of the Availability Group.
...
Run the SQLcomplianceClusterSetup.exe to launch the installation wizard.
...
Once the installation is complete, go to the SQL Compliance Manager install path. Unless you have specified a different path, the one by default is C:\Program Files\IDERA\SQLCompliance.
3. Once the setup wizard launches, click
...
the Next button to proceed to the License Agreement.
...
4. Read the license agreement, select the option to accept
...
the license agreement terms, and
...
click Next.
5. Select the destination path in which you want to install the IDERA Cluster Configuration Console.
...
6. Click Install to
...
begin the installation.
...
7. The Cluster Configuration Console launches automatically after installation.
...
...
8. Click Add
...
Service to
...
register the
...
Availability Group Listener. SQL Compliance Manager displays the Add SQLcompliance Agent Service - General window, where the name of the Availability Group Listener to audit will need to be entered into the SQL Server textbox.
...
9. Once the name of the
...
Availability Group Listener to audit has been entered, click Next. If you receive a message stating that the selected SQL Server instance is not clustered,
...
click Yes
...
to confirm. When configuring a Listener scenario
...
, this is the correct behavior and ensures that the selected SQL Server instance is hosted on a Windows Failover Cluster.
10. On
...
the Collection Server dialog window, specify the server's name
...
where the
...
SQL Compliance Manager Collection Service is installed
...
and
...
click Next.
...
11. On the SQLcompliance Agent Service Account dialog window,
...
specify the login credentials for the Agent service account
...
and
...
click Next. This account must have local administrator privileges
...
, and sysadmin permissions on the SQL Server nodes of the Availability Group set up for auditing.
12. On the SQLcompliance Agent Trace Directory dialog window, specify the path
...
where audit trace files will
...
be created for the audit process and click Next. Note that the
...
service account specified
...
to run the
...
Agent service
...
must have read
...
and write permissions
...
on this trace directory folder.
...
13. On the CLR Trigger Location
...
dialog window,
...
specify the location where you want the SQL Compliance Manager Agent to store the corresponding CLR trigger assemblies, and
...
click Next. Note that the
...
service account specified
...
to run the
...
Agent service
...
must have read
...
and write permissions
...
on this
...
trace directory folder.
...
| Note | ||
|---|---|---|
| ||
Ensure the |
...
Agent Trace directory and the CLR Trigger location specified exist by creating the folder structure manually through Windows Explorer. |
14. Review the
...
configuration Summary
...
and
...
click Finish.
15. The wizard asks for another confirmation to proceed with the registration of the Availability Group Listener as a virtual cluster server registration, click
16. The IDERA Cluster Configuration Console displays a confirmation message stating that you have successfully added the SQL Compliance Manager Agent.
...
Click OK.
| Note | ||
|---|---|---|
| ||
Repeat these steps on each remaining node in your AlwaysOn Availability Group. Consider using the same folder structure for the Agent Trace directory and the CLR Trigger location when setting the Agent up on the secondary nodes. When you are finished configuring all the nodes, proceed with the steps below. |
2.
| Anchor | ||||
|---|---|---|---|---|
|
...
Create a clustered resource for the newly installed Agent service in Failover Cluster Manager
| Note | ||||||
|---|---|---|---|---|---|---|
The Registry Replication tab is not available in Windows Server 2012. If you are using Windows Server 2012, you must use the "Add-ClusterCheckpoint" PowerShell cmdlet to add the necessary setting. For more information, see
|
Use the following steps only on each the Primary node involved in of the AlwaysOn group Availability Group before adding the listener to SQL Compliance Manager for auditing.
...
finally registering the Availability Group Listener for auditing into the SQL Compliance Manager console.
- Launch the Failover Cluster Manager
2. Select the clusters' ServiceGroup
...
(Windows Server 2008) or Role (Windows Server 2012 and later) created for the cluster agent service.
...
3. On the Server Name area, right-click the resource name and click
...
Failover Cluster Manager displays
...
the Properties window.
...
4. Click the Dependencies
...
.
5. Verify that
...
the Resource
...
field displays the listener's IP address.
6. On the Other Resources area of the
...
Failover Cluster Manager window, right-click the resource within the role
...
and select Properties. Failover Cluster Manager displays
...
the Propertieswindow.
...
7. Click the Dependencies
...
8.
...
Verify that
...
the Resource
...
field displays the listener name. Click Cancel to close this window.
9. After
...
verifying the resource information,
...
right-
...
click the Service Group or
...
Role
...
and point
...
to Add a resource.
...
Click on
...
Generic Service. Failover Cluster Manager displays the New Resource Wizard.
10. On
...
the Select Service page, select the
...
SQLcompliance Agent service
...
from the available list. The
...
service
...
name is displayed in the format SQLcomplianceAgent$[listener name], where [listener name] is
...
the SQL Server Availability Group Listener name
...
previously registered into the SQLcompliance Cluster Configuration Console.
...
11. Click
Next,
...
continue following the wizard, and click Finish.
...
12. On the Other Resources area of the Failover Cluster Manager window, right-click the SQLcomplianceAgent$[listener name]
...
...
and select Bring Online the resource.
...
13. While the cluster service is online, right-click
...
the SQLcomplianceAgent$[listener name]
...
cluster service
...
and
...
click Properties.
...
14. On the Registry Replication tab, click Add. Failover Cluster Manager displays
...
the Registry Key window.
| Note | ||||||
|---|---|---|---|---|---|---|
The Registry Replication tab is not available in Windows Server 2012. If you are using Windows Server 2012, you must use the "Add-ClusterCheckpoint" PowerShell cmdlet to add the necessary setting. For more information, see
|
...
15. To obtain the correct path, go to the IDERA Cluster Configuration Console
...
and copy the Replicated Registry Key from the SQLcompliance Agent details.
...
...
16. Click OK and copy the registry key path back into the service properties window
. The new root registry key appears in
...
the Registry Replication tab of the Properties window.
...
Click Apply and then OK to save changes.
3.
| Anchor | ||||
|---|---|---|---|---|
|
...
Register the Availability Group Listener
...
in SQL Compliance Manager
Use the following steps to add the listener to SQL Compliance Manager for auditing.
- Start the IDERA SQL Compliance Manager Management Console and click New > Registered SQL Server. SQL Compliance Manager displays the SQLcm Configuration Wizard - Add Server.
- On the SQL Server window, specify or browse the listener the Availability Group Listener you want to register with SQL Compliance Manager, and click Next.
- On the SQL Server Cluster page, check This SQL Server instance is hosted by a Microsoft SQL Server Cluster virtual server box, and click Next. This step makes registers the listener into AG Listener as a virtual cluster SQL Server name.
- On the SQLcompliance Agent Deployment page, verify that the Manually Deploy is selected, and click Next. This option is required for all virtual SQL Servers.
- On the Select Databases page, check the AlwaysOn database that you want to audit, and click Next.
SQL Compliance Manager displays the AlwaysOn Availability Group Detailspage including page, including a list of all nodes where the AlwaysOn database is replicated.
Info This step is valid only if the database selected for auditing is AlwaysOn. The wizard skips this page for regular databases.
- If the AlwaysOn Availability Group Details window is displayed, click Next to continue.
- On the Audit Collection Level page, select the desired audit collection level for the database , and click click Next.
- On the Permissions Check page, SQL Compliance Manager verifies that all the required permissions are in place on the SQL Server instance you want to audit on the Permissions Check page.
- After all the operations are complete and all permissions checks pass, click click Next. The Summary page displays the audit settings for the SQL Server instance.
- Click Finish to to close the wizard. Finally, SQL Compliance Manager displays the newly-added SQL Server instance and AlwaysOn database AlwaysOn Availability Group Listener in the Explore Activity tree.
- Make all necessary audit settings for the listener and AlwaysOn databases, and then update the configuration and begin collecting data. It is recommended to update the configuration before collecting data because users are unaware of which node is PRIMARY. After updating the configuration, be sure to click Refresh in the node context menu to apply the settings to the displayed information.
...