Book Image

Professional SQL Server High Availability and Disaster Recovery

By : Ahmad Osama
Book Image

Professional SQL Server High Availability and Disaster Recovery

By: Ahmad Osama

Overview of this book

Professional SQL Server High Availability and Disaster Recovery explains the high availability and disaster recovery technologies available in SQL Server: Replication, AlwaysOn, and Log Shipping. You’ll learn what they are, how to monitor them, and how to troubleshoot any related problems. You will be introduced to the availability groups of AlwaysOn and learn how to configure them to extend your database mirroring. Through this book, you will be able to explore the technical implementations of high availability and disaster recovery technologies that you can use when you create a highly available infrastructure, including hybrid topologies. Note that this course does not cover SQL Server Failover Cluster Installation with shared storage. By the end of the book, you’ll be equipped with all that you need to know to develop robust and high performance infrastructure.
Table of Contents (9 chapters)
Professional SQL Server High Availability and Disaster Recovery
Preface

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...