Page History

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 AlwaysOn database along with the failovers.

How AlwaysOn integrates with SQL

...

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

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

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:

1. Install cluster agent services on all Listener nodes using the SQL Compliance Manager Cluster Configuration Console
2. Install cluster agent services on all Listener nodes using the Failover Cluster Manager
3. 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 

...