About monitoring SQL Server Availability Groups with DPA

DPA provides status information, annotations, and alerts for your SQL Server Availability Groups (AGs).

Automatic naming

When you register an AG listener, DPA automatically names the instance using the following format:

<PrimaryReplicaName> via <ListenerName>

When a failover occurs, the name is automatically updated to reflect the new primary replica.

If you manually change the display name of an AG that is registered via the listener, by default DPA overwrites the name each time the monitor starts. To change the default behavior and manually specify the name, edit the advanced Support option AG_INSTANCE_NAME_UPDATE_ENABLED.

AG information in DPA

On the DPA home page, the AG Status Summary box in the Status Summary area shows the number of database instances with partially healthy or not healthy AGs. As with other status boxes, click the filter next to a status to display only instances associated with the selected status.

The AG status icon identifies database instances that include AGs. The color of the dot provides status information (described in the following section). Possible statuses are:

• Green for healthy
• Yellow for partially healthy
• Red for not healthy
• Gray for unknown

To view detailed information:

1. From the DPA home page, click an AG status icon to open the Availability Group Summary view.

   This view shows information about each AG in the database instance. DPA shows status information for primary replicas in the instance. For secondary replicas, the status of the primary replica is displayed if DPA is also monitoring the primary replica.

2. Click any link to view detailed information about the databases and replicas in the AG.

   

How DPA determines the AG status

If the instance is monitored directly and acting as a primary replica

If you are monitoring the instance directly (not through a listener), DPA does the following:

1. Determines the status of all AGs that the instance acts as the primary replica for.
2. Displays the worst status.

Example: An instance is acting as the primary replica for four availability groups. Their statuses are:

• AG1: Healthy
• AG2: Healthy
• AG3: Partially Healthy
• AG4: Not Healthy

DPA shows the status as Not Healthy.

AG1 (primary replica)	AG2 (primary replica)	AG3 (primary replica)	AG4 (primary replica)	DPA status
Healthy	Healthy	Partially Healthy	Not Healthy	Not Healthy

If the instance is monitored directly and acting as a secondary replica

If an instance is acting as a secondary replica for any AGs, that AG's status is Unknown. If the instance also acts as a primary replica for one or more AGs, the Unknown status is ignored.

Example: An instance acts as the primary replica for three availability groups. Their statuses are:

• AG1: Healthy
• AG2: Healthy
• AG3: Partially Healthy

The instance also acts as a secondary replica for one AG. Its status is Unknown.

DPA ignores the Unknown status, and shows the status as Partially Healthy.

AG1 (primary replica)	AG2 (primary replica)	AG3 (primary replica)	AG4 (secondary replica)	DPA status
Healthy	Healthy	Partially Healthy	Unknown	Partially Healthy

If DPA shows the AG status as Unknown, that typically indicates that the instance is acting as a secondary replica for all AGs.

If the instance is monitored via the listener

If you are monitoring the instance via the listener, by default DPA displays the aggregate status as described above. However, you can edit the advanced Support option AG_STATUS_ROLLUP_USE_PRIMARY to determine the status using only the AG associated with the listener.

AG alerts

DPA provides the following AG alerts: SQL Server Availability Group Failover and SQL Server Availability Group Status Change.

SQL Server Availability Group Failover

This alert is triggered when an AG failover occurs. DPA sends alerts based on how you registered instances:

• If you registered database instances directly (not through a listener), when a failover occurs DPA sends an alert for each instance involved in the failover that it is monitoring. For example, if an AG fails over from Instance1 to Instance2 and DPA is monitoring both instances, you receive two alerts. If DPA is monitoring only one of the instances, you receive only one alert.

• If you registered the AG through a listener and the AG associated with the listener fails over, DPA sends one alert, because the listener moves with the AG from Instance1 to Instance2.

If multiple AG failovers occur in a short period of time, DPA aggregates them into one alert per instance.

SQL Server Availability Group Status Change

This alert is triggered when an AG status changes to Partially Healthy or Not Healthy. DPA evaluates AG statuses every 10 minutes by default. You are alerted if the status changes from Healthy to Partially Healthy or Not Healthy between the evaluations. If the status changes from Healthy to another status and then back to Healthy during the same evaluation period, you are not alerted.

Example: In this example, DPA is monitoring an instance that acts as a primary replica for three AGs. The following table shows how the alerts would behave for each of DPA's alert policies. Changes to alert levels are shown in red.

Interval	AG status (change in red)	Policy: Notify when level not visited since normal	Policy: Notify when level changes	Policy: Notify when level is not normal
1	AG1 Healthy AG2 Healthy AG3 Healthy	No alert	No alert	No alert
2	AG1 Healthy AG2 Partially Healthy AG3 Healthy	AG2 Partially Healthy	AG2 Partially Healthy	AG2 Partially Healthy
3	AG1 Healthy AG2 Partially Healthy AG3 Partially Healthy	AG3 Partially Healthy	AG3 Partially Healthy	AG2 Partially Healthy AG3 Partially Healthy
4	AG1 Healthy AG2 Not Healthy AG3 Not Healthy	AG2 Not Healthy AG3 Not Healthy	AG2 Not Healthy AG3 Not Healthy	AG2 Not Healthy AG3 Not Healthy
5	AG1 Partially Healthy AG2 Not Healthy AG3 Not Healthy	AG1 Partially Healthy	AG1 Partially Healthy	AG1 Partially Healthy AG2 Not Healthy AG3 Not Healthy
6	AG1 Healthy AG2 Partially Healthy AG3 Partially Healthy	No alert	AG1 Healthy AG2 Partially Healthy AG3 Partially Healthy	AG2 Partially Healthy AG3 Partially Healthy
7	AG1 Partially Healthy AG2 Healthy AG3 Healthy	AG1 Partially Healthy	AG1 Partially Healthy AG2 Healthy AG2 Healthy	AG1 Partially Healthy
8	AG1 Healthy AG2 Healthy AG3 Healthy	No alert	AG1 Healthy	No alert

Automatic annotations when AG failovers occur

Annotations are automatically added to wait time charts when an AG failover occurs. The annotations allow you to compare changes in performance before and after a failover.

The number of annotations depends on how you registered instances:

• If you registered database instances directly (not through a listener), when a failover occurs DPA adds an annotation for each instance involved in the failover that it is monitoring. For example, if an AG fails over from Instance1 to Instance2 and DPA is monitoring both instances, DPA adds two annotations. If DPA is monitoring only one of the instances, DPA adds only one annotation.
• If you registered the AG through a listener and the AG associated with the listener fails over, DPA adds one annotation.

If you do not want to add an annotation when an AG failover occurs, edit the advanced option AG_EVENT_ANNOTATIONS_ENABLED.