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:

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)

       ...