CONCEPT: Managing SQL Server Availability Groups with RockSolid

RockSolid fully supports the monitoring and management of SQL Server Availability Groups. However, to ensure the correct functionality and behaviour it is important you understand how to configure and managed SQL Server Availability Groups correctly.

Adding Availability Group Instances to RockSolid

To properly monitor and manage SQL Server availability groups in RockSolid, all SQL Server nodes in the availability group must be added as instance nodes within RockSolid.

These are added normally, i.e. the same as any other SQL Server instance in your environment, and should be assigned to the appropriate Instance Group and Database bucket’s to ensure appropriate management occurs.

For more information see Adding SQL Server Instances to RockSolid

Adding Availability Group Listeners to RockSolid

In addition to adding the SQL Server instance nodes themselves, it is also a requirement that each SQL Server Availability Group Listener is added into the RockSolid configuration. Again, these should be added in same way as other nodes in your environment, except the Node Type option must be set to “Listener” rather than “Node”.

For more information see Adding SQL Server Listeners to RockSolid

Appling RockSolid Configuration

Once nodes are added to RockSolid, it is important to understand how the RockSolid configuration applies to SQL Server Availability group nodes, as this affects both SQL Server instance and database configuration, as well as job deployment and scheduling.

Policy Hierarchy

RockSolid applies configuration settings in a policy hierarchy for all instances, and this is the same for when SQL Server AG nodes are configured with one important difference. The Listener instance configuration becomes an extra level in the configuration hierarchy.

When configured, RockSolid settings and scheduling defined at the AG Listener level become settings which are applied as part of the policy hierarchy for the primary AG node, overriding any instance and database level configurations made directly at the AG instance node level, or settings inherited as part of the SQL Server configuration hierarchy.

In general the hierarchy can be thought of as being in order of:

Instance Configuration

The configuration that applies to the SQL Server instance is specified using the following hierarchy.

Service Provider Level Instance Configuration

Site Level Instance Configuration

Instance Group Level Instance Configuration

Instance Level Configuration

Listener Level Configuration (when instance is primary node)

In all cases in RockSolid, the more specific configuration overrides a less specific configuration if a conflict occurs.

Database Configuration

The configuration that applies to the SQL Server database is specified using the following hierarchy.

Database Bucket Level Configuration

Database Level Configuration

Listener Database Level Configuration

Scheduling / Calender

The calendar hierarchy used for scheduling jobs and categorising monitored data and is applied in the following hierarchy.

Instance Group Level Calendar

Instance Level Calendar

Listener Level Calendar (when instance is primary node)

Database Level Calendar

Listener Database Level Calendar (when instance is primary node)

Effect of Policy Hierarchy

Without Listener Level Configuration

When no policy is defined at the AG Listener level, the policy applied to the AG nodes as it would be if the SQL Server Instances were unrelated nodes. However in this scenario RockSolid still determines if the databases are part of an AG, so only deploys configuration relevant for databases given their current role in the SQL Server Availability group.

For example, if RockSolid detects databases are participating in an AG, it will only deploy the backup jobs to the node specified in the AG configuration as the backup node. Similarly, any actions or tasks which cause change in the database, such as index rebuilds, will only be deployed to the Primary Node, regardless if RockSolid configuration states both nodes should have this configuration.

The purpose of this allows the DBA to configure all nodes that can participate in the AG as if they “were the primary node”, however RockSolid only deploys the relevant configurations based on each nodes current role in the AG.

If/when the AG nodes role changes, RockSolid automatically re-evaluates the configuration and redeploys configuration and jobs based on the new role of each instance in the SQL Server Availability Group.

For simplicity, this is the recommended method of applying configuration for most SQL Server AG configurations in RockSolid.

With Listener Level Configuration

Alternatively, if a configuration is applied at the Listener Level, then the same approach as above is used except the Primary Node’s instance level policy is overridden by any specific policy configuration defined in RockSolid at the Listener node level.

In this scenario, it is recommended that each SQL Server Instance node is instead configured as if it was expected to be a Secondary Node in the AG. Then, the configuration applied at the Listener node level is defined as per the requirements of the Primary AG node.

Using this approach, whatever node is currently acting as the Primary AG Node will be configured and have jobs deployed based on the RockSolid configuration defined at the Listener Node level. The Secondary Node(s) in the AG will have the configuration applied directly from the instance level configuration defined for that specific 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.

Viewing & Managing Availability Groups

Once defined in RockSolid, SQL Server Availability Groups can be view and managed via the listener node. To find the listener node you can search for the listener by name via the managed instance screen.

Listeners are identified with a blue icon, as opposed to a green icon which identifies a standard instance node.

Drilling into the listener shows the name of the availability group in the manage instance screen. Clicking the availability group name takes you through to the manage configuration screen for the availability group.

Configuration options which can be set in this screen are:

Preferred Primary: The SQL Server instance node which RockSolid expects to be the primary node. If the availability group primary is a node other than this node, a service request is raised in RockSolid to alert the DBA team that the AG is running on a non-preferred node.

Backup Location: This defaults to the AG backup location, but if changed overrides the configured AG backup location to that specified in RockSolid.

DBCC Location: This defaults to the AG DBCC location, but if changed overrides the configured AG DBCC location to that specified in RockSolid.

Summary

Using the above approach RockSolid allows SQL Server Availability Group configuration, scheduling and job deployment to be configured based on standard policy configuration, but with alterations applied depending on the role of the SQL Server Instance node at a specific point in time.

Using RockSolid configuration appropriately with SQL Server Availability Groups allows a specific and highly controlled policy combination to be applied and dynamically adjusted as the role of each node changes.