Wiki source code of Monitoring AlwaysOn Availability Groups

Last modified by Billie D on 2021/03/05 13:55
Hide line numbers
  1: {{box cssClass="floatinginfobox" title="**Contents**"}}
  2: {{toc /}}
  3: {{/box}}
  4: 
  5: [[image:About Us.WebHome@myitguide_small_01.jpg]]
  6: Once you have implemented Availability Groups, you need to monitor them and respond to any errors or warnings that could affect the availability of your data.
  7: 
  8: If you have __many availability groups__ implemented throughout the enterprise, use an enterprise monitoring tool, such as
  9: 
 10: * **SOC** (**Systems Operations Center**).
 11: 
 12: If you only have a __small number of availability groups__, however, 
 13: 
 14: * then SQL Server provides the AlwaysOn** Dashboard**
 15: * and the AlwaysOn **Health Trace**.
 16: * You can also create your own **Extended Events sessions** to monitor Availability Groups.
 17: 
 18: = AlwaysOn Dashboard =
 19: 
 20: The AlwaysOn Dashboard is an **interactive report** that allows you to view the health of your AlwaysOn environment and drill through, or roll up elements within the topology.
 21: 
 22: [[image:SQLAG0012.JPG||height="510" width="959"]]
 23: 
 24: * see that currently synchronization is in a healthy state
 25: * add the Estimated Recovery Time and Estimated Data Loss measures.
 26: ** Estimated Data Loss - gauge impact in the event of an incident
 27: * three possible synchronization states
 28: ** SYNCHRONIZED,
 29: ** SYNCHRONIZING,
 30: ** and NOT SYNCHRONIZING
 31: *** A __synchronous__ replica should be in the __SYNCHRONIZED__ state, and any other state is unhealthy.
 32: *** An __asynchronous__ replica, however, will never be in the SYNCHRONIZED state, state of __SYNCHRONIZING__ is considered healthy.
 33: *** Regardless of the mode, __NOT SYNCHRONIZING__ indicates that the __replica is not connected__.
 34: ** PENDING_FAILOVER,
 35: ** PENDING,
 36: ** ONLINE,
 37: ** OFFLINE,
 38: ** FAILED,
 39: ** FAILED_NO_QUORUM, and
 40: ** NULL (when the replica is disconnected).
 41: * The **operational state** of a replica can be viewed using the **//sys.dm_hadr_availability_replica_states//** DMV
 42: * At the top right of the report, there are links to the **//failover wizard//**
 43: * Cluster Quorum Information screen
 44: 
 45: = AlwaysOn Health Trace =
 46: 
 47: The AlwaysOn Health Trace is an **Extended Events session**, which is __created when you create you first availability group__.  Drilling through the session exposes the session’s package, and from the context menu of the package, you can view previously captured events
 48: 
 49: Right-clicking a column header in the top pane of the window will expose a context menu, which allows you to search for a text or a value in a specific column, group by the values within a column or sort the results sets by a specific column. You can also use the context menu to add or remove columns from the result set.
 50: 
 51: == Monitoring AlwaysOn with Extended Events ==
 52: 
 53: **Extended Events** are a __lightweight monitoring system__ in SQL Server, which __capture  events using **WMI**__. Because the architecture //uses so few system resources//, __they scale very well__ and allow you to monitor instances, __with minimal impact on user activity__. They are also __highly configurable__, giving you a wide range of __options for capturing details__ from a very fine grain, such as //page splits//, to courser-grain information, such as //CPU utilization//.  You can also __correlate Extended Events with operating system data__ to provide a holistic  picture when troubleshooting issues. The __predecessor to Extended Events was //SQL Trace//__ and its GUI, called __Profiler, which is deprecated__.
 54: 
 55: == Extended Events Concepts ==
 56: 
 57: Extended Events have a rich architecture, which consists of the following.  These artifacts are stored within a **Package**, which is, in turn, stored within a module, which can be either a **.dll** or an **executable**.
 58: 
 59: * Events,
 60: * Targets,
 61: * Actions,
 62: * Predicates,
 63: * Types,
 64: * Maps,
 65: * and Sessions.
 66: 
 67: === Packages ===
 68: 
 69: A package is a container for the objects used within Extended Events. Here are the four types of SQL Server package:
 70: 
 71: * **Package0** – The default package, used for Extended Events system objects.
 72: * **Sqlserver** – Used for SQL Server–related objects.
 73: * **Sqlos** – Used for SQLOS-related objects.
 74: * **SecAudit** – Used by SQL Audit; however, its objects are not exposed.
 75: 
 76: === Events ===
 77: 
 78: An **event** is an occurrence of interest that you can trace. It may be a SQL batch completing, a cache miss, or a page split, or virtually anything else that can happen within the Database Engine, depending on the nature of the trace that you are configuring. Each event is categorized by **channel** and **keyword** (also known as category). A //**channel**// is a __high-level categorization__, and all **//events//** in SQL Server 2016 fall into one of the channels
 79: 
 80: * **Admin** - __Well-known events__ with well-known resolutions. For example, //deadlocks//, //server starts//, //CPU thresholds// being exceeded, and the use of deprecated features.
 81: * **Operational** - Used for __troubleshooting__ issues. For example, //bad memory// being detected an AlwaysOn Availability Group replica changing its state, and a //long IO// being detected are all events that fall within the Operational channel.
 82: * **Analytic** - __High-volume__ events that you can use for troubleshooting issues such as //performance//. For example, //a transaction beginning//, a //lock// being acquired, and a //file read completing// are all events that fall within the Analytic channel.
 83: * **Debug** - Used by //developers to diagnose issues// by returning __internal data__. The events in the Debug channel are __subject to change in future__ versions of SQL Server, so you should avoid them when possible.
 84: 
 85: SQL Server exposes __122 events__ relating to AlwaysOn.
 86: 
 87: === Targets ===
 88: 
 89: A target is the __consumer of the events__; essentially, it is the device to which the __trace data will be written.__
 90: 
 91: [[image:SQLAG0013.JPG||height="686" width="968"]]
 92: 
 93: === Actions ===
 94: 
 95: Also known as Global Fields, **Actions** are __commands that allow additional information to be captured when an event fires__. An action is fired synchronously when an event occurs and the event is unaware of the action. There are 50 actions available that allow you to capture a rich array of information, including the statement that caused the event to fire, the security context under which the statement ran, the transaction ID, the CPU ID, and the call stack.
 96: 
 97: === Predicates ===
 98: 
 99: **Predicates** are __filter conditions that you can apply before the system sends events to the target__. It is possible to create simple predicates, such as filtering statements completing based on a database ID, but you can also create more complex predicates, such as only capturing the role change of an AlwaysOn Availability Group replica if it happens more than twice.
100: 
101: Predicates also fully support short-circuiting. This means that if you use multiple conditions within a predicate, then the order of predicates is important, because if the evaluation of the first predicate fails, the second predicate will not be evaluated. Because predicates are evaluated synchronously, this can have an impact on performance. Therefore, it is sensible to design your predicates, so that predicates which are least likely to evaluate to true are placed before predicates that are very likely to evaluate to true. For example, imagine that you are planning to filter on a specific database (with a database ID of 6), but this database accounts for a high percentage of the activity on the instance. You also plan to filter on a specific user ID (UserA), which is responsible for a low percentage of the activity. In this scenario, you would use the WHERE (([sqlserver].[username]='UserA') AND ([sqlserver].[database_id]=(6~)~)~) predicate to first filter out activity that does not relate to UserA, before then filtering out activity that does not relate to database ID 6. Types  All objects within a package are assigned a type. This type is used to interpret the data stored within the byte collection of an object. Objects are assigned one of the following types:
102: 
103: * Action
104: * Event
105: * Pred_compare (retrieve data from events)
106: * Pred_source (compare data types)
107: * Target
108: * Type
109: 
110: === Maps ===
111: 
112: A **map** is a __dictionary that maps internal ID vales to strings that DBAs can understand__.  Map keys are only unique within their context and are repeated between contexts. For example, within the statement_recompile_cause context, a map_key of 1 relates to a map_value of Schema Changed. Within the context of a database_sql_statement type, however, a map_key of 1 relates to a map_value of CREATE DATABASE. You can find a
113: complete list of mappings by using the __//sys.dm_xe_map_values DMV//__.
114: 
115: === Sessions ===
116: 
117: A **session** is __essentially a trace__. It can contain events from multiple packages, actions, targets, and predicates. When you start or stop a session, you are turning the trace on or off. When a session starts, events are written to memory buffers and have predicates applied before they are sent to the target. Therefore, when creating a session, you need to configure properties, such as how much memory the session can use for buffering, what
118: events can be dropped if the session experiences memory pressure, and the maximum latency before the events are sent to the target.
119: 
120: == Creating an Event Session to Monitor Availability Group ==
121: 
122: You can create an event session using either the **New Session Wizard**, the New Session Dialog Box, or via T-SQL. To create a session using the New Session Wizard, drill through __Management__ ➤ __Extended Events in Object Explorer__, and select __New Session Wizard__ from the context menu of Sessions. This will cause the Introduction page of the New Session Wizard to be displayed.  After passing through the Introduction page, you will find the Set Session Properties page. Here, you can configure a name for the Session and also specify if the Session should automatically be started on creation.
123: 
124: