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.
When an availability group is configured using multiple SQL Servers, one of the servers is designated as the primary node and others are considered 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 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.
The AlwaysOn Availability Groups feature is available for SQL Server 2012 and above only.
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
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.