SQL Server Advanced Troubleshooting and Performance Tuning

Chapter 12. AlwaysOn Availability Groups

AlwaysOn Availability Groups is the most common High Availability (HA) technology used in SQL Server. It persists multiple copies of databases, eliminating storage from being the single point of failure. It also allows you to scale the read workload through multiple readable secondary nodes.

In this chapter, I’ll provide an overview of how Availability Groups work internally and explain how to troubleshoot common issues. You will learn about the overhead introduced by synchronous replicas and readable secondaries. Finally, I will discuss Availability Groups monitoring and cover how to troubleshoot failover events.

AlwaysOn Availability Groups Overview

Perhaps the easiest way to explain how AlwaysOn Availability Groups work is to look at the history of this technology. It was introduced in SQL Server 2012 as the replacement and successor to Database Mirroring, which used to have the internal Microsoft code name Real Time Log Shipping. That name is a good description: both Database Mirroring and Availability Groups rely on the stream of transaction log records to communicate. The primary node sends log records to secondaries, which harden them in their transaction logs and replay (redo) the changes in the databases.

Both technologies support the single primary node that handles the write workload. In SQL Server Enterprise Edition, Availability Groups can have multiple secondary nodes, of which some or all can handle read-only workloads. ...