Versions Compared

Old Version 2

changes.mady.by.user Nadja Pollard

Saved on

compared with

New Version 3

changes.mady.by.user Nadja Pollard

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

The AlwaysOn Availability Groups feature uses the availability of a set of databases within your enterprise to improve your failover options and general availability. This feature makes the database highly available using the Windows Failover Cluster Service for Windows Server 2008 and above. As a result, this feature requires Windows Failover Cluster as well as SQL Server on all cluster nodes.

When an availability group is configured using multiple SQL Servers, one of the servers is designated as the primary PRIMARY node and others are considered secondary SECONDARY nodes. If the primary node SQL Server stops or shuts down, the failover automatically switches to the synchronized secondary node with no data loss. You also can manually perform a failover on the SQL Server.

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

...

How AlwaysOn integrates with SQL CM

In SQL CM, the role changes or failover updates are done using a timer thread that runs every 10 seconds. The Collection Service checks the databases added in SQL CM to see if the alwayson database exists. It then uses the Agent Service to see if the role switchover occurred. If yes, then SQL CM updates the database settings accordingly.

Adding a new AlwaysOn SQL Server instance and database

When you attempt to add a new SQL Server instance, the Add SQL Server Instance wizard displays a page specifically for AlwaysOn configuration. Once you select the databases that you want to audit, SQL CM displays the AlwaysOn Availability Group Details page that includes the details of the AlwaysOn configuration for the selected database if it is part of any AlwaysOn Availability Group. Use this page to verify the current settings of your AlwaysOn Availability Groups before continuing to add the new instance. Click Finish in this wizard, and SQL CM automatically adds all the AlwaysOn instances along with the Database that were part of the availability group. Any database that is not part of the AlwaysOn Availability Group is added only in the current instance.

Adding an AlwaysOn database to an existing SQL Server instance

Adding a new database to an existing SQL Server instance in SQL CM uses the same wizard as adding an instance. At the end of the wizard, if the affected instance is already added in the Management Console, then SQL CM adds only the database.

Editing an AlwaysOn database

You can only modify the database properties for the primary node of the AlwaysOn database. If you attempt to modify the secondary node database, SQL CM displays an error message followed by a read-only version of the properties dialog.

Deleting an AlwaysOn database

When deleting a database that is part of the AlwaysOn Availability Group, all primary and secondary databases are deleted using a single Remove option.

Applying regulation guidelines to an AlwaysOn database

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.

You can use only one scenario at a time and it is not possible to use them at the same time on cluster.

Understanding the Listener scenario

Configuring the Listener scenario

The Listener scenario is recommended for users who want to audit ONLY ALWAYS ON databases on PRIMARY node over LISTENER. In case if users need to audit read-only SECONDARY nodes, they should choose ‘Nodes’ Scenario.

Users must follow these steps for all nodes involved into always on group before adding listener to SQL CM console application 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 configure for cluster support.
  5. to see the Cluster Configuration Console is running:
  6. Press ‘Add Service’ button to specify the listener (Virtual SQL instance name, in this case it is ‘aoaglistener’) for ‘SQL Server’ and press ‘Next >’ button.

 

NOTE: Press ‘Yes’ button if below dialog window appears. Doing this users confirm that SQL Server instance will be hosted on Windows Failover Cluster. For always on configuration of listener this is the correct behavior:

 

  1. Specify ‘Collection Server computer’ (where SQL CM product is installed) and left click ‘Next >’ button:

 

  1. Specify an account with Administrator privileges that a cluster agent service will be run with. It is recommended to use the same account which a collection service is run with on the ‘Collection Server computer’. Left click ‘Next >‘ button:

 

  1. Specify ‘Trace Directory’ for the cluster agent service. Be sure that the administrator account, specified for the cluster agent service, has read/write permissions for this folder. Left click ‘Next >‘ button:

 

  1. Specify ‘Assembly Directory’ for the cluster agent service. Be sure that the administrator account, specified for the cluster agent service, has read/write permissions for this folder. Left click ‘Next >‘ button:

 

 

 

 

  1. Left click ‘Finish’ button:

 

NOTE: Press ‘Yes’ button if below dialog window appears. Doing this users confirm that SQL Server instance will be hosted on Windows Failover Cluster. For always on configuration of listener this is the correct behavior:

 

 

  1. The following dialog window message notes to do the above steps for all nodes involved into the always on availability groups and register the cluster service agent as a shared resource. Left click ‘OK’ button and repeat the steps from 1 to 9 for the rest nodes:

 

 

  1. The added listener is shown below:

 

  1. After cluster agent services were installed on all nodes, run ‘Failover Cluster Manager’ and select the cluster service group created for cluster agent service (see the screenshot below, in this case it is ‘AGroup01’):

 

  1. Select the resource of the cluster service group in ‘Server Name’ section (in this case it is ‘aoaglistener’). Left click the ‘Properties’ item from the context menu. In the opened window, click the ‘Dependencies’ tab.  Ensure the resource listener ‘IP Address’ dependency is added (see the screenshot below):

 

  1. Select the resource of the cluster service group in ‘Other Resources’ section (in this case it is ‘AGroup01’). Left click the ‘Properties’ item from the context menu. In the opened window, click the ‘Dependencies’ tab. Ensure the resource listener ‘Name’ dependency is added (see the screenshot below):

 

  1. 15.   Left click the ‘Add a resource’ | ‘4 – Generic Service’ item from the context menu of the cluster service group created for cluster agent service:

 

 

  1. In the opened ‘Select Service’ page of the ‘New Resource’ wizard select the cluster service agent. Cluster service name should be displayed like ‘SQLcomplianceAgent$[listener name]’ where [listener name] is a virtual SQL server name. In this case it is ‘SQLcomplianceAgent$AOAGLISTENER’. Left click ‘Next >’ button.

 

  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. Select the ‘SQLcomplianceAgent$[listener name]’ cluster service (in this case it is ‘SQLcomplianceAgent$AOAGLISTENER’) and left click the ‘Bring this resource online’ itemfrom the context menu (see the screenshot below).

 

  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.

 

 When you apply regulation guidelines to an AlwaysOn database, the guideline applies to the primary node and then is saved for the databases on the secondary nodes. If you attempt to apply guidelines to a secondary node, SQL Compliance Manager displays an error message.

 

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

...