RockSolid – Full support for SQL Server Availability Groups

published: 2014-12-12 23:45

SQL Server Availability Groups, introduced in SQL Server 2012, provide a robust mechanism for SQL Server high availability without the need for shared disk style clustering. RockSolid, the world leading SQL Server monitoring and management environment for large scale SQL Server deployments, provides full support for SQL Server Availability Groups (SSAG).

Adding SSAG nodes to RockSolid

Database Nodes

Each database node that is a member within a SQL Server availability group should be added to RockSolid as normal. To do this use the familiar “New Instance” button on the Manage Instances screen. Each node in the availability group should be of the standard “Node” type, i.e. the same type of node for stand-alone SQL Server instances.

Availability group database node

Listener Nodes

In addition to adding the standard database nodes, the SSAG Listener Nodes should also be added to RockSolid. To do this again go to the “New Instance” screen and enter the relevant details. But this time choose “Listener” as the Node Type.

Availability group listener node

It is important to not add the Listener Instance as a standard “Node” type, this must be added as a “Listener” node type. Adding the Listener instance as a standard “Node” will cause issues with job deployment.

How RockSolid manages SSAG

Once all standard nodes and listeners have been added to RockSolid, RockSolid will manage the SSAG environment as per your standard policies, but automatically adjust these settings based on the SSAG configuration. For example, RockSolid will only deploy the RockSolid Index and Statistics maintenance jobs to the primary active database, and backup and DBCC jobs will be deployed to the relevant node based on SSAG configuration preference.

Overriding SSAG preference configuration

If you wish to override the SSAG configuration for the preferred node locations of various job types, you can do this within the RockSolid interface. To achieve this find the listener node within the “Manage Instances” screen then drill into the Availability Group configuration screen.

Availability group listener configuration

From the Availability Group configuration screen, the DBA can select the preferred location of Backup and DBCC jobs, either on the primary node or secondary node(s). Index and statistic maintenance jobs will always occur on the primary node.

When SSAG’s fail-over

When SSAG’s failover RockSolid will automatically move the active Backup, Index, Stats and DBCC jobs to the relevant nodes without configuration change required by the DBA team. If the move is temporary and fail-backup occurs, jobs will again automatically fail-back to the relevant nodes. No further configuration changes are required in RockSolid if both database and listener nodes are added.

Notification of when a database is not part of an AG

Databases are required to be added to a new of existing SSAG before the database can received the benefits from that SSAG. However customer feedback has shown that DBAs may forget to added a database to a SSAG as simply creating the database on the primary SSAG node does not automatically added the database to a SSAG in SQL Server.

RockSolid helps to prevent this situation from occurring by allow the DBA team to configure an overlay which specifies that, by default, databases on a certain instance should be included within a SSAG. Once this overlay is added, databases created on nodes with SSAG’s enabled, which are not participating in a SSAG will have configuration violation SR’s raised within the RockSolid console.

Availability group database overlay

To add this overlay:

  • Create a new overlay called “Database not in SSAG”
  • Set the overlay -> Configuration -> “Database should be within an Availability Group” option to Yes
  • Under the Manage -> Database Buckets –> Auto Overlay screen, add the overlay to any Instances of SQL Server where this warning should be initiated.

Related Categories

RockSolid PlatformKBDBA

RockSolid Automation Platform