Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Release Notes

Availability Groups

Note: The Availability Groups connection type requires SQL Server 2012 or above. The Availability Groups connection type was called High Availability in Spotlight on SQL Server releases prior to 11.1.

An availability group is a set of user databases that fail over together. An availability group consists of a primary availability replica and one to four secondary replicas that are maintained through SQL Server log-based data movement for data protection without the need for shared storage. Each replica is hosted by an instance of SQL Server on a different node of the Windows Server Failover Cluster (WSFC). The availability group and a corresponding virtual network name are registered as resources in the WSFC.

 

Spotlight displays for the Availability Group connection type

Select Availability Group from Monitored connections to show performance statistics for connections of type Availability Group.

Icon Help Reference Description
Heat map A display of monitored Availability Group connections focusing on those connections with the comparatively larger number and severity of alarms.
Spotlight today A display of Availability Group alarms raised, alarms are organized according to priority.
Alarms by time A display of Availability Group alarms raised, alarms are shown according to their duration and severity.
Alarm log A display of Availability Group alarms raised, alarms are sorted in a tabular format.

 

Spotlight displays for a single Spotlight connection to an Availability Group

Expand the Availability Group connection type from Monitored connections to list connections by name.

Click on the connection name to show components, the replicas grid and databases grid for the Availability Group.

Components

Component Description

Status

The status indicator is colored according to the highest raised alarm on the Availability Group.

During a planned outage, all controls are disabled except Status. A Monitored Server - Planned Outage alarm is raised against the Status control. For more information, see Configure | Planned Outage.

Synchronization Health

The health rating of the Availability Group is excellent when all nodes are available and the preferred node is primary.

TIP: See the Replicas grid for the health of each node in the group.

Primary Instance The name of the node (instance) that is currently primary.

Failover indicator

The current failover availability:

Automatic Failover Available At least one of the connected secondary nodes is set for automatic failover.
Manual Failover Required None of the connected nodes are set for automatic failover.

TIP: Also check the Databases grid regarding any loss of data on failover.

Cluster The name of the Windows Server Failover Cluster (WSFC).
Quorum Rating The Quorum rating of database availability. The DBA sets the quorum configuration in the SQL Server Management Studio. The Quorum rating determines the number of node failures that the cluster can sustain.

Replicas grid

Show data for each node in the Availability Group.

Note:

  • One node takes the Primary role. Usually all other nodes have a Secondary role. If a node's role is Resolving then that node may be down.
  • The failover mode for each node is configured by the DBA in the SQL Server Management Studio.

    When the failover mode is set to manual an accompanying icon highlights that manual failover is required. When no failover is available this icon is red.

  • Synchronization Health is indicative of the health of the node. A warning is shown for a partially healthy node. An error is shown for an unhealthy node. For the health of the Availability Group as a whole see Synchronization Health at the top of the Home Page.
  • The availability mode is Synchronous or Asynchronous Commit as configured by the DBA in the SQL Server Management Studio.
  • The node with the primary role is always Connected. Nodes with a secondary role in a Connected state are available for connection. Nodes with a secondary role in a Disconnected state are not available for connection.
  • The backup priority is a numeric value between 1 and 100 configured by the DBA in the SQL Server Management Studio.
  • The read only routing URL is not the endpoint URL. Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica.

    The READ_ONLY_ROUTING_URL = 'TCP://system-address:port' where the system address is a string such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system. The port is a port number that is used by the Database Engine of the SQL Server instance.

Databases grid

Show data for each database in the Availability Group.

Note:

  • Failover readiness indicates whether or not there will be data loss on failover. If there will be data loss then check the Synchronization state. The database on this node could be behind time: in the process of synchronizing (Synchronizing) or Not Synchronized.
  • The Database State shows an error for databases in Suspect or Emergency states. It shows a warning for any other state that is not Online.
  • The LSN values refer to the Log Sequence Number.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating