Troubleshooting AlwaysOn Availability Groups

In this section, we'll look at some of the common AlwaysOn AG problems and their solutions.

Exercise 58: Problem 1 - DDL Queries Block the Redo Thread on the Secondary Replica

This is one of the most common issues you can come across in an AlwaysOn AG environment: the DDL queries on the primary replica block the redo thread on the secondary replica.

Setup

To simulate the problem, follow these steps:

Solution

1. Navigate to the C:\Code\Lesson05 folder and open 3_CreateTableOrders.sql in SSMS. Connect to the DPLPR instance and execute this query:

   -- To be executed at DPLPR
   -- Creates a sample Orders table and populates it with dummy data
   USE Sales
   GO
   DROP TABLE IF EXISTS Orders
   GO
   CREATE TABLE Orders
   (
     OrderID int identity, 
     OrderQty int,
     Price int,
     [Description] varchar(100)
   )
   GO
   WITH cte0 AS (SELECT 0 g UNION ALL SELECT 0)
       ,cte1 AS (SELECT 0 g FROM cte0 a CROSS JOIN cte0 b) 
       ,cte2 AS (SELECT 0 g FROM cte1 a CROSS JOIN cte1 b) 
       ,cte3...